[DB-SIG] Parameters in DCOracle2
James Briggs
James.Briggs@unisuper.com.au
Wed, 21 Aug 2002 09:55:31 +1000
>From the first example I see fd1 in tb must be defined as a CHAR type.
This is valid point, CHAR and VARCHAR behave differently in Oracle. You =
should consider CHAR to be a fixed length string with trailing spaces =
attached. All comparisons are done by attaching trailing spaces to =
constant strings (which is why example 1 below returns rows). All selects =
with DCOracle2 Bind variables in the DCOracle2 interface (and everyother =
Oracle interface I have tried) convert strings to VARCHARs which when =
compared with a CHAR produce these weird results.
As an oracle DBA I always craete tables with VARCHARs because of these =
issues and the fact that VARCHARs don't store all those extra spaces on =
disk somewhere, important when there are millions of rows. CHARs are =
really for backward compatability and situations where fix length strings =
are necessary.
Options:
1) Convert your CHAR columns to VARCHAR2 columns in the database (recommend=
ed by Oracle, but politically unacceptable I think...)
2) Dynamically recreate all selects using string manipulation, inefficient =
as it involves a lot of string manipulation and re-parsing in Oracle. But =
the comparisons are now done with Oracle literals and will now work as =
expected.
3) Tweak or Force DCOracle2, or the interface of your choic,e to convert =
string bind variables to CHAR datatypes not VARCHAR datatypes, using =
DCOracle2.TypeCoercion??? Not sure this is possible, I haven't had access =
to a database to test this yet. I do know in the C layer it is possible to =
define arrays to be mapped to the CHAR type...
Onto your question regarding Sybase and Oracle, my background is Oracle, =
but I am pretty sure both can work well and have their quirks. Oracle's =
biggest advantages probably come with third-party tools available(?), huge =
databases, table partitioning, and running on Unix clusters etc. There =
heaps of quirks and options when it comes to setting up your database, =
which is why it can run on a PC upto multi-terabyte machines. This is what =
keeps me in work ;)=20
James
>>> Anthony Tuininga <anthony@computronix.com> 21/08/2002 5:07:56 am >>>
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
>=20
> _______________________________________________
> DB-SIG maillist - DB-SIG@python.org=20
> http://mail.python.org/mailman/listinfo/db-sig=20
--=20
Anthony Tuininga
anthony@computronix.com=20
=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=20