Non-unique columns via ODBC driver

[I & L Fogg]

I am trying to copy data from a proprietary ("semi"-relational) database
to MySQL. Fortunately, the db vendor provides an ODBC driver, so getting
the db schema and data is fairly straighforward using DBI and DBD::ODBC.

I am encountering one problem that someone may be able to help me with.

One of the tables in the db has several non-unique columns that are not
actually used (there are 10-15 columns named "Unused").

When I pull the table def using column_info, I can easily filter these
out before creating an appropriate table definition in MySQL.

The problem is that when I try to extract the data, the DBD::ODBC driver
barfs complaining about the non-unique columns (Unused). This happens
even when I specifically exclude Unused from the SELECT clause. IWO, I
use SELECT col_a, col_b FROM table; - not SELECT * FROM table;.

Can anyone suggest a workaround to this problem? I have suggested to my
client that we might need to modify the DB definition to fix the column
names for these Unused columns, but it would be nice if I could load the
data from the other columns without needing to do this.

Cheers, Iain

 

[ctcgag]

I am trying to copy data from a proprietary ("semi"-relational) database
to MySQL. Fortunately, the db vendor provides an ODBC driver, so getting
the db schema and data is fairly straighforward using DBI and DBD::ODBC.

I am encountering one problem that someone may be able to help me with.

One of the tables in the db has several non-unique columns that are not
actually used (there are 10-15 columns named "Unused").

When I pull the table def using column_info, I can easily filter these
out before creating an appropriate table definition in MySQL.

The problem is that when I try to extract the data, the DBD::ODBC driver
barfs complaining about the non-unique columns (Unused). This happens
even when I specifically exclude Unused from the SELECT clause. IWO, I
use SELECT col_a, col_b FROM table; - not SELECT * FROM table;.

What is the exact error that you get?

Xho

 

[I & L Fogg]

The error message is:

DBD::ODBC::db prepare failed: [Simba][Simba ODBC Driver]Non unique
column reference: Unused. (SQL-HY000)(DBD: st_prepare/SQLPrepare err=-1)
at ./dbcopy.pl line 346.

The code fragment that generates the message is:

my $select = q/SELECT / .
join(',', @cols) .
q/ FROM / .
$src_dbh->quote_identifier($table);
my $sel_sth = $src_dbh->prepare( $select );
$sel_sth->execute();

Cheers, Iain

 

[I & L Fogg]

Sorry, forgot to say that @cols does NOT contain the "Unused" column
that the error message refers to.

The error message is:

DBD::ODBC::db prepare failed: [Simba][Simba ODBC Driver]Non unique
column reference: Unused. (SQL-HY000)(DBD: st_prepare/SQLPrepare err=-1)
at ./dbcopy.pl line 346.

The code fragment that generates the message is:

my $select = q/SELECT / .
join(',', @cols) .
q/ FROM / .
$src_dbh->quote_identifier($table);
my $sel_sth = $src_dbh->prepare( $select );
$sel_sth->execute();

Cheers, Iain

What is the exact error that you get?

Xho

 

[ctcgag]

The error message is:

DBD::ODBC::db prepare failed: [Simba][Simba ODBC Driver]Non unique
column reference: Unused. (SQL-HY000)(DBD: st_prepare/SQLPrepare err=-1)
at ./dbcopy.pl line 346.

It looks like this error originates with the ODBC driver itself, and Perl
merely passes it along. If that is the case, it is unlikely that there
is much you can about from within Perl.

Xho

 

[J. Gleixner]

Sorry, forgot to say that @cols does NOT contain the "Unused" column
that the error message refers to.

The error message is:

DBD::ODBC::db prepare failed: [Simba][Simba ODBC Driver]Non unique
column reference: Unused. (SQL-HY000)(DBD: st_prepare/SQLPrepare
err=-1) at ./dbcopy.pl line 346.

The code fragment that generates the message is:

my $select = q/SELECT / .
join(',', @cols) .
q/ FROM / .
$src_dbh->quote_identifier($table);

It might help to:

print $select, "\n";

 

[I & L Fogg]

Yes, that's what I thought too. I traced the call down through the DBI
and DBD modules which confirmed it.

If there's nothing I can do at the Perl level, is there anything I can
do with ODBC (alternative function calls, different flags, etc).

Cheers, Iain

The error message is:

DBD::ODBC::db prepare failed: [Simba][Simba ODBC Driver]Non unique
column reference: Unused. (SQL-HY000)(DBD: st_prepare/SQLPrepare err=-1)
at ./dbcopy.pl line 346.

It looks like this error originates with the ODBC driver itself, and Perl
merely passes it along. If that is the case, it is unlikely that there
is much you can about from within Perl.

Xho