Dynamic SQL in MySQL

[Michael C#]

Is it possible to create Dynamic SQL queries in MySQL, like in SQL Server?
i.e., the EXECUTE command or sp_executesql stored procedure in SQL Server.

TIA

[Bill Karwin]

Re: Dynamic SQL in MySQL

Michael C# wrote:[color=blue]
> Is it possible to create Dynamic SQL queries in MySQL, like in SQL Server?
> i.e., the EXECUTE command or sp_executesql stored procedure in SQL Server.[/color]

Do you mean in general, or specifically in the MySQL stored procedure
language?

I regularly build SQL statements as strings in application code (Perl,
PHP, Java, ASP, etc.).

But I can't find any mention in the MySQL 5.0 stored procedure language
documentation of this capability in stored procedures. I am not aware
that the ANSI/ISO SQL3 "Persistent Stored Modules" standard includes
this functionality, and that is the model on which MySQL based their
stored procedure language.

So no, AFAICT this is not supported in MySQL. It's an extension to the
SQL standard, implemented in a proprietary way by Microsoft and perhaps
some other vendors.

Regards,
Bill K.

[Michael C#]

Re: Dynamic SQL in MySQL

Thanks Bill. Yeah, it all has to be done server-side in this case.

I was trying to convert some MS SQL code over to MySQL - specifically, some
dynamic SQL code that generates "pivot-table" type queries. Anyone have any
ideas on how that might be possible?

Thanks,
Michael C

"Bill Karwin" <bill@karwin.co m> wrote in message
news:ctjjsd016d 8@enews4.newsgu y.com...[color=blue]
> Michael C# wrote:[color=green]
>> Is it possible to create Dynamic SQL queries in MySQL, like in SQL
>> Server? i.e., the EXECUTE command or sp_executesql stored procedure in
>> SQL Server.[/color]
>
> Do you mean in general, or specifically in the MySQL stored procedure
> language?
>
> I regularly build SQL statements as strings in application code (Perl,
> PHP, Java, ASP, etc.).
>
> But I can't find any mention in the MySQL 5.0 stored procedure language
> documentation of this capability in stored procedures. I am not aware
> that the ANSI/ISO SQL3 "Persistent Stored Modules" standard includes this
> functionality, and that is the model on which MySQL based their stored
> procedure language.
>
> So no, AFAICT this is not supported in MySQL. It's an extension to the
> SQL standard, implemented in a proprietary way by Microsoft and perhaps
> some other vendors.
>
> Regards,
> Bill K.[/color]

[Michael C#]

Re: Dynamic SQL in MySQL **Correction**

I'm actually trying to do a "Cross-tab" type query. Sorry about the mix-up.
Any ideas?

Thanks,
Michael C.

"Bill Karwin" <bill@karwin.co m> wrote in message
news:ctjjsd016d 8@enews4.newsgu y.com...[color=blue]
> Michael C# wrote:[color=green]
>> Is it possible to create Dynamic SQL queries in MySQL, like in SQL
>> Server? i.e., the EXECUTE command or sp_executesql stored procedure in
>> SQL Server.[/color]
>
> Do you mean in general, or specifically in the MySQL stored procedure
> language?
>
> I regularly build SQL statements as strings in application code (Perl,
> PHP, Java, ASP, etc.).
>
> But I can't find any mention in the MySQL 5.0 stored procedure language
> documentation of this capability in stored procedures. I am not aware
> that the ANSI/ISO SQL3 "Persistent Stored Modules" standard includes this
> functionality, and that is the model on which MySQL based their stored
> procedure language.
>
> So no, AFAICT this is not supported in MySQL. It's an extension to the
> SQL standard, implemented in a proprietary way by Microsoft and perhaps
> some other vendors.
>
> Regards,
> Bill K.[/color]

[Bill Karwin]

Re: Dynamic SQL in MySQL **Correction**

Michael C# wrote:[color=blue]
> I'm actually trying to do a "Cross-tab" type query.[/color]

This is a new kind of problem to me, but after a few Google searches I
found several refereces to an example of generating cross-tabulation
queries in MySQL, by Giuseppe Maxia:

http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html

But if you need to do a dynamic cross-tab query, i.e. querying over an
arbitrary and unknown set of columns and tables, then I think you're out
of luck if you must do it in a stored procedure. There doesn't seem to
be any mechanism for fully dynamic server-side queries in MySQL. You
must do this kind of query in application code, where you can build a
string as a query statement, and then execute the finished query.

If you instead only need a finite number of static cross-tab queries,
you could write one stored procedure for each such query.

Regards,
Bill K.

[Michael C#]

Re: Dynamic SQL in MySQL **Correction**

Thanks again Bill. I googled the heck out of it, and I ended up with a
*lot* of articles on generating cross-tab queries in MS SQL (there are a
surprising number of examples that declare a VarChar variable named
"MySQL" -- go figure). I'm stuck in a situation where the number of columns
and rows is unknown, and varies from query to query... I was trying to
avoid generating the queries in presentation-layer code, but it looks like
that's what I'm going to be stuck with for now...

Thanks
Michael C


"Bill Karwin" <bill@karwin.co m> wrote in message
news:ctju6602eh 5@enews3.newsgu y.com...[color=blue]
> Michael C# wrote:[color=green]
>> I'm actually trying to do a "Cross-tab" type query.[/color]
>
> This is a new kind of problem to me, but after a few Google searches I
> found several refereces to an example of generating cross-tabulation
> queries in MySQL, by Giuseppe Maxia:
>
> http://www.onlamp.com/pub/a/onlamp/2...crosstabs.html
>
> But if you need to do a dynamic cross-tab query, i.e. querying over an
> arbitrary and unknown set of columns and tables, then I think you're out
> of luck if you must do it in a stored procedure. There doesn't seem to be
> any mechanism for fully dynamic server-side queries in MySQL. You must do
> this kind of query in application code, where you can build a string as a
> query statement, and then execute the finished query.
>
> If you instead only need a finite number of static cross-tab queries, you
> could write one stored procedure for each such query.
>
> Regards,
> Bill K.[/color]

[swdev2]

Re: Dynamic SQL in MySQL

and the answer is:
Make up a SPT codebase that works in Visual FoxPro , and slam it out to the
mySql database.

I dynamically generate SPT code all the time in my applications ...
I don't recommend you try this with VB - its such a sluff off with munging
data ..
for more info - see http://fox.wikis.com/wc.dll?Wiki~VFPmySQLLinuxP1~VFP
and http://groups.yahoo.com/group/vfpmysql

Going forward- you can have a server side component built in VFP that does
nothing but hold, store and execute SQL queries back at the mysql database,
and the component can be callable in WebServices, SOAP, IIS, ASP, and
ASP.NET [ug]

mondo regards [Bill]

--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
VFP Webhosting? You BET! -> http://efgroup.net/vfpwebhosting
mySql / VFP / MS-SQL

"Michael C#" <xyz@abcdef.com > wrote in message
news:1BdLd.4837 $9p1.398@fe08.l ga...[color=blue]
> Thanks Bill. Yeah, it all has to be done server-side in this case.
>
> I was trying to convert some MS SQL code over to MySQL - specifically,[/color]
some[color=blue]
> dynamic SQL code that generates "pivot-table" type queries. Anyone have[/color]
any[color=blue]
> ideas on how that might be possible?
>
> Thanks,
> Michael C
>
> "Bill Karwin" <bill@karwin.co m> wrote in message
> news:ctjjsd016d 8@enews4.newsgu y.com...[color=green]
> > Michael C# wrote:[color=darkred]
> >> Is it possible to create Dynamic SQL queries in MySQL, like in SQL
> >> Server? i.e., the EXECUTE command or sp_executesql stored procedure in
> >> SQL Server.[/color]
> >
> > Do you mean in general, or specifically in the MySQL stored procedure
> > language?
> >
> > I regularly build SQL statements as strings in application code (Perl,
> > PHP, Java, ASP, etc.).
> >
> > But I can't find any mention in the MySQL 5.0 stored procedure language
> > documentation of this capability in stored procedures. I am not aware
> > that the ANSI/ISO SQL3 "Persistent Stored Modules" standard includes[/color][/color]
this[color=blue][color=green]
> > functionality, and that is the model on which MySQL based their stored
> > procedure language.
> >
> > So no, AFAICT this is not supported in MySQL. It's an extension to the
> > SQL standard, implemented in a proprietary way by Microsoft and perhaps
> > some other vendors.
> >
> > Regards,
> > Bill K.[/color]
>
>
>[/color]

[Michael C#]

Re: Dynamic SQL in MySQL

Thanks Bill. I'll definitely take a look, but server-side FoxPro isn't an
option with my ISP. I'll get back to you whether it works or not.

Thanks again,
Michael C

"swdev2" <garage.sale@ef group.bob.net> wrote in message
news:gcdMd.2269 8$wi2.15970@new ssvr11.news.pro digy.com...[color=blue]
> and the answer is:
> Make up a SPT codebase that works in Visual FoxPro , and slam it out to
> the
> mySql database.
>
> I dynamically generate SPT code all the time in my applications ...
> I don't recommend you try this with VB - its such a sluff off with munging
> data ..
> for more info - see http://fox.wikis.com/wc.dll?Wiki~VFPmySQLLinuxP1~VFP
> and http://groups.yahoo.com/group/vfpmysql
>
> Going forward- you can have a server side component built in VFP that does
> nothing but hold, store and execute SQL queries back at the mysql
> database,
> and the component can be callable in WebServices, SOAP, IIS, ASP, and
> ASP.NET [ug]
>
> mondo regards [Bill]
>
> --
> William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
> email.
> Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
> Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
> Mondo Cool Satellites -> http://www.efgroup.net/sat
> VFP Webhosting? You BET! -> http://efgroup.net/vfpwebhosting
> mySql / VFP / MS-SQL
>
> "Michael C#" <xyz@abcdef.com > wrote in message
> news:1BdLd.4837 $9p1.398@fe08.l ga...[color=green]
>> Thanks Bill. Yeah, it all has to be done server-side in this case.
>>
>> I was trying to convert some MS SQL code over to MySQL - specifically,[/color]
> some[color=green]
>> dynamic SQL code that generates "pivot-table" type queries. Anyone have[/color]
> any[color=green]
>> ideas on how that might be possible?
>>
>> Thanks,
>> Michael C
>>
>> "Bill Karwin" <bill@karwin.co m> wrote in message
>> news:ctjjsd016d 8@enews4.newsgu y.com...[color=darkred]
>> > Michael C# wrote:
>> >> Is it possible to create Dynamic SQL queries in MySQL, like in SQL
>> >> Server? i.e., the EXECUTE command or sp_executesql stored procedure in
>> >> SQL Server.
>> >
>> > Do you mean in general, or specifically in the MySQL stored procedure
>> > language?
>> >
>> > I regularly build SQL statements as strings in application code (Perl,
>> > PHP, Java, ASP, etc.).
>> >
>> > But I can't find any mention in the MySQL 5.0 stored procedure language
>> > documentation of this capability in stored procedures. I am not aware
>> > that the ANSI/ISO SQL3 "Persistent Stored Modules" standard includes[/color][/color]
> this[color=green][color=darkred]
>> > functionality, and that is the model on which MySQL based their stored
>> > procedure language.
>> >
>> > So no, AFAICT this is not supported in MySQL. It's an extension to the
>> > SQL standard, implemented in a proprietary way by Microsoft and perhaps
>> > some other vendors.
>> >
>> > Regards,
>> > Bill K.[/color]
>>
>>
>>[/color]
>
>[/color]