error DBI with quote

[Jesús Lasso Sánchez <jl...@ya.com>]

Hi,

  I have a problem with DBI and Oracle. I have an input form where i insert a phrase like 

      "Mike's car"

 when i try to do the insert, it produces an error: "not valid sentence". this is the code:

      $sql=qq{INSERT INTO TABLE_NAME (PHRASE) VALUES (?)};
      my $insert_phrase=$dbh->prepare($sql);
      $sql->bind_param(1,$dbh->quote($phrase),SQL_VARCHAR);
      $insert_phrase->execute();

Anybody know something about this

  thanks

______________________________________
Jesús Lasso - Ya.com Internet Factory
jlasso@ya.com
www.ya.com - www.globalya.com

Re: error DBI with quote

[Rodney Broom <rb...@home.com>]

Ack! I can't find the string "not valid sentence" in DBI.pm, Apache/DBI.pm, or
in Oracle.pm, so I'll ignore it.

Here's your code:

      $phrase = "Mike's car"
      $sql=qq{INSERT INTO TABLE_NAME (PHRASE) VALUES (?)};
      my $insert_phrase=$dbh->prepare($sql);
      $sql->bind_param(1,$dbh->quote($phrase),SQL_VARCHAR);
      $insert_phrase->execute();

There are several little problems it this chunk that lead me to believe that
this was not copy-n-pasted from the actual code. But who cares, try this:

      $phrase = "Mike's car"
      $sql=qq{INSERT INTO TABLE_NAME (PHRASE) VALUES (?)};
      #-- The return from prepare() is a statement handle
      my $insert_sth = $dbh->prepare($sql);
      $insert_sth->execute($phrase);

That's it, just pass a list of values into execute(). As for Michael's comment
of not being sure whether you can use prepare() for non-SELECT-statements, the
answer is yes. In fact, all statements have to be prepare()ed when using DBI,
it's just a matter of who does the prepare(), you or DBI.

----
Rodney Broom

Re: error DBI with quote

[dr...@smartt.com]

Jesús Lasso Sánchez wrote:
> 
> Hi,
> 
>    I probed:
> 
>           s/'/''/g
> 
>    yesterday but it did'nt work, will try with HTML code. I think is the
> solution.
> 
> Thanks
> 
> > the easiest way is
> > s/'/''/g
> > or
> > s/'/'/g
> >


the way i do it is

my $q=new CGI;
$var=$q->param('var');

$var=s/'/''/g;


btw: this code is off the top of my head

Re: error DBI with quote

[Michael Hanisch <ha...@informatik.uni-muenchen.de>]

On Thu, 6 Jul 2000, Jesús Lasso Sánchez wrote:

> Hi,
> 
>    I probed:
> 
>           s/'/''/g
> 
>    yesterday but it did'nt work, will try with HTML code. I think is the
> solution.
> 
> 
> Thanks
> 
> > the easiest way is
> > s/'/''/g
> > or
> > s/'/'/g
> >
> 
> 

Hi,

AFAIK DBI's bind_param method should call DBI::quote on its arguments. 
However, I'm not sure whether you can use 'prepare' for
non-SELECT-statements; have you tried 

$sth->do('INSERT ... VALUES(?)', undef, $phrase) 

instead?

Maybe you should give the dbi-users mailing list (dbi-users@isc.org) 
a try.

HTH,
	Michael.

________________________________________________________
Michael   |  email: hanisch@informatik.uni-muenchen.de 
Hanisch   |                                            
________________________________________________________

Re: error DBI with quote

[Jesús Lasso Sánchez <jl...@ya.com>]

Hi,

   I probed:

          s/'/''/g

   yesterday but it did'nt work, will try with HTML code. I think is the
solution.


Thanks

> the easiest way is
> s/'/''/g
> or
> s/'/'/g
>

Re: error DBI with quote

[dr...@smartt.com]

Jesús Lasso Sánchez wrote:
> 
>    Part 1.1    Type: Plain Text (text/plain)
>            Encoding: quoted-printable

yes ! because ' is a start or end of sql statement you 
have to escape it
either convert to html equiv 
' i think
or convert ' to '' in postgresql i dont know what it is in mysql

the easiest way is
s/'/''/g
or 
s/'/'/g