Hello All,
I just started to take a look at the new rt files, and have some
interesting issues that will need to be dealt with if we’re going to port to
Oracle/DB2/other enterprise DBs. Looking at the schema.mysql file, it looks
like we shouldn’t have a problem making a similar file for the other DBs.
MY question is, how abstract is the RT DBI code itself? I was looking at
the files in lib/rt/database, and while it’s close to being able to work
with these DBs, there is one real important thing missing off the bat.
Schema.
MySQL doesn’t require the schema to be in there (As a matter of
fact, I think it all falles under a ‘mysql.’ schema) but the larger DBs do.
Is that something that there is a planned workaround for, or will we need to
re-write the lib files as well?
The only other thing I can see, and I don’t know how Oracle handles
this, is that DB2 will puke if you attempt to call a CHAR or VARCHAR column
without enclosing the values in 's. Likewise, INT and similar numeric
columns will puke if you DO enclose them with anything. If I recall
correctly, MySQL has no such restraints.
I’ll keep searching through it and see what else I can see.
Cheers,
Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation
DBI has something that directly addresses this quoting problem:
$dbh->quote($string) and
$dbh->quote($string, $data_type)
These will use the DBD to quote the data as appropriate for the database
and the data type. This works for Oracle. It’s almost a must for quoting
between different databases: some need ’ and \ quoted, %, etc.
Another really great solution that also markably improves efficiency is to
use bind parameters. You don’t need to quote things at all:
my $sql = “SELECT foo FROM table WHERE col=?”; #? is a placeholder
my $sth = $dbh->prepare($sql);
$sth->bind_param( 1, $col_data"); #col_data can contain anything
$sth->execute;
Oracle supports bind parameters with “:names” instead of ?, but it is not
portable. ODBC also uses ?s.
Another easy fix for cross database portability is $DBI::err and
$DBI::errstr. $DBI::err will get set if there is an error, $DBI::errstr
will contain the error. You don’t have to use $Mysql::db_errstr.
Hello All,
I just started to take a look at the new rt files, and have some
interesting issues that will need to be dealt with if we’re going to port to
Oracle/DB2/other enterprise DBs. Looking at the schema.mysql file, it looks
like we shouldn’t have a problem making a similar file for the other DBs.
MY question is, how abstract is the RT DBI code itself? I was looking at
the files in lib/rt/database, and while it’s close to being able to work
with these DBs, there is one real important thing missing off the bat.
Schema.
MySQL doesn’t require the schema to be in there (As a matter of
fact, I think it all falles under a ‘mysql.’ schema) but the larger DBs do.
Is that something that there is a planned workaround for, or will we need to
re-write the lib files as well?
The only other thing I can see, and I don’t know how Oracle handles
this, is that DB2 will puke if you attempt to call a CHAR or VARCHAR column
without enclosing the values in 's. Likewise, INT and similar numeric
columns will puke if you DO enclose them with anything. If I recall
correctly, MySQL has no such restraints.
I’ll keep searching through it and see what else I can see.
Cheers,
Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation
I had completely forgotten about quote and bind_param… This has not been a
good week for brainpower.
The Schema thing was my biggest concern however. I’m not sure of a good way
to address that one.
The only thing I can think of offhand is to address the MySQL calls with the
schema ‘mysql.’ prepended, which would make it ugly, but could work.
Any suggestions out there for a way to approach that?
Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation-----Original Message-----
From: Aryeh “Cody” Sherr [mailto:asherr@cs.unm.edu]
Sent: Wednesday, April 12, 2000 5:02 PM
To: Dylan Vanderhoof
Cc: ‘rt-devel@lists.fsck.com’
Subject: Re: [rt-devel] let DBI handle it
DBI has something that directly addresses this quoting problem:
$dbh->quote($string) and
$dbh->quote($string, $data_type)
These will use the DBD to quote the data as appropriate for the database
and the data type. This works for Oracle. It’s almost a must for quoting
between different databases: some need ’ and \ quoted, %, etc.
Another really great solution that also markably improves efficiency is to
use bind parameters. You don’t need to quote things at all:
my $sql = “SELECT foo FROM table WHERE col=?”; #? is a placeholder
my $sth = $dbh->prepare($sql);
$sth->bind_param( 1, $col_data"); #col_data can contain anything
$sth->execute;
Oracle supports bind parameters with “:names” instead of ?, but it is not
portable. ODBC also uses ?s.
Another easy fix for cross database portability is $DBI::err and
$DBI::errstr. $DBI::err will get set if there is an error, $DBI::errstr
will contain the error. You don’t have to use $Mysql::db_errstr.
Hello All,
I just started to take a look at the new rt files, and have some
interesting issues that will need to be dealt with if we’re going to port to
Oracle/DB2/other enterprise DBs. Looking at the schema.mysql file, it looks
like we shouldn’t have a problem making a similar file for the other DBs.
MY question is, how abstract is the RT DBI code itself? I was looking at
the files in lib/rt/database, and while it’s close to being able to work
with these DBs, there is one real important thing missing off the bat.
Schema.
Whoa! I think you’re looking at RT 1.0’s code. are you sure you’ve got the rightcode? For 2.0, everything lives under lib/RT. (You’ll want to look at the DBIx
module in CVS as well, as that’s hwere all the abstraction is.)
MySQL doesn’t require the schema to be in there (As a matter of
fact, I think it all falles under a ‘mysql.’ schema) but the larger DBs do.
Is that something that there is a planned workaround for, or will we need to
re-write the lib files as well?
The only other thing I can see, and I don’t know how Oracle handles
this, is that DB2 will puke if you attempt to call a CHAR or VARCHAR column
without enclosing the values in 's. Likewise, INT and similar numeric
columns will puke if you DO enclose them with anything. If I recall
correctly, MySQL has no such restraints.
I’ll keep searching through it and see what else I can see.
Cheers,
Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation
jesse reed vincent – jrvincent@wesleyan.edu – jesse@fsck.com
pgp keyprint: 50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91
There are no supercomputer applications that are solvable that cannot be solved
in finite time using a fucking TRS-80 with approprite disk/tape drives. Zero.
-Tanj
Another really great solution that also markably improves efficiency is to
use bind parameters. You don’t need to quote things at all:
Well, you’ll want to look at the DBIx::EasySearch and DBIx::Record and DBIx::Handle as RT uses them for all it’s DB interaction. they’re cvs module DBIx
in fsck.com’s CVS. The way I’m dynamically generating queries in EasySearch may make this hard. though it should be easy to do in Record.
jesse
my $sql = “SELECT foo FROM table WHERE col=?”; #? is a placeholder
my $sth = $dbh->prepare($sql);
$sth->bind_param( 1, $col_data"); #col_data can contain anything
$sth->execute;
Oracle supports bind parameters with “:names” instead of ?, but it is not
portable. ODBC also uses ?s.
Another easy fix for cross database portability is $DBI::err and
$DBI::errstr. $DBI::err will get set if there is an error, $DBI::errstr
will contain the error. You don’t have to use $Mysql::db_errstr.
just my .02 upon looking at some of the code too.
$::cody
Hello All,
I just started to take a look at the new rt files, and have some
interesting issues that will need to be dealt with if we’re going to port to
Oracle/DB2/other enterprise DBs. Looking at the schema.mysql file, it looks
like we shouldn’t have a problem making a similar file for the other DBs.
MY question is, how abstract is the RT DBI code itself? I was looking at
the files in lib/rt/database, and while it’s close to being able to work
with these DBs, there is one real important thing missing off the bat.
Schema.
MySQL doesn’t require the schema to be in there (As a matter of
fact, I think it all falles under a ‘mysql.’ schema) but the larger DBs do.
Is that something that there is a planned workaround for, or will we need to
re-write the lib files as well?
The only other thing I can see, and I don’t know how Oracle handles
this, is that DB2 will puke if you attempt to call a CHAR or VARCHAR column
without enclosing the values in 's. Likewise, INT and similar numeric
columns will puke if you DO enclose them with anything. If I recall
correctly, MySQL has no such restraints.
I’ll keep searching through it and see what else I can see.
Cheers,
Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation
jesse reed vincent – jrvincent@wesleyan.edu – jesse@fsck.com
pgp keyprint: 50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91
There are no supercomputer applications that are solvable that cannot be solved
in finite time using a fucking TRS-80 with approprite disk/tape drives. Zero.
-Tanj
MY question is, how abstract is the RT DBI code itself? I was looking at
the files in lib/rt/database,
The lib/rt/database stuff is to be removed in 2.0, the new code is in
Jesses DBIx modules.
and while it’s close to being able to work
with these DBs, there is one real important thing missing off the bat.
Schema.
MySQL sets the default “database” (which, AFAIK, is similar to schema)
during the login. I don’t think it will break MySQL if the sql sentences
are modified to use dbname.tablename instead of tablename.
MySQL doesn’t require the schema to be in there (As a matter of
fact, I think it all falles under a ‘mysql.’ schema)
The mysql database/schema is only for database metadata (tables,
permissions, etc)
I think the most complete library for “easy” database manipulation is
DBIx::Recordset. It’s working for any known DBMS AFAIK. It really scares
me, because the code is huuuge. I think it really needs to be done a lot
of work to EasySearch if it’s to be nearly as good as Recordset. Still, I
clearly see that it’s room for EasySearch at CPAN.