Source language: Translate to:

Stored Procedures in MySql

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Stored Procedures in MySql

Postby cp4w » Sun Nov 18, 2007 10:21 am

Can the rows returned by a stored procedure in MySql display on a grid?
cp4w
 
Posts: 533
Joined: Sun Apr 03, 2005 4:37 pm
Location: Great Neck, NY

Postby Neosoft Support » Mon Nov 19, 2007 11:52 am

In most cases the answer is yes. The third parameter of the dbpExecProc action is for a results table. From the help file:

If the procedure returns a data set, this parameter can be used to enter the name of the table where you want the results of the query to be displayed. This may be the name of an existing database table or it can be a temporary table. If the table specified does not exist in the database, it is assumed to be a temporary table. Leave this parameter blank if the procedure does not return any results.

A temporary table exists only while your publication is running and is not physically part of the database. However, most temporary tables derive their contents from real data, so any edits you make may affect other tables. A temporary table created with dpbExecProc can be opened with the dbpOpenTable and displayed with dbpShowGrid actions. Actions that require a physical table, such as dbpAddField or dbpQuery, cannot be used with a temporary table.

The following example executes a stored procedure and sends the results to a temporary table, which is then opened and displayed in a grid:

dbpExecProc "DB1" "CalculateSales" "Temp1"
dbpOpenTable "DB1" "Temp1" ""
dbpShowGrid "DB1" "Temp1" "Rectangle1"
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

dbpExecProc Not working in MySql

Postby cp4w » Mon Nov 19, 2007 1:48 pm

I don't know if I am missing something obvious, but I have created stored procedures in MySQl. When I use them using dbpexecsql and use a "Call" to execute it the stored procedure is found and executed.

However, when I do "dbpexecproc" I get the following
[MySQL][ODBC 3.51 Driver][mysqld-5.0.18-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

If I change the name to a procedure that does not exist I get the same thing

When I do "dbpGetProcedureNames" it does not find any stored procedures.

Any ideas?

[/quote]
cp4w
 
Posts: 533
Joined: Sun Apr 03, 2005 4:37 pm
Location: Great Neck, NY

Postby Neosoft Support » Tue Nov 20, 2007 11:50 am

Unfortunately, this appears to be a bug in mySQL. Details below:

http://bugs.mysql.com/bug.php?id=15635

You should still be able to execute mySQL stored procedures using dbpExecSQL which also includes a results table parameter. Just make sure that your SQL code includes a SELECT statement.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby cp4w » Tue Nov 20, 2007 12:36 pm

Hi Dave,

The current odbc driver release is 3.16. the link that you pointed to says that it was fixed in 3.13.
cp4w
 
Posts: 533
Joined: Sun Apr 03, 2005 4:37 pm
Location: Great Neck, NY

Postby Neosoft Support » Fri Nov 23, 2007 12:24 pm

We'll I can look into it, but stored procs work in Oracle and Microsoft SQL, so I'm not sure what would be different about mySQL.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests

cron