SQL Parameter Issues.

SQL Parameter Issues.

Post by Michae » Thu, 21 Jul 2005 11:59:09


Hi,

Is there something I'm missing about using ParamByName with Table Names?
ParamByName works fine for values, but when I use them to insert a tablename
it
never seems to want to work???
Database: MYSQL

For Example:
=========
Transactions.Active := False;
Transactions.SQL.Clear;
Transactions.SQL.LoadFromFile('sql\startaccount.sql');
// startaccount.sql contains INSERT INTO :dbn (blah) VALUES (:avalue)
Transactions.ParamByName('dbn').AsString := CustCode;
// Error is to do with the :dbn Param.
Transactions.ParamByName('avalue').AsString := 'something';
=========

This just gives me an error stating that the query is wrong. However if I
manually put the tablename in without using ParamByName it works fine. But
I cannot manually enter the tablename as each client has their own table.
Anything missing here that I should know about?

TIA

Mick.
 
 
 

SQL Parameter Issues.

Post by Wayne Nidd » Thu, 21 Jul 2005 12:33:43


Correct, parameters are only for field values. The purpose of parameterized
queries, for database engines that support it, is to allow the engine to
prepare and optimize the query for you, allowing you to execute that query
multiple times by just passing different parameters instead of having to
pass the entire SQL and preparing it again. But a query cannot be prepared
and optimized on an unknown table.

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.yqcomputer.com/
Reality is that which, when you stop believing in it, doesn't go away.
Philip K. ***

 
 
 

SQL Parameter Issues.

Post by Michae » Thu, 21 Jul 2005 12:39:58

Anyway around it? I mean, without having to put the sql in by hard coding
it?

Thx.






parameterized
 
 
 

SQL Parameter Issues.

Post by Bill Tod » Thu, 21 Jul 2005 22:16:46

No. As Wayne said, you cannot use parameters for anything but values.
If the table name were unknown it would be impossible for the server to
optimize the query.

--
Bill Todd (TeamB)
 
 
 

SQL Parameter Issues.

Post by Yanni » Fri, 22 Jul 2005 01:08:59


A number of alternatives come to mind

1)
Use a fixed variable name for all the tables on all you queries and
o load the file in to a string using either tstringlist or text files.
o use stringreplace to replace the variable name with the actual
table
name
o set the query and use the parameters as needed
2) instead of using a variable name use %S constanst and
o load the file in to a string using either tstringlist or text files.
o Use Format to replace %S with the table
o set the query sql command and play with the parameters as needed.

and some other options that will require more coding from your part.

I do not know what are the requirements for your application and what
you try to accomplish here so based on the sql you have posted I
recommend the use of stringReplace as the simplest solution to your
problem. Please keep in mind that if the sql statement which you read
from a file is more complicated (eg has more than one tables inner
joins etc) then it might be a good time to re evaluate your design.

Regards
Yannis.