mysql Version :- 5.1.42-community-log
mysql Connector/J Version :- mysql-connector-java-5.1.6-bin.jar
Sample Java Code Which Calls stored procedure :-
//get the connection to database
Connection dbConnection = getConnection();
//create the call for procedure
String procedureCallStmtStr = "Call XYZ()";
//create callable statement object
CallableStatement cs = conn.prepareCall(procedureCallStmtStr);
//execute the procedure
cs.execute();
//obtain resultset
ResultSet result = cs.getResultSet();
//Iterate to get the resultSet, if present
//commit transaction
conn.commit();
//close resultset, callableStatement
result.close();
cs.close();
Stored procedure which is getting called :-
CREATE definer=`myus...@`localhost` PROCEDURE `AddCust`(InputUserID
BigInt, InputCustID BigInt, InputDBID BigInt, InputTimeStamp DateTime)
DETERMINISTIC
BEGIN
Declare DBName Varchar(45);
Select InputDBID into DBName;
Drop Temporary Table If Exists Temp;
Create Temporary Table Temp
(
UserID BigInt,
CustID BigInt,
MarkForDeletion Boolean
);
SET @stmt = Concat('Insert into Temp(UserID, CustID, MarkForDeletion)
Select FK_UserID, FK_CustID, MarkForDeletion
From `',DBName,'`.Tbl1
Where FK_UserID = ',InputUserID,'
and FK_CustID = ',InputCustID,';');
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate prepare stmt1;
IF Exists (Select CustID From Temp Where CustID = InputCustID)
Then
SET @stmt = Concat('Update `',DBName,'`.Tbl1
Set MarkForDeletion = 0,
TimeStamp =
','"',InputTimeStamp,'"','
Where FK_UserID =
',InputUserID,'
and FK_CustID =
',InputCustID,';');
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate Prepare stmt1;
ELSE
SET @stmt = Concat('Insert into ',
'`',DBName,'`.Tbl1 (FK_CustID, FK_UserID, MarkForDeletion, TimeStamp) ',
'Select ', '"', InputCustID, '"', ',',
'"',InputUserID,'"',', False',',','"',InputTimeStamp,'"',';');
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate Prepare stmt1;
Select InputUserID as RecordInserted;
END IF;
Thanks in advance.
--
Regards,
Manasi Save
On Fri, 28 May 2010 15:40:05 0200, Mattia Merzi wrote:
2010/5/28 Manasi Save <[email protected]>:
> [...]
> > Or am I doing something wrong?
> probably;
>
> you better send us another e-mail writing at least:
> - mysql version you are using
> - mysql Connector/J version you are using
> - piece of java code you are using to call the stored procedure
> - source of the stored procedure (or part of it)
>
> ... probably, a subset of all of these infos will not be enough
> to understand the problem.
>
> In any case, if you have troubles using the mysql jdbc driver
> but no problem using the mysql CLI and you suspect a
> Connector/J bug, maybe you better write to the "mysql java"
> support mailing list: http://lists.mysql.com/java
>
> Greetings,
>
> Mattia.
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
