[DB-SIG] Parameters in DCOracle2
Anthony Tuininga
anthony@computronix.com
20 Aug 2002 13:07:56 -0600
Would you mind posting your table definition? I am suspecting that you
are using "char" as a data type rather than "varchar2" which is what
Oracle recommends. If you use "char", strange things start to happen
because Oracle will convert some arguments to "varchar2" automatically
and others it won't. The internal representation of the "char" data type
includes spaces, unfortunately, so you have to match perfectly or have
Oracle do some implicit conversion for you or do your own explicit
conversion. Why not just use "varchar2" and have done with it!!?
On Tue, 2002-08-20 at 13:41, Marcos S=E1nchez Provencio wrote:
> Thank you, really, but it must be a political question of mine not to do
> that. I refuse to have a system that is so costly to do everything in
> the program then. Why does the system return different results using
> exactly the same string as a parameter and as a constant string?
>=20
> select * from tb where fd1=3D'SAENS'
> [returns rows]
>=20
> select * from tb where fd1=3D:1 //// 'SAENS'
> [returns nothing]
>=20
> What are the _real_ advantages of using Oracle versus Sybase? I find it
> full of little nasty things that make it impossible to work fluently.
>=20
> El mar, 20-08-2002 a las 02:12, James Briggs escribi=F3:
> >=20
> > To avoid breaking the index usage, you could try:
> >=20
> > cursor.execute("select * from table where nombre like :1||'%'",
> nombre)
> >=20
>=20
> Or
> between :1 and :1 || ' '
> but I still don't like it...
> > Of course you still have to test the nombre you now have is "SAENS"
> with optional trailing spaces and not "SAENSA", but at least your using
> the database index to only fetch the rows that possible match. I guess
> the ugliness of the extra code is the price for having trailing spaces
> in the database...
> >=20
> > buena suerte
> >=20
> Hasta la vista
>=20
> > James
> >=20
> > >>> Marcos S=E1nchez Provencio <msanchez@grupoburke.com> 20/08/2002
> 4:04:36 am >>>
> > I expect this to break index usage, I suppose...
> >=20
> > El lun, 19-08-2002 a las 15:05, Matthew T. Kromer escribi=F3:
> > > I think you're really after something like
> > >=20
> > > cursor.execute("select * from table where RTRIM(nombre) =3D :1"=
,
>=20
> > > nombre)
> > >=20
> > > Although I'm away from my manual to look up RTRIM to see if you need
> to=20
> > > pass it a 2nd parameter to tell it to trim trailing spaces.
> > >=20
> > >=20
> > > On Monday, August 19, 2002, at 05:27 AM, Marcos S=E1nchez Provencio
> wrote:
> > >=20
> > > > First, thank you for your help, I have found out several new
> things.
> > > >
> > > > The value is stored in the table as 'SAENS ' with spaces, but
> _not_=20
> > > > to
> > > > thefull length of the column, which is 50. So, the non-parameter
> query=20
> > > > does find
> > > > results, but the parametrised query only finds results when
> providing=20
> > > > the
> > > > exactnumber of spaces (5 in this case). I can't touch the data in
> the=20
> > > > table. Is
> > > > theresome way to get the same results as a SQL*Plus window?
> > > >
> > > > PS. I use now named parameters correctly. Thanks.
> > > >
> > > >> Hello, I am having a problem trying to get some data from an
> Solaris
> > > >> Oracle 7.3.3 server. I use the 8.1.7 client in the same machine.
> The
> > > >> problem is as follows:
> > > >>
> > > >> I get results if I say
> > > >>
> > > >> where nombre=3D'SAENS'
> > > >>
> > > >> but not if I say
> > > >>
> > > >> where nombre=3D:p1
> > > >>
> > > >> and pass a parameter 'SAENS', fetchall returns an empty list
> > > >>
> > > >> It is funny that I get some results if I say
> > > >>
> > > >> where nombre between :p1 and :p1 || 'Z'
> > > >>
> > > >> or (almost) what I want by saying
> > > >>
> > > >> where nombre like :p1 || ' %'
> > > >>
> > > >> I have been puzzled by Oracle since I am using it (I had used
> Sybase
> > > >> before). Is this an Oracle issue or a Python one?
> > > >>
> > > >> Thank you very much
> > > >>
> > > >>
> > > >>
> > > >> _______________________________________________
> > > >> DB-SIG maillist - DB-SIG@python.org=20
> > > >> http://mail.python.org/mailman/listinfo/db-sig=20
> > > >
> > > >
> > > >
> > > >
> > > > _______________________________________________
> > > > DB-SIG maillist - DB-SIG@python.org=20
> > > > http://mail.python.org/mailman/listinfo/db-sig=20
> > >=20
> > >=20
> > > _______________________________________________
> > > DB-SIG maillist - DB-SIG@python.org=20
> > > http://mail.python.org/mailman/listinfo/db-sig=20
> >=20
> >=20
> >=20
> > _______________________________________________
> > DB-SIG maillist - DB-SIG@python.org=20
> > http://mail.python.org/mailman/listinfo/db-sig
>=20
>=20
>=20
> _______________________________________________
> DB-SIG maillist - DB-SIG@python.org
> http://mail.python.org/mailman/listinfo/db-sig
--=20
Anthony Tuininga
anthony@computronix.com
=20
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada T5N 4A3
Phone: (780) 454-3700
Fax: (780) 454-3838
http://www.computronix.com