Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2012
    Posts
    6

    Unanswered: simple stored procedure example

    hello!

    Can someone give me simple example of stored procedure with select statement with IN and OUT parameter?

    I cant find any simple examples.

    example table:

    Code:
    CREATE TABLE IF NOT EXISTS `accident` (
      `AccidentID` int(11) NOT NULL DEFAULT '0',
      `AccidentDtm` date DEFAULT NULL,
      `AccidentNotes` char(100) DEFAULT NULL,
      `Street` char(100) DEFAULT NULL,
      `City` char(100) DEFAULT NULL,
      `State` char(100) DEFAULT NULL,
      `Zip` char(100) DEFAULT NULL,
      `DriverID` char(100) NOT NULL,
      `WitnessID` int(11) DEFAULT NULL,
      PRIMARY KEY (`AccidentID`),
      UNIQUE KEY `AccidentID` (`AccidentID`),
      KEY `DriverID` (`DriverID`),
      KEY `WitnessID` (`WitnessID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    Thanks!
    Last edited by exose; 04-25-12 at 09:41. Reason: thanks! :)

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Code:
    mysql> CREATE TABLE IF NOT EXISTS `accident` (
        ->   `AccidentID` int(11) NOT NULL DEFAULT '0'
        -> );
    Query OK, 0 rows affected (0.09 sec)
    
    mysql> insert into `accident` values(1),(2);
    Query OK, 2 rows affected (0.07 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> delimiter &&
    mysql> create procedure proc( in x_in int, out x_out int )
        -> begin
        ->   SELECT sum( `AccidentID` ) + x_in INTO x_out
        ->   FROM `accident`;
        -> end;
        -> &&
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delimiter ;
    mysql> set @x = 0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> call proc( 12, @x );
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select @x;
    +------+
    | @x   |
    +------+
    |   15 |
    +------+
    1 row in set (0.00 sec)
    
    .

  3. #3
    Join Date
    Apr 2012
    Posts
    6
    thanks!

    I have created stored procedure:

    Code:
    CREATE PROCEDURE get_Drivers_involved_in_accidents_in_specific_year(
    IN Year int(4),
    OUT AccidentDtm int)
    select DriverName, DriverSurname,DriverPhone,DriverLicense,DriverInsurer,DriverPolicy
    from accidentdriver
    INNER JOIN accident
    on accident.driverID=accidentdriver.driverID
    WHERE AccidentDtm > '%Year%';
    and I am trying to call it from Java as:

    Code:
     case 8:
    			        		System.out.println("Stored procedure:");
    			        		System.out.println("Shows Drivers involved in accidents in specific year (specyfied by user)");
    			        		System.out.println("Year: 2011 or 2012");
    			        		Integer intArg;
    			        		intArg = Integer.parseInt(readEntry("Enter Year: "));
    			          		runProcedure(connection, intArg);
    			        		break;
    Code:
       static void runProcedure(Connection c, int x) throws SQLException, IOException {
    
       		// Connection
       		Connection conn;
       		conn = c;
    
    		// Create callable staement
    		CallableStatement cs1 = conn.prepareCall("Call get_Drivers_involved_in_accidents_in_specific_year(?, ?)");
    		cs1.setInt("Year", x);
    		cs1.registerOutParameter("AccidentDtm", Types.INTEGER); // Register OUT paramenter
    		// Execute statement
    		ResultSet res = cs1.executeQuery();
    		System.out.println("Results of called procedure: ");
    		int avg = cs1.getInt("AccidentDtm"); // Retrive OUT parameter of stored procedure
    		//Display result
       }
    however I am not too sure how to display result from this stored procedure. Can you assist please?

  4. #4
    Join Date
    Jan 2012
    Posts
    84
    Mayby
    Code:
    System.out.println( avg );
    ?

    It depends on how do you want to display results - on the console (println), in a window (swing), on a web page (servlets/JSP/J2EE), on a mobile phone etc.?
    But I'am not sure if these methods/technologies could be discussed here, since it's MySql forum.

  5. #5
    Join Date
    Apr 2012
    Posts
    6
    i have changed it to:

    Code:
    		int sum = cs1.getInt("AccidentDtm"); // Retrive OUT parameter of stored procedure
    		//Display result
    		System.out.println("Number of drivers involved in an accident in a year " + x +" equals "+ sum);
    but my result is:

    Shows Drivers involved in accidents in specific year (specified by user)
    Year: 2011 or 2012
    Enter Year: 2012
    Results of called procedure:
    Number of drivers involved in an accident in a year 2012 equals 0

    and I have at least 3 accidents in 2012... ;/
    Last edited by exose; 04-26-12 at 11:52. Reason: did not finish the post :)

  6. #6
    Join Date
    Apr 2012
    Posts
    6
    do you think, my stored procedure is fine?

  7. #7
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by exose View Post
    do you think, my stored procedure is fine?
    Yes, almost ... only some small improvements are needed.

    #1 AccidentDtm parameter is ambiguous:
    Code:
    CREATE PROCEDURE get_Drivers_involved_in_accidents_in_specific_year(
    IN Year int(4),
    OUT AccidentDtm int)
    
    ........
    
    select ...............................
     .................................
    WHERE AccidentDtm > '%Year%';
    Can you tell me if AccidentDtm in the WHERE clause is a name of table column? Or is it a name of procedure parameter ?

    It's would be better to use a prefix (o ra postfix) in parameters and local variable names to avoid ambiguity, for example:
    Code:
    CREATE PROCEDURE get_Drivers_involved_in_accidents_in_specific_year(
    IN p_Year int(4),
    OUT p_AccidentDtm int)
    BEGIN
       DECLARE v_var1 int;
       DECLARE v_var2 date;
    here 'p_' stands for parameters and 'v_' stands for local variables.


    #2 The procedure doesn't assign any value to the out parameter 'AccidentDtm'.
    Therefore it always contains 0.

    Use Set var = value, or SELECT xxx INTO to assign value to the variable or the parameter:
    Code:
    CREATE PROCEDURE name(
    OUT p_AccidentDtm int)
    BEGIN
        SET p_AccidentDtm = 20;
        SELECT somefield INTO p_AccidentDtm
        FROM sometable;
    END;

    #3 This condition always evaluates to true:
    Code:
    WHERE AccidentDtm > '%Year%';
    'AccidentDtm' type is date.
    '%Year%' is a literal (a string).
    MySql converts 'AccidentDtm' to a string and compares it to a string: '%Year%'
    For example:
    '2012-04-20' > '%Year%' - it always will be true since ascii code of the first char '2' from the first string is greather that ascii code of '%' (first char of the second string).

    #4 The procedure executes straight SQL
    Code:
    select DriverName, DriverSurname,DriverPhone,DriverLicense,DriverInsurer,DriverPolicy
    from accidentdriver
    INNER JOIN accident
    on accident.driverID=accidentdriver.driverID
    WHERE AccidentDtm > '%Year%';
    It works in a such way, that MySql creates a cursor and returns that cursor to the caller.
    The caller must be capable to open that cursor and retrieve a resultset from it.
    Unfortunately, Java cannot do that (some other languages can).

    Could you explain what do you want to achieve with this procedure ?
    Do you want to calculate a single value in the procedure and return it (a single value) to the caller ?
    Or do you want to return to the caller a result of the query (a resultset with several rows) ?

  8. #8
    Join Date
    Apr 2012
    Posts
    6
    Thanks for your input! At the beginning i was not too sure what is my approach so I have decided to make it simple and run procedure to receive only a number of accidents in specific year, one number.

    here is my procedure which works:

    Code:
    Create PROCEDURE `get_Drivers_per_year`(
    IN DriversYear int(4),
    OUT DriversOut int)
    SELECT count(DriverName)
    INTO DriversOut
    FROM accidentdriver
    INNER JOIN accident ON accident.driverID = accidentdriver.driverID
    Where YEAR(AccidentDtm)=DriversYear
    My Java works with it as well so I am happy now!

    thanks for your help on this!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •