>I'm adapting Rick Strahl's wwbusiness class (which is designed for MS SQL) to work with MySQL. It uses a stored procedure that I'm having trouble duplicating in MySQL.
>
>
>lcSQL=[CREATE PROCEDURE sp_ww_NewID ]+;
> [@cName char(30),]+;
> [@nRetval int OUTPUT ]+;
> [AS ]+;
> [UPDATE mytableids ]+;
> [SET id = id + 1,]+;
> [@nRetval = id + 1 ]+;
> [WHERE TableName = @cName ]
>
>
>which is called via SPT with
>
>PRIVATE pnID
>pnID = 0
>lcSQL="Execute sp_ww_NewId '" + lcTableName + "',?@pnID"
>
>
>So my adaption to MySQL is:
>
>lcSQL=[CREATE PROCEDURE sp_ww_NewID (cName char(30), out nRetval integer) ]+;
> [UPDATE mytableids SET id = id + 1,]+;
> [nRetval = id + 1 ]+;
> [WHERE TableName = cName ]
>
>and then calling it via SPT with:
>
>PRIVATE pnID
>pnID = 0
>lcSQL="call sp_ww_NewId('" + lcTableName + "', @pnID)"
>>
>But attempting to call the stored procedure gives the error message:
>"Connectivity error: [MySQL][ODBC 5.1 Driver][mysqld-5.1.30]Unknown column 'nRetval' in 'field list' [1526:1054]"
>
>What am I doing wrong?
I don't know mySQL but looking at documentation (almost next to none) sounds like:
text to lcSQL noshow
CREATE PROCEDURE sp_ww_NewID (cName char(30), out nRetval integer)
begin
UPDATE mytableids SET id = id + 1 WHERE TableName = cName;
select id from myTableIds where TableName = cName into nRetval;
end
endtext
PS: Wouldn't the call syntax be better like this:
lcSQL="Execute sp_ww_NewId ?m.lcTableName,?@pnID"
Cetin
