[rt-devel] let DBI handle it

[Aryeh "Cody" Sherr]

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.

just my .02 upon looking at some of the code too.

$::cody


On Wed, 12 Apr 2000, Dylan Vanderhoof wrote:

>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
>
>
>_______________________________________________
>Rt-devel mailing list
>Rt-devel at lists.fsck.com
>http://lists.fsck.com/mailman/listinfo/rt-devel
>