Re: [Libdbi-drivers-devel] libdbi oracle driver based on ocilib
Hi markus, I'm coming back about an ocilib based Oracle driver for libdbi. About FreeBSD, There's no official support or package from Oracle Corp. A port has been done and seems to work. The last few weeks, i was in contact with an italian that tried to port OCILIB to FreeBSD. OCILIB compiles well on FreeBDS but there is a problem with the version of the lib C used for the Oracle Client port. I'm still waiting for news... So, the last package i sent you was working well except the numeric types problems... It's not possible to know what size a numeric value really is (1/2/4/8 bytes integers or even numeric...) When you create an INT column or a SHORT column, the storage is the same : oracle NUMBER. Even SQL*PLUS would cannot make the difference. Only the server knows the difference for range checking.. Oracle is different from other database because the client program that tells Oracle how it wants to retreive/insert the value. So, i'm still suck in the ocilib based libdbo driver to set the integer type bit... For example, a column created as INT (4 bytes integer range) or NUMBER (38 digits for scale and precision that can holds integer, numeric, real, etc..) will be reported by Oracle client library (OCI) as NUMBER... There's no way to make nay difference... So my question : how to deal with that into an libdbi driver ? PS : about the actual oracle libdbi driver : the driver can't even compile (syntax problems) and is wrong (LONG types, etc..) and buggy. I'm sure no one has tested it... Regards, Vincent. 2008/11/13 Markus Hoenicka markus.hoeni...@mhoenicka.de vincent rogier writes: I believe it would be more appropriate to call the driver ocilib Sure :). I'll change that Don't rush it. I hoped to get some feedback about the status and the capabilities of the existing oracle driver (which I can't test myself). If the ocilib driver surpasses the oracle driver and is better maintained, then the existing driver is obsolete and should be replaced by the ocilib driver. In that case, oracle wouldn't be a misnomer. However, if for some reason both drivers have to coexist, I'd rather avoid the name clash. Btw, i've got one question about libdbi integer types... Oracle does not make any difference between 1, 2, 4 or 8 bytes integer (from an OCI point of vue). So, as far i've seen about libdbi implementation of datatype (use of unions and checks on the integer type flag), there 's no easy and pretty way to deal with it does all other libbdi drivers have no problems like that ? SQlite is essentially typeless. I.e. it won't even distinguish between text and integer. SQLite3 has something called column affinities which helps to distinguish between text and other data types. However, there is no support for different integer types either. The sqlite/sqlite3 drivers use whatever type the column was declared with. Although it doesn't make a difference to SQLite, you *can* declare a column as TINYINT, SMALLINT, INTEGER, or BIGINT, and the drivers rely on you (as a programmer) doing so. OTOH, SQLite wouldn't barf if you store a long long value in a column declared as TINYINT. You wouldn't even lose data if you retrieve the value with sqlite. libdbi would reduce the data to a 1 byte integer upon retrieval though. How does Oracle deal with this? Is there just one integer type? What size is it? Are there any compatibility types to deal with imported data, like MySQL and other engines do? regards, Markus -- Markus Hoenicka markus.hoeni...@cats.de (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- Vincent Rogier -- Register Now Save for Velocity, the Web Performance Operations Conference from O'Reilly Media. Velocity features a full day of expert-led, hands-on workshops and two days of sessions from industry leaders in dedicated Performance Operations tracks. Use code vel09scf and Save an extra 15% before 5/3. http://p.sf.net/sfu/velocityconf___ Libdbi-drivers-devel mailing list Libdbi-drivers-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-drivers-devel
Re: [Libdbi-drivers-devel] sqlite selects for bigint return 0
Hello, I will verify it. 2009/5/2 Mariusz Mazur mma...@kernel.pl: I assume sqlite is mostly off the radar and just seen as legacy, but I think this should at least get a mention in bacula's docs: sqlite via libdbi is majorly broken (standard 686 platform). Reason is this: selects for bigints (and bacula's volume sizes are bigints) always return a 0 no matter what the actual value is. I've confirmed this using bacula's debugging output (the native 'sqlite' client showed correct values with the exact same SELECT), but didn't bother with an actual stand-alone test case and just migrated to postgres. People at least should be aware of the problem (even if it's something extremely specific to my configuration, which I doubt). --mmazur -- --- João Henrique Freitas - joaohf_at_gmail.com Campinas-SP-Brasil BSD051283 LPI 1 http://www.joaohfreitas.eti.br -- Register Now Save for Velocity, the Web Performance Operations Conference from O'Reilly Media. Velocity features a full day of expert-led, hands-on workshops and two days of sessions from industry leaders in dedicated Performance Operations tracks. Use code vel09scf and Save an extra 15% before 5/3. http://p.sf.net/sfu/velocityconf ___ Libdbi-drivers-devel mailing list Libdbi-drivers-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-drivers-devel
Re: [Libdbi-drivers-devel] libdbi oracle driver based on ocilib
vincent rogier writes: So, i'm still suck in the ocilib based libdbo driver to set the integer type bit... For example, a column created as INT (4 bytes integer range) or NUMBER (38 digits for scale and precision that can holds integer, numeric, real, etc..) will be reported by Oracle client library (OCI) as NUMBER... There's no way to make nay difference... So my question : how to deal with that into an libdbi driver ? The sqlite/sqlite3 drivers have to deal with a similar problem. sqlite is essentially typeless, so you can't even distinguish between numbers and text. sqlite3 has something like sticky types but these distinguish only between text and numeric data. The sqlite/sqlite3 drivers have to retrieve the command which was used to create a table (this is fortunately stored in each sqlite database) and figure out which type to use as a return value. This approach has its problems (think functions which mangle values retrieved from several columns) but it mostly works ok. Would that approach be useful for an ocilib based driver? PS : about the actual oracle libdbi driver : the driver can't even compile (syntax problems) and is wrong (LONG types, etc..) and buggy. I'm sure no one has tested it... AFAIK there is no current oracle driver maintainer. Several people have fiddled with the code in the past, but at this time hardly anyone is able to even run the tests in lack of an Oracle installation. It may very well be true that the driver is broken. I can't verify this. regards, Markus -- Markus Hoenicka markus.hoeni...@cats.de (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- Register Now Save for Velocity, the Web Performance Operations Conference from O'Reilly Media. Velocity features a full day of expert-led, hands-on workshops and two days of sessions from industry leaders in dedicated Performance Operations tracks. Use code vel09scf and Save an extra 15% before 5/3. http://p.sf.net/sfu/velocityconf ___ Libdbi-drivers-devel mailing list Libdbi-drivers-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-drivers-devel
Re: [Libdbi-drivers-devel] libdbi oracle driver based on ocilib
No, the sqllite trick cannot be used with Oracle... And even if it was possible, it is not a serious option because it would kill the driver performance People are still coding DB apps in C because it's faster... If a C driver has to use exotics and slow behaviors to work, what's the point ? The fact libdbi has to know the number of rows returned by a query, before any fetch, is already a performance bottleneck for some DB, such as Oracle. From Oracle 9i, it's possible to know that, with some big performance loss...(eg. Oracle 8i can't be supported) In the end, the only thing that i need to finish the ocilib based libdbi driver is to solve this problem of integer flags and bits.. Any ideas ? Vincent 2009/5/3 Markus Hoenicka markus.hoeni...@mhoenicka.de vincent rogier writes: So, i'm still suck in the ocilib based libdbo driver to set the integer type bit... For example, a column created as INT (4 bytes integer range) or NUMBER (38 digits for scale and precision that can holds integer, numeric, real, etc..) will be reported by Oracle client library (OCI) as NUMBER... There's no way to make nay difference... So my question : how to deal with that into an libdbi driver ? The sqlite/sqlite3 drivers have to deal with a similar problem. sqlite is essentially typeless, so you can't even distinguish between numbers and text. sqlite3 has something like sticky types but these distinguish only between text and numeric data. The sqlite/sqlite3 drivers have to retrieve the command which was used to create a table (this is fortunately stored in each sqlite database) and figure out which type to use as a return value. This approach has its problems (think functions which mangle values retrieved from several columns) but it mostly works ok. Would that approach be useful for an ocilib based driver? PS : about the actual oracle libdbi driver : the driver can't even compile (syntax problems) and is wrong (LONG types, etc..) and buggy. I'm sure no one has tested it... AFAIK there is no current oracle driver maintainer. Several people have fiddled with the code in the past, but at this time hardly anyone is able to even run the tests in lack of an Oracle installation. It may very well be true that the driver is broken. I can't verify this. regards, Markus -- Markus Hoenicka markus.hoeni...@cats.de (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- Vincent Rogier -- Register Now Save for Velocity, the Web Performance Operations Conference from O'Reilly Media. Velocity features a full day of expert-led, hands-on workshops and two days of sessions from industry leaders in dedicated Performance Operations tracks. Use code vel09scf and Save an extra 15% before 5/3. http://p.sf.net/sfu/velocityconf___ Libdbi-drivers-devel mailing list Libdbi-drivers-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-drivers-devel
