Error Trying to Call Stored Procedure with Prepared Statement

Stored procedure and prepared statement error

From this page explaining the reasons for SQLCODE: -440, one of the reasons is

The right number of arguments was included in the routine reference, but the data type of one or more of the arguments is incorrect.

I suspect that your following method call

stmt.setObject(1, userid, java.sql.Types.CHAR);

should be replaced with

stmt.setString(1, userid);

or

stmt.setObject(1, userid, java.sql.Types.VARCHAR);

I would prefer the former, i.e. setString and leave it to the driver to do the appropriate conversion based on the underlying DB/column.

The same would apply to other statement.set* methods as well, calling the appropriate set* method like setString, setInt etc.,

prepared statement inside stored procedure not working

I tried your procedure in the mysql client, so to rule out any chance the problem is related to PHP:

mysql> create table lunch_status (userId int, lunchstatus int, issueddatetime datetime);

mysql> DELIMITER $$
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllUserLunchReport`(IN `start_date` DATETIME, IN `end_date` DATETIME)
    -> BEGIN ...

mysql> CALL getAllUserLunchReport ('2018-06-17','2018-06-17');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1
mysql> select @SQL;
+------+
| @SQL |
+------+
| NULL |
+------+

But when I tried inserting a row that falls in the date range:

mysql> insert into lunch_status values (123, 1, now());
Query OK, 1 row affected (0.02 sec)

mysql> CALL getAllUserLunchReport ('2018-06-17','2018-06-17');
+--------+------------+
| userId | 2018-06-17 |
+--------+------------+
|    123 |          1 |
+--------+------------+

The problem is that your GROUP_CONCAT() generates NULL for the pivot-table column list when there are no rows in the date range you're searching for.

Then you CONCAT() that NULL into your full SELECT statement, but CONCAT('SELECT ...', NULL, 'FROM ...') yields NULL, because concatenating any string with NULL yields NULL.

You need to make sure your GROUP_CONCAT() defaults to some non-NULL string:

SELECT
    COALESCE(GROUP_CONCAT(DISTINCT
        CONCAT(
        'SUM(CASE WHEN date = "',DATE(issuedDateTime),'" THEN lunchStatus ELSE 0 END) AS `',DATE(issuedDateTime),'`'
        )
    ), '0 as `NoMatchingRows`') INTO @SQL

Calling the procedure will still return no result set, because there are no dates in the table matching the date range you gave. But at least it won't get a syntax error when you try to prepare NULL.

Procedure with cursor and prepared statement problem: You have an error in your SQL syntax; EXECUTE stmt; DEALLOCATE PREPARE stmt; END L' at line 34

In MySQL only user-defined variables may be used in PREPARE. So remove v_statement declaration and use @v_statement everywhere instead:

CREATE PROCEDURE ExecuteGrantsForModifiedProcedures(IN p_schema varchar(120),
                                                       IN p_mysqlUser varchar(120),
                                                       IN p_fromModificationDate datetime)
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_statement TEXT;
    # Get all the modified procedures/functions
    DECLARE cur1 CURSOR FOR SELECT CONCAT('GRANT EXECUTE ON PROCEDURE `', ROUTINE_SCHEMA, '`.`', routine_name, '` TO ',
                                          p_mysqlUser, ';')
                            FROM information_schema.routines
                            where routine_schema = p_schema
                              AND ROUTINE_TYPE = 'PROCEDURE'
                              AND date(LAST_ALTERED) >= date(p_fromModificationDate)
                            UNION
                            SELECT CONCAT('GRANT EXECUTE ON FUNCTION `', ROUTINE_SCHEMA, '`.`', routine_name, '` TO ',
                                          p_mysqlUser, ';')
                            FROM information_schema.routines
                            where routine_schema = p_schema
                              AND ROUTINE_TYPE = 'FUNCTION'
                              AND date(LAST_ALTERED) >= date(p_fromModificationDate);

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    SET max_sp_recursion_depth = 1024;

    OPEN cur1;

    read_loop:
    LOOP
        FETCH cur1 INTO v_statement;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        SET @v_statement := v_statement;
        PREPARE stmt FROM @v_statement;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur1;

end

Syntax error when calling stored procedure inside stored procedure

Use CALL instead:

call UpdateProdcut(myName);

execute is used for prepared statements.

Ref:
https://dev.mysql.com/doc/refman/5.5/en/call.html

Error trying to call stored procedure with prepared statement

You want something like this (untested)

Dim cmd, rs, ars, conn

Set cmd = Server.CreateObject("ADODB.Command")

With cmd
  'Assuming passing connection string if passing ADODB.Connection object
  'make sure you use Set .ActiveConnection = conn also conn.Open should
  'have been already called.
  .ActiveConnection = conn
  'adCmdStoredProc is Constant value for 4 (include adovbs or 
  'set typelib in global.asa)
  .CommandType = adCmdStoredProc
  .CommandText = "dbo.procName"
  'Define parameters in ordinal order to avoid errors
  Call .Parameters.Append(.CreateParameter("@ParamName", adVarChar, adParamInput, 50))

  'Set values using parameter friendly name
  .Parameters("@ParamName").Value = param

  'Are you returning a recordset?
  Set rs = .Execute()
  'Populate array with data from recordset
  If Not rs.EOF Then ars = rs.GetRows()
  Call rs.Close()
  Set rs = Nothing
End With
Set cmd = Nothing

It is important to remember that the friendly name (as I rule I tend to match my parameter names in my stored procedure to my friendly names in ADO) you give your parameter means nothing to the stored procedure as ADO passes the parameters ordinally and nothing more, the fact you get the error;

Procedure or function 'procName' expects parameter '@ParamName', which was not supplied.

Suggests that the stored procedure is expecting your @ParamName parameter (defined in your stored procedure) value to be passed from ADO in a different ordinal position, which usually means you have not defined all your parameters or passed all the parameter values in the position they are expected.

You can also do a shortened version if your confident of your ordinal positioning and parameter requirements

With cmd
  .ActiveConnection = conn
  .CommandType = adCmdStoredProc
  .CommandText = "dbo.procName"

  'Pass parameters as array following ordinal position.
  Set rs = .Execute(, Array(param))
  'Populate array with data from recordset
  If Not rs.EOF Then ars = rs.GetRows()
  Call rs.Close()
  Set rs = Nothing
End With
Set cmd = Nothing

Working with a 2-dimensional array is easy and negates the overhead of working directly with a ADODB.Recordset.

Dim row, rows

If IsArray(ars) Then
  rows = UBound(ars, 2)
  For row = 0 To rows
    Response.Write "First column from row " & row & " = " & ars(0, row) & "<br />"
  Next
Else
  Response.Write "No data to return"
End If

---

Links

• Using METADATA to Import DLL Constants - If your having trouble with the ADO constants (adCmdStoredProc etc.) this will fix it for you.

Calling a stored procedure with a cursor and a prepared statement results in a error 1064 near 'NULL'

I have fixed the issue, please see below for the corrected code:

DROP PROCEDURE IF EXISTS `dropForeignKeys`;
DELIMITER $$

CREATE PROCEDURE `dropForeignKeys`()
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
   DECLARE done INT DEFAULT FALSE;
   DECLARE stmt CHAR(200);
   DECLARE cursor1 CURSOR FOR SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';')
   FROM information_schema.key_column_usage
   WHERE CONSTRAINT_SCHEMA = DATABASE()
   AND referenced_table_name IS NOT NULL;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

   OPEN cursor1;

   execute_loop: LOOP
      FETCH cursor1 INTO stmt;
      IF done THEN
         LEAVE execute_loop;
      END IF;
      SET @stmtBody = stmt;
      PREPARE dropKeyStatement FROM @stmtBody;
      EXECUTE dropKeyStatement;
      DEALLOCATE PREPARE dropKeyStatement;
   END LOOP;

   CLOSE cursor1;
END $$

DELIMITER ;

---

Related Topics