| Subject: | Quoting field and table names when using reserved words |
Again some great work, I'm using this as part of CRUDDY and I managed
to pick a MySQL reserved word for my table, apparently lines is, who
knew!
I've got the following fix, which is a bit of a bodge, and I understand
Oracle uses a different quote type, so I guess this probably really
wants changing to a function in each of the mysql/oracle Lib/IO modules.
Anyway current changes, and hence bits that need quoting:
DBIx/IO.pm:
533c534
< my $sth = $self->make_cursor("SELECT $cols FROM $table $where")
|| return undef;
---
Show quoted text
> my $sth = $self->make_cursor("SELECT $cols FROM `$table` $where")
|| return undef;
568c569
< my $sql = "DELETE FROM $table WHERE $key_name = $id_val";
---
Show quoted text> my $sql = "DELETE FROM `$table` WHERE $key_name = $id_val";
600c601
< return $dbh->do("DELETE FROM $table $where");
---
Show quoted text> return $dbh->do("DELETE FROM `$table` $where");
DBIx/IO/Search.pm:
417c417,418
< $self->{sql} = "SELECT $cols FROM $self->{table_name} $self->
{table_name} $where" ;
---
Show quoted text> #Quote table name in case its a reserved word
> $self->{sql} = "SELECT $cols FROM `$self->{table_name}` `$self->
{table_name}` $where" ;
424c425
< $self->{sql} .= $self->{order_by} = ( ref($self->
{sortlist}) ? " ORDER BY " . join(',', map("$self->{table_name}.$_",@
{$self->{sortlist}})) : '');
---
Show quoted text> $self->{sql} .= $self->{order_by} = ( ref($self->
{sortlist}) ? " ORDER BY " . join(',', map("`$self->{table_name}
`.`$_`",@{$self->{sortlist}})) : '');
446c447
< return "$table_name.$column $op $start AND $end";
---
Show quoted text> return "`$table_name`.`$column` $op $start AND $end";
457c458
< return "$table_name.$column $op ($val_list)";
---
Show quoted text> return "`$table_name`.`$column` $op ($val_list)";
480c481
< $column = $self->{io}->lc_func("$table_name.$column");
---
Show quoted text> $column = $self->{io}->lc_func("`$table_name`.`$column`");
489c490
< return "$table_name.$column $op SYSDATE - $offset";
---
Show quoted text> return "`$table_name`.`$column` $op SYSDATE -
$offset";
494c495
< return "$table_name.$column $op SYSDATE - $offset";
---
Show quoted text> return "`$table_name`.`$column` $op SYSDATE -
$offset";
499c500
< return "$table_name.$column $op SYSDATE - $offset";
---
Show quoted text> return "`$table_name`.`$column` $op SYSDATE -
$offset";
504c505
< return "$table_name.$column $op SYSDATE - $offset";
---
Show quoted text> return "`$table_name`.`$column` $op SYSDATE -
$offset";
509c510
< return "$table_name.$column $op $val";
---
Show quoted text> return "`$table_name`.`$column` $op $val";
DBIx/IO/OracleIO.pm:
375c375
< my $sql = "INSERT INTO $table ($fields) VALUES ($values)";
---
Show quoted text> my $sql = "INSERT INTO `$table` ($fields) VALUES ($values)";
487c487
< my $sql = "UPDATE $hint $table SET $set_sql $where";
---
Show quoted text> my $sql = "UPDATE $hint `$table` SET $set_sql $where";
DBIx/IO/mysqlIO.pm:
259c259,260
< my $sth = $self->make_cursor("DESCRIBE $table");
---
Show quoted text> #Quote table name in case its a reserved word
> my $sth = $self->make_cursor("DESCRIBE `$table`");
402c403
< my $sql = "INSERT INTO $table ($fields) VALUES ($values)";
---
Show quoted text> my $sql = "INSERT INTO `$table` ($fields) VALUES ($values)";
460c461
< my $sql = "UPDATE $hint $table SET $set_sql $where";
---
Show quoted text> my $sql = "UPDATE $hint `$table` SET $set_sql $where";
Thanks,
PN