SQLStoredProc

anglam62 · Post by **anglam62** » Fri 13 Jul 2007 11:47

I have used Delphi 7, mySQL 5.0, dbxmda 3.10.4 Trial. No problem in this case.
After, I buy the dbxmda 3.20.5 and the same program give me an error.
Using SQLStoredProc that have params[0] ptResult (integer) and param[1] ptInput (string).
Is sufficient I try to put Active = true, the error is :
'Incorrect number of argument of FUNCTION BASENAME.FUNCTIONNAME; expected 1, got 0.'
That is not true. Infact removing dbxmda 3.20.5 and installing
the old version the function give mi the correct value.

Antaeus · Post by **Antaeus** » Mon 16 Jul 2007 13:42

Please show me the code that you use to setup SQLStoredProc, fill its parameters and run it, and SQL statement to create a stored procedure/function on the server. What Queries are sent to server in both cases (you can use the TSQLMonitor, or SQL server logs for that)?

Try to call this function using the SQLQuery component. Query may look like this:
SELECT func(:inp_parameter)

anglam62 · Post by **anglam62** » Mon 16 Jul 2007 15:39

Hi Antaeus!

The code to setup SQLStoredProc is:

dmOrdini.ScriviProgrGen.Close;
dmOrdini.ScriviProgrGen.ParamByName('Tipo').AsString := 'Ordini';
dmOrdini.ScriviProgrGen.Open;

on the open if gives me the error

Error: Incorrect number of arguments for FUNCTION sigea.ScriviProgrGen; expected 1, got 0

This is my Function on the server:

CREATE DEFINER = 'root'@'localhost' FUNCTION `ScriviProgrGen`(Tipo CHAR(6))
RETURNS int(11)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE ResultValue Integer;
IF NOT EXISTS
(SELECT Progr from progressivi
WHERE Ditta = '000'
AND Anno = '0000'
AND tipo = Tipo
AND Serie = '00') THEN
INSERT INTO Progressivi (Ditta,Anno,Tipo,Serie,progr)
VALUES('000','0000',Tipo,'00',0);
END IF;
select Progr into ResultValue
from progressivi
WHERE Ditta = '000'
AND Anno = '0000'
AND Tipo = Tipo
AND Serie = '00'
For UPDATE;
update Progressivi
set progr=Progr+1
WHERE Ditta = '000'
AND Anno = '0000'
AND Tipo = Tipo
AND Serie = '00';
RETURN ResultValue+1;
END;

I try to call the function 'Select ScriviProgrGen('Ordini')' using the SQLQuery component

the log Result is:

Select ScriviProgrGen(?)
:1 (String[6],IN) = 'Ordini'

if i try to open the SQLStoredProcedure

the log Result is:

SELECT ScriviProgrGen(?)
:1 (Int32,RESULT) =
:2 (String[6],IN) = 'Ordini'

Error: Incorrect number of arguments for FUNCTION sigea.ScriviProgrGen; expected 1, got 0

Thanks
bye
Angelo.

Antaeus · Post by **Antaeus** » Wed 18 Jul 2007 08:36

Thank you for such detailed information. We have reproduced this problem and fixed it. This fix will be included in the next build of DbxMda.

anglam62 · Post by **anglam62** » Thu 19 Jul 2007 06:39

hi Antaeus,
I'm glad, to be Useful.
One month ago, I decide to migrate from BDE to MySql, someone suggest me to use dbexpress and dbxmda.
so I try it an then buy dbxmda. But Just now, I undestend that in my case the best is MyDac. I try it. it's excellent.
As I buy the licenze of dbxmda on June 27 2007, I ask you if is possible to change it with MyDac.
Thanks.
Angelo

Devart · Post by **Devart** » Fri 20 Jul 2007 08:35

Please send your developer license number for DbxMda to our sales team by email.