MTA-MySQL and Stored Procedures

[churchill]

has anyone tried to call an SP from mta that does a SELECT statement and returns a mysql query result? it works fine with SPs that have INSERTs or UPDATEs, but it seems SELECT statements have to be inline ?

I can't remember the error message it gives but it seems like a common problem with php/mysql systems as well, with the answer being to use mysqli instead of mysql, but that's probably not an option here.

I've not tested whether you can return OUT parameters so perhaps that could be a solution in some cases?

[Gamesnert]

has anyone tried to call an SP from mta that does a SELECT statement and returns a mysql query result? it works fine with SPs that have INSERTs or UPDATEs, but it seems SELECT statements have to be inline ?

I can't remember the error message it gives but it seems like a common problem with php/mysql systems as well, with the answer being to use mysqli instead of mysql, but that's probably not an option here.

I've not tested whether you can return OUT parameters so perhaps that could be a solution in some cases?

Eh... I don't understand what you're saying... =/

Anyway, I use MTA-MySQL, and it works fine. Even select works fine.

You might need mysql_fetch_rows perhaps?

[churchill]

has anyone tried to call an SP from mta that does a SELECT statement and returns a mysql query result? it works fine with SPs that have INSERTs or UPDATEs, but it seems SELECT statements have to be inline ?

I can't remember the error message it gives but it seems like a common problem with php/mysql systems as well, with the answer being to use mysqli instead of mysql, but that's probably not an option here.

I've not tested whether you can return OUT parameters so perhaps that could be a solution in some cases?

Eh... I don't understand what you're saying... =/

Anyway, I use MTA-MySQL, and it works fine. Even select works fine.

You might need mysql_fetch_rows perhaps?

Ok, I'll explain it another way:

This works:

MySQLResult mysql_query (handler, "SELECT someColumn FROM someTable WHERE someOtherColumn = " .. someValue) 

And this works:

MySQLResult mysql_query (handler, "INSERT INTO someTable (someColumn, someOtherColumn) VALUES (" .. someValue .. "," .. someOtherValue .. ")") 

And this also works:

MySQLResult mysql_query (handler, "call AddSomething(" .. someValue .. "," .. someOtherValue .. ")") 

where AddSomething is the name of a Stored Procedure on the MySQL DB server, that does goes something like:

CREATE PROCEDURE AddSomething (IN someValue INT, in someOtherValue INT) 
BEGIN  
INSERT INTO someTable (someColumn, someOtherColumn) VALUES (someValue, someOtherValue); 
END 

However, this does not work:

MySQLResult mysql_query (handler, "call GetSomething(" .. someValue .. ")") 

where GetSomething is an SP like:

CREATE PROCEDURE GetSomething(IN someValue INT) 
BEGIN  
SELECT someColumn FROM someTable WHERE somOtherColumn = someValue 
END 

You get me? I would have expected mySQLResult to still contain a valid sql result, but instead you get an error. In order to understand the question, you need to understand what Stored Procedures are

[Gamesnert]

Lol, I'm a general MySQL n00b, and just use the basic things. I only use things like insert and select you know... ^^

Might be a mistake on your side or something?

[churchill]

Well that's what I was hoping, I thought maybe there is another way to call it, but from what i've read, it's a symptom of using the original mysql library, rather than the improved mysqli, so I think I just have to accept it can't be done, or create my own MTA-MySQLi module - and that AIN'T gonna happen

[Gamesnert]

Well that's what I was hoping, I thought maybe there is another way to call it, but from what i've read, it's a symptom of using the original mysql library, rather than the improved mysqli, so I think I just have to accept it can't be done, or create my own MTA-MySQLi module - and that AIN'T gonna happen

Speaking of bugs...

CREATE PROCEDURE GetSomething(IN someValue INT)

BEGIN

SELECT someColumn FROM someTable WHERE somOtherColumn = someValue

END

somOtherColumn

I think you're missing something there... =/

EDIT: In case you still don't see it, compare it to:

CREATE PROCEDURE AddSomething (IN someValue INT, in someOtherValue INT)

BEGIN

INSERT INTO someTable (someColumn, someOtherColumn) VALUES (someValue, someOtherValue);

END

Or...

someOtherColumn

[churchill]

well spotted - not that I actually have SPs or Select statements like those - they were just examples! I'm pretty sure the SP wouldn't even get created if you use the one above as it probably has syntax errors in it somewhere.

[Ace_Gambit]

Correct me if I am wrong, but isn't this supposed to happen? As far as I know MySQL stored procedures and stored functions can not return record sets.

[churchill]

you might be right, I'm not familiar with SPs in MySQL, having come from SQL Server background, so I just expected them to work the same way.

I guess that's why the PHP mysqli thing works differently.