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 <manasi.s...@artificialmachines.com>: > [...] > > 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/mysql?unsub=arch...@jab.org