From: Philipp Meier <meier@...>
Date: 2002-08-14T08:11:37+09:00
Subject: Re: DBI Error when attempting to pass Oracle's SYSDATE to a place hol der


--mojUlQ0s9EVzWg2t
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Aug 14, 2002 at 07:54:07AM +0900, Berge, Robert wrote:
> I'm using Ruby 1.6.7, Ruby/dbi  .15 and I'm trying to call an Oracle stor=
ed
> proc where the third parameter is of DATE type.  I want to pass in the
> current date & time by using SYSDATE.  If I do the following everything
> works as it should.
> dbh.do( "BEGIN SOME_PROC( 1, 1, SYSDATE ); END" )
> =20
> However, if I try to use place holders as below
> sql =3D "BEGIN SOME_PROC( ?, ?, ? ); END"
> dbh.do( sql, 1, 1, SYSDATE )=20

Try the following:


 sql =3D "BEGIN SOME_PROC( ?, ?, SYSDATE ); END"
 dbh.do( sql, 1, 1)

There is no need to repalce all parmameters in a query if you use
perpared statements and parameter binding via "?".

> Oracle returns an error because of the SYSDATE value.  Even putting quotes
> around SYSDATE doesn't do any good.  Using similar code I get the same er=
ror
> using perl.

SYSDATE is an ocracle function. With your code ruby trys to find the
constant "SYSDATE" which does not exist in ruby. Therefore your code cannot=
 work.=20

> Even doing the following works:
> Dbh.do( sql, 1, 1, "#{Time.new.strftime( \"%d-%b-%Y\" ) }" )   Does work.=
=20

Here, ruby evaluates "Time.new...." which works of course.

> Has anyone else seen this error?  Is this an error in DBI or in Oracle?  =
Is
> there a way to work around this problem in the current environment?

See above.

My two cents,
	-billy.

--=20
Meisterbohne       S=F6flinger Stra=DFe 100          Tel: +49-731-399 499-0
   eL=F6sungen       89077 Ulm                     Fax: +49-731-399 499-9

--mojUlQ0s9EVzWg2t
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE9WZIjfBriNoqItSYRAjQdAJ99TNigJPN1grKNQfwdMnshj1+8NwCeKTqt
qjrVF7sHErCwBM00nEK81Mc=
=NByh
-----END PGP SIGNATURE-----

--mojUlQ0s9EVzWg2t--