Re: Calling SQL with embedded quotes
by virtualsue (Vicar) on May 21, 2002 at 08:40 UTC
|
If you were using the DBI module to access the database,
you could take advantage of the quote method,
which takes care of database-specific quoting automagically.
$test = $dbh->quote($test);
I have never done any sybase work, but the syntax of your
delete statement looks incorrect. Maybe that is what is
causing the problem?
| [reply] [d/l] [select] |
Re: Calling SQL with embedded quotes
by grep (Monsignor) on May 21, 2002 at 08:43 UTC
|
If at all possible I would switch to DBI instead of sybperl. DBI is the standard for perl database access and gives you a (mostly) consistant interface to whatever database you want to access wether it be Sybase or PostGreSQL or MySQL.
DBI and DBD::Sybase give you some nice methods for dealing with your quoting problem like placeholders and binding
But back to your original question, in the sybperl docs it explains you have both the 'dbsafestr' method and placeholders available with the 'ct_dyn_prepare' method. My personal preference is for placeholders since they make for efficient queries if reused.
Of course you can always go to the monastary's mpeppler's page and read about both.
grep
These are not the monks you are looking for, move along |
| [reply] [d/l] [select] |
|
I have to second use DBI instead. Also, you should get in the habit of using placeholders and binding, as they can make a huge difference on performance.
Where I'm working now, most of our old code didn't use placeholders and binding. Now that we are literally doing 1000X more transactions then when the program was written, we are feeling the pain. We've gone back and started to change all of our old code, and it has made a huge difference. Not only are we able to keep up now, but the program now responds noticeably faster then it did before. I see using placeholders and binding like using strict and warnings, you may not think you need to use them, but if you don't use them, it will come back to haunt you.
PS We use an Oracle database.
PPS We are doing this to both our perl and java code, and both languages are benifiting from using placeholders.
| [reply] |
|
| [reply] |
|
| [reply] |
Re: Calling SQL with embedded quotes
by mpeppler (Vicar) on May 21, 2002 at 16:04 UTC
|
Your sample above should work with the following:
$test = q/RunSql "delete sometable where strike like '%[A-Za-z]%'"/;
$test =~ s/'/''/g;
$sql = "update job set cmd='$test' where jobname='somejob'";
You use '' to quote the string that you set "cmd" to, so you just need to make sure that any ' quotes inside that string are doubled.
There are other methods as well - placeholders for example when using DBI or Sybase::CTlib as mentioned by others in this thread, but there are drawbacks to those as well - nothing's perfect :-)
Michael
| [reply] [d/l] |
|
Thanks all for the advice on placeholders and the use of DBI, both I will further investigate and learn. :)
The delete does work as indicated, but that delete statement is from a field already in an existing database. I'm just trying to read from one table, and store the field in another table. When I try to store the data, I always get an syntax error due to the inside single quotes. Sometimes there are backticks as well. I guess the real question was how do I stop Perl from interperting the command line as just text and not code that should be executed.
I'll try Michael's suggestion to see if that works for me.
Thanks again.
budman
| [reply] |
|
That was quick...
Just doubling the single quotes did the job!
Thanks.
budman
| [reply] |
Re: Calling SQL with embedded quotes
by davis (Vicar) on May 21, 2002 at 08:34 UTC
|
Should "delete sometable" be "delete from sometable"?
cheers.
davis
Is this going out live?
No, Homer, very few cartoons are broadcast live - it's a terrible strain on the animator's wrist
| [reply] |
|
| [reply] |
|
| [reply] [d/l] |