How to call a stored procedure using MariaDB (MySQL)

[Mike Reed]

I am having trouble getting a stored procedure to work in a MariaDB using arguments. It will work when no arguments are used.
I have tried the formats listed in the wiki, enabled/disabled portablesql, tried different formats in the command but to no avail.
The wiki says to the format cn.execute( exec testinsert @area=:area). The 'exec' command does not work at all.

Can someone point me to the proper way to make this work?

Thanks,
Mike

This is my xbasic code:

dim cn as sql::Connection
dim args as sql::Arguments
dim connection as c = "::Name::connname"
debug(1)
args.Set("AREA","Short Name 1")


cn_open = cn.Open(connection)

'THIS INSERT WORKS FINE
cn_exec = cn.Execute("call testinsert ('New Area 12')")
'THIS DOES NOT WORK
cn_exec = cn.Execute("EXEC testinsert ('New Area 12')")
result = cn.ResultSet.data(1)

'NONE OF THESE INSERT COMMANDS WORK, plus many other options I tried.
'cn.PortableSQLEnabled = .f.
'cn.PortableSQLEnabled = .t.
cn_exec = cn.Execute("call testinsert @area=:area ",args)
cn_exec = cn.Execute("call testinsert :area" ,args)
cn_exec = cn.Execute("EXEC testinsert :area" ,args)

result = cn.ResultSet.data(1)
cn.PortableSQLEnabled = .t.

cn.Close()

Here is the stored procedure:

CREATE DEFINER=`root`@`%` PROCEDURE `TestInsert`( IN aname char(30) )
BEGIN
DECLARE areaid INTEGER DEFAULT 0;
INSERT INTO area (area) values (aname);
SELECT LAST_INSERT_ID() INTO areaid;
SELECT areaid;
END

[RustyB]

A shot in the dark, but have you tried

cn_exec = cn.Execute("call testinsert( :area)" ,args)

[Mike Reed]

Yup, that was on my list of things I tried
No dice,

Thanks,

[madtowng]

Hi Mike,

Try this:

dim cn as sql::Connection
dim args as sql::Arguments
dim connection as c = "::Name::homeaa"
dim cn_open as l
dim itWorked as l

debug(1)
args.Set("AREA","Short Name 1")


cn_open = cn.Open(connection)
cn.PortableSQLEnabled = .t.

itWorked = cn.Execute("call testinsert(:area)" ,args)
'''cn_exec = cn.Execute("EXEC testinsert :area" ,args)

result = cn.ResultSet.data(1)


cn.Close()

[Mike Reed]

Sorry for the delay in responding, That works fine.
Thank you and Happy New Year!
Mike

[madtowng]

glad to hear it.
Just for possible future reference, "exec" is the way SQL Server/SQL Express calls stored procedures.
Have a happy New Year.

Gregg