MySQL and ADODB Command/Parameter objects 1

[TheInsider]

Hi,

In Microsoft SQL Server you can use an ADODB Command object with a Parameter object to set or return values from a query using the "?" operator

i.e "SELECT ? = fldLastName FROM tblClients where fldFirstName = 'Tom'"

Here a value is assigned to ? from fldLastName and that value will be available to my code in Visual Basic through the Parameter object.

As far as I know MySQL 4.1 doesn't recognize the ? operator. How can a Command/Parameter object be used to dynamically set and retrieve values from SQL statements in MySQL 4.1?

Thanks

 

[TonyGroves]

MySQL 4.1 does support prepared statements using ? placeholders. See

http://dev.mysql.com/doc/mysql/en/sqlps.html

.

For example, you could start with:[tt]
PREPARE s1 FROM
'
SELECT name,address
FROM members
WHERE region=? and memberid=?
';[/tt]
Then, for each memberid value:[tt]
SET @reg=15,@mid=12345;
EXECUTE s1 USING @reg,@mid;[/tt]

This feature is implemented on the server, independent of the language API used, and it is a one-way system only; the parameters are placeholders for static values which are supplied by the client.

Your example would not be possible with MySQL, but anyway I can't quite see what advantages it would offer over a non-parameterised statement.

 

[TheInsider]

Thanks. Basically I wanted to use a Command & Parameter object to return value(s) from a SELECT statement without the overhead of using a Recordset object. The nice thing about SQL Server is that you can send and receive parameters with the Command and Parameter objects. In this case I was only interested in retrieving values.