[DB-SIG] Parameters in DCOracle2

Marcos Sánchez Provencio msanchez@grupoburke.com
Wed, 21 Aug 2002 14:17:20 +0200 (CEST)


I am not going to defend this database, it is a port of a port of a port...
It started with vms/db2, in Cobol, now it's using ProC and whatever. If i'm
lucky i get to do the new version (not patches) after 15 years.

And yes, they are using char :-\

I hope to ask better questions from now on and promise not to write when I'm
angry :-)

> 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ánchez 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?
>>
>> select * from tb where fd1='SAENS'
>> [returns rows]
>>
>> select * from tb where fd1=:1 //// 'SAENS'
>> [returns nothing]
>>
>> 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.
>>
>> El mar, 20-08-2002 a las 02:12, James Briggs escribió:
>> >
>> > To avoid breaking the index usage, you could try:
>> >
>> >     cursor.execute("select * from table where nombre like :1||'%'",
>> nombre)
>> >
>>
>> 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...
>> >
>> > buena suerte
>> >
>> Hasta la vista
>>
>> > James
>> >
>> > >>> Marcos Sánchez Provencio <msanchez@grupoburke.com>
20/08/2002
>> 4:04:36 am >>>
>> > I expect this to break index usage, I suppose...
>> >
>> > El lun, 19-08-2002 a las 15:05, Matthew T. Kromer escribió:
>> > > I think you're really after something like
>> > >
>> > >       cursor.execute("select * from table where RTRIM(nombre) =
>> > >       :1",
>>
>> > > nombre)
>> > >
>> > > Although I'm away from my manual to look up RTRIM to see if you
>> > > need
>> to
>> > > pass it a 2nd parameter to tell it to trim trailing spaces.
>> > >
>> > >
>> > > On Monday, August 19, 2002, at 05:27 AM, Marcos Sánchez Provenci=
o
>> wrote:
>> > >
>> > > > 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_
>> > > > to
>> > > > thefull length of the column, which is 50. So, the non-parameter
>> query
>> > > > does find
>> > > > results, but the parametrised query only finds results when
>> providing
>> > > > the
>> > > > exactnumber of spaces (5 in this case). I can't touch the data
>> > > > in
>> the
>> > > > 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='SAENS'
>> > > >>
>> > > >> but not if I say
>> > > >>
>> > > >>    where nombre=: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
>> > > >> http://mail.python.org/mailman/listinfo/db-sig
>> > > >
>> > > >
>> > > >
>> > > >
>> > > > _______________________________________________
>> > > > DB-SIG maillist  -  DB-SIG@python.org
>> > > > http://mail.python.org/mailman/listinfo/db-sig
>> > >
>> > >
>> > > _______________________________________________
>> > > DB-SIG maillist  -  DB-SIG@python.org
>> > > http://mail.python.org/mailman/listinfo/db-sig
>> >
>> >
>> >
>> > _______________________________________________
>> > DB-SIG maillist  -  DB-SIG@python.org
>> > http://mail.python.org/mailman/listinfo/db-sig
>>
>>
>>
>> _______________________________________________
>> DB-SIG maillist  -  DB-SIG@python.org
>> http://mail.python.org/mailman/listinfo/db-sig
> --
> Anthony Tuininga
> anthony@computronix.com
>
> 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