List:General Discussion« Previous MessageNext Message »
From:Manasi Save Date:May 31 2010 10:49am
Subject:Re: Strange behavior by MySQL Stored Procedure
View as plain text  
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=`myuser`@`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.save@stripped>:
  > [...]
  > > 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. 
  >
  >

Thread
Strange behavior by MySQL Stored ProcedureManasi Save28 May
  • Re: Strange behavior by MySQL Stored ProcedureMattia Merzi28 May
  • Re: Strange behavior by MySQL Stored ProcedureAnirudh Sundar28 May
  • Re: Strange behavior by MySQL Stored ProcedureVenugopal Rao1 Jun
    • [ANN]VTD-XML 2.9jimmy Zhang13 Aug
Re: Strange behavior by MySQL Stored ProcedureManasi Save31 May
Re: Strange behavior by MySQL Stored ProcedureManasi Save2 Jun
Re: Strange behavior by MySQL Stored ProcedureManasi Save7 Jun
  • Replication of MySQL Stored ProcedureSabika Gmail7 Jun
    • RE: Replication of MySQL Stored ProcedureRolando Edwards7 Jun
      • Re: Replication of MySQL Stored ProcedureSabika Gmail7 Jun
        • Re: Replication of MySQL Stored ProcedureSuresh Kuna8 Jun
Re: Replication of MySQL Stored ProcedureManasi Save8 Jun