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

Reply via email to