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 7 8383
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
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.
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" <bi**@karwin.co m> wrote in message
news:ct******** *@enews4.newsgu y.com... 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 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.
I'm actually trying to do a "Cross-tab" type query. Sorry about the mix-up.
Any ideas?
Thanks,
Michael C.
"Bill Karwin" <bi**@karwin.co m> wrote in message
news:ct******** *@enews4.newsgu y.com... 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 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# wrote: I'm actually trying to do a "Cross-tab" type query.
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.
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" <bi**@karwin.co m> wrote in message
news:ct******** *@enews3.newsgu y.com... Michael C# wrote: I'm actually trying to do a "Cross-tab" type query.
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.
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#" <xy*@abcdef.com > wrote in message
news:1B******** ********@fe08.l ga... 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" <bi**@karwin.co m> wrote in message news:ct******** *@enews4.newsgu y.com... 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
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.
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" <ga*********@ef group.bob.net> wrote in message
news:gc******** ***********@new ssvr11.news.pro digy.com... 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#" <xy*@abcdef.com > wrote in message news:1B******** ********@fe08.l ga... 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" <bi**@karwin.co m> wrote in message news:ct******** *@enews4.newsgu y.com... > 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 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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: George Adams |
last post by:
I like the idea of compiling DSO modules for Apache. It allows me to turn
on or off things we may or may not need at a given time (like mod_ssl,
mod_auth_mysql, mod_auth_ldap, etc.) and also allows me to compile in new
versions of modules without having to rebuild Apache from scratch.
Now, when I build PHP, I tend to put in a lot of things....
|
by: John Wells |
last post by:
Yes, I know you've seen the above subject before, so please be gentle with
the flamethrowers.
I'm preparing to enter a discussion with management at my company
regarding going forward as either a MySql shop or a Postgresql shop.
It's my opinion that we should be using PG, because of the full ACID
support, and the license involved. A...
|
by: Angelos |
last post by:
Hello again,
I have this dynamic menu and I want to change the order of the menu items...
I added a column in the database wich has an integer value for ordering the
menuitems.
But the only way is by hardcoding them into the database.
I would like a way to sort them with drag-and-drop or with up/down arrows.
Do you think that except...
|
by: ElkGroveR |
last post by:
Hi there!
I'm using PHP to create a simple, dynamic MySQL SELECT query.
The user chooses a selection from a HTML Form SELECT element's many options
and submits the form via a POST action.
The SELECT query is built as follows:
$itemtype = stripslashes(trim($_POST));
|
by: alf |
last post by:
Hi,
is it possible that due to OS crash or mysql itself crash or some e.g.
SCSI failure to lose all the data stored in the table (let's say million
of 1KB rows). In other words what is the worst case scenario for MyISAM
backend?
Also is it possible to not to lose data but get them corrupted?
| |
by: edfialk |
last post by:
Hi all, I'm desperately trying to get a simple mysql connection
working in php 4.3.9 and I think I have a doozy for you guys. First
of all, I didn't set up ANY of this system, I'm just working with it.
So, on Redhat, we have two versions of PHP - Apache uses 4.3.9,
there's also a 4.4.2 that is default from command-line. 4.3.9 was...
|
by: traineeirishprogrammer |
last post by:
I am currently working on a project where I need to sort my MYSQL query results by different categories. How ever the code does not seem to be working properly and I spend too much time on it already.
So here is the code:
if (isset($_GET)){$sort = $_GET;}
else $sort = '';
$query = "SELECT * FROM list WHERE county = '$counties'...
|
by: ssnaik84 |
last post by:
Hi Guys,
Last year I got a chance to work with R&D team, which was working on DB scripts conversion..
Though there is migration tool available, it converts only tables and constraints..
Rest of things (stored procedures, functions).. we have to manually edit.
That time, we face some interesting challenges.. I failed to document all of them,...
|
by: remya1000 |
last post by:
i'm using VB \ ASP.NET.
i'm trying to display some values in Gridview. but i don't know how many columns i need to display. at run time only i will come to know how many columns i need to display. it depends up on the values from Database. so i'm creating columns dynamically and adding to gridview. till that it works fine. but i want some...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |