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

I & L Fogg <il.fogg@bigpond.net.au> wrote:
> 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

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB

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


ctcgag@hotmail.com wrote:
> I & L Fogg <il.fogg@bigpond.net.au> wrote:
> 
>>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
> 

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

I & L Fogg wrote:
> 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
> 
> 
> ctcgag@hotmail.com wrote:
> 
>> I & L Fogg <il.fogg@bigpond.net.au> wrote:
>>
>>> 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 <il.fogg@bigpond.nospam.net.au> wrote:
> 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

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB

I & L Fogg wrote:
> Sorry, forgot to say that @cols does NOT contain the "Unused" column 
> that the error message refers to.
> 
> I & L Fogg wrote:
> 
>> 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";

>> my $sel_sth = $src_dbh->prepare( $select );
>> $sel_sth->execute();

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

ctcgag@hotmail.com wrote:
> I & L Fogg <il.fogg@bigpond.nospam.net.au> wrote:
> 
>>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
>