 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Michael
Guest
|
 Posted: Wed Jul 20, 2005 2:59 am Post subject: SQL Parameter Issues. |
 |
|
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('sqlstartaccount.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.
|
|
| Back to top |
|
 |
Wayne Niddery [TeamB]
Guest
|
| Posted: Wed Jul 20, 2005 3:33 am Post subject: Re: SQL Parameter Issues. |
|
|
Michael wrote:
| Quote: |
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???
|
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.logicfundamentals.com/RADBooks.html
Reality is that which, when you stop believing in it, doesn't go away.
— Philip K. Dick
|
|
| Back to top |
|
|
Michael
Guest
|
| Posted: Wed Jul 20, 2005 3:39 am Post subject: Re: SQL Parameter Issues. |
|
|
Anyway around it? I mean, without having to put the sql in by hard coding
it?
Thx.
"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote
| Quote: | Michael wrote:
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???
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.logicfundamentals.com/RADBooks.html
Reality is that which, when you stop believing in it, doesn't go away.
- Philip K. Dick
|
|
|
| Back to top |
|
|
Bill Todd
Guest
|
| Posted: Wed Jul 20, 2005 1:16 pm Post subject: Re: SQL Parameter Issues. |
|
|
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)
Michael wrote:
| Quote: | Anyway around it? I mean, without having to put the sql in by hard
coding it?
|
|
|
| Back to top |
|
|
Yannis
Guest
|
| Posted: Wed Jul 20, 2005 4:08 pm Post subject: Re: SQL Parameter Issues. |
|
|
Michael wrote:
| Quote: | Anyway around it? I mean, without having to put the sql in by hard
coding it?
Thx.
|
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.
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
