Error Message in MyDAC, not elsewhere: "#42000 OUT or INOUT argument <param#> for routine <procname> is

[Perry]

Hi, I am having a problem with MyDAC.

I have a stored procedure in MySQL with an OUT parameter. When I execute the stored procedure I get the error "#42000 OUT or INOUT argument for routine is not a variable"

This takes place only with use of MyDAC. I tested procedure execution in SQLyog. It runs fine there.

Coincidentally, I am testing MyDAC for purchase. I don' t have the source code yet so I can't step through the code to find out why this error happens. Have I uncovered a bug in MyDAC? It seems so to me. Not entirely sure though.

Anyone have any information regarding this error? If I can't execute stored procedures with OUT or INOUT parameters then MyDAC is next to useless for me. Most stored procedures in my database have OUT params.

Thanks,

Perry

[Antaeus]

MySQL Server data transferring protocol does not support output parameters. Please refer to this topic. It's very likely that SQLyog implicitly generates special SQL query to get output paremeters.

[Perry]

MySQL Server data transferring protocol does not support output parameters. Please refer to this topic. It's very likely that SQLyog implicitly generates special SQL query to get output paremeters.

Hi Antaeus. For anyone else reading this thread, I found this topic as well regarding this very subject matter. It has more examples to illustrate various techniques to accomplish the same thing.

I posted on forums.mysql.com about this subject matter as well link, hoping that I'd find out when MySQL is slated for returning OUT params to client. I hope I get some feedback, but probably won't. It seems very odd to me that they don't do this already. In my case, I NEED this functionality or I have to employ a highly creative work-around which isn't so structured and could lead to errors my application cannot control or know about in advance without special coding. Let me explain...

I made a modular web server (name Hermes). The modules are like ISAPI in a way, but in my opinion much further advanced with a rich server-side "Webform" system that interfaces a web hit with a database (up til a few days ago, only SQL Server) and also from the database back to HTML content. In SQL Server a stored proc not only works in every sense with OUT parameters, but it also returns an integer value. 0 for success and >=1 for no success with the value being an error code. MySQL procs do not have this functionality, but my webform system wants to use this return value (on SQL Server = @@RETURN_VALUE) to control how it operates. So, if I truely want to use MySQL, now I have to employ an OUT param in all the procedures my webform system uses for this particular app I am working on. So I recode stored procedures and find out MySQL does not return OUT parameters to client. So now I need another method.

In addition to the above scenario, some stored procedures are meant to return datasets that will then be used to construct an HTML grid of data. I had been using a series of other OUT parameters to return values for the count of records returned, the sorted field (0 based index of fields in dataset), the sorted direction (0=ascending, 1=descending), and any special process messages that took place in execution. So a single stored procedure returns a success value, plus returns these extra OUT parameters with vital information, plus returns a dataset.

We can only depend on MySQL to return a dataset. So... If I might suggest a creative go-around for anyone who perhaps is in a similar situation, here is how I plan on attacking this problem. Bear in mind this is a system you need to employ in a rather manual way, but I think it works as I've tested it already, yesterday.

1. Create your MySQL procedure with ONLY input parameters.
2. Before you return the dataset and end the procedure, do a singleton select of the values you would have passed to OUT parameters. Make sure to provide an "AS " so you can programmatically distinguish each value from each other. Example below:
3. Select your dataset
4. End the procedure.

Here's an example of what I'm talking about...

Code: Select all

DELIMITER $$;

DROP PROCEDURE IF EXISTS `WorkAroundExample`$$

CREATE PROCEDURE `WorkAroundExample` (_DatabaseName VARCHAR(64))
BEGIN

DECLARE _RecordCount INT UNSIGNED;
DECLARE _ProcessMessage VARCHAR(500);
DECLARE _Success BOOLEAN;

SET _Success = TRUE;
SET _ProcessMessage = 'some message you want returned';

#get count of dataset records to be returned later
SELECT 
	COUNT(`TABLE_NAME`) INTO _RecordCount 
FROM 
	`information_schema`.`TABLES` 
WHERE (`TABLE_SCHEMA` = _DatabaseName);

#now all values are set, so return singleton select 
#of values you want returned
SELECT 
	_Success AS `Success`,
	_ProcessMessage AS `ProcessMessage`,
	_RecordCount AS `RecordCount`;


#now return dataset of tables in database
SELECT 
	`TABLE_NAME` 
FROM 
	`information_schema`.`TABLES` 
WHERE 
	(`TABLE_SCHEMA` = _DatabaseName);

END$$

DELIMITER ;$$

Now, if you execute the stored procedure with MyDAC TMyStoredProc, your first record set will be the three fields you want, and since they are named you can employ checking in Delphi for existence of field by field NAME before you throw exception for calling for a field that does not exist. When done retrieving those values, call .OpenNext to allow iteration of second result set which is the dataset you wanted.

I realize this is a manual process, but in my case, since my client WANTS to use MySQL instead of SQL Server, this is my only recourse. When all is said and done, this seems to be the most elegant way of interfacing with ummm..... a rather rudimentary database interface.

Gee.. I'd really like to see OUT parameters supported. It would mean a world of difference, but.. as you can see, there IS a work-around in a case such as mine. AND.. you get to use this faster-than-greased-lightning MyDAC which makes it even better (faster).

-Perry

[/url]

[ajasja]

Thank you Perry, your posts have saved me quite a lot of time.

Best regards,
Ajasja Ljubetič

[Antaeus]

By the way, output parameters are supporter in MyDAC 5. Beta versions of MyDAC 5 for Delphi 7 and Delphi 2006 are now available for download from here.