ok,
here's my code and then my error. I tried the same code with DBD::mysql and
it worked fine, so whats my problem and what my work around. I've commented
out my mysql stuff for now, only worry about the ODBC stuff..
print "start\n\n";
use strict;
use DBI;
#my $username;
#my $host = 'localhost';
#my $dbName = 'mysql';
#my $driver = 'mysql';
#my $data_source = "dbi:$driver:dbname=$dbName;host=$host";
my $odbc = "dbi:ODBC:P4A";
my $auth;
my %attr;
my $rc;
#my $dbh = DBI->connect($data_source, $username, $auth, \%attr);
my $dbh = DBI->connect('dbi:ODBC:Dev', 'DEV1', 'data');
my $table_number = 1;
my $tabsth = $dbh->table_info();
while ( my ( $qual, $owner, $table, $type ) = $tabsth->fetchrow_array() ) {
#print "name:$table\n";
my $sql = "select * from $table";
print "\n\nTable Information for '$table'\t:$table_number:\n";
print "-------------------\n";
$table_number++;
print "statement:$sql\n";
### prepare statement
my $sth = $dbh->prepare( $sql );
$sth->execute();
my $fields = $sth->{'NUM_OF_FIELDS'};
print "NUM_OF_FIELDS:$fields\n";
print "Column Name Type Precision
Scale Null\n";
print "------------------------------ --------- -------------
-------- -----\n";
for ( my $i = 0; $i < $fields; $i++ ) {
my $col_name = $sth->{'NAME'}->[$i];
my $col_type = $sth->{'TYPE'}->[$i];
$col_type = &_format_numeric_type_code( $col_type );
my $col_prec = $sth->{'PRECISION'}->[$i];
my $col_scale = $sth->{'SCALE'}->[$i];
#my $nullable = ("Yes", "No", "Unk") [
$sth->{'NULLABLE'}->[$i] ];
my $nullable = $sth->{'NULLABLE'}->[$i];
printf "%-30s %9s %5d %4d %s\n",
$col_name, $col_type, $col_prec, $col_scale, $nullable;
#print "$col_name\t\t$col_type\t$col_prec\t$col_scale\n";
}
}
$tabsth->finish();
my $rc = $dbh->disconnect or warn $dbh->errstr;
print "end\n\n";
sub _format_numeric_type_code {
my $code = shift;
my $value = 'default';
my %sql_type = (
1 => 'CHAR',
2 => 'NUMERIC',
3 => 'DECIMAL',
4 => 'INTEGER',
5 => 'SMALLINT',
6 => 'FLOAT',
7 => 'REAL',
8 => 'DOUBLE',
9 => 'DATE',
10 => 'TIME',
11 => 'TIMESTAMP',
12 => 'VARCHAR',
-1 => 'LVARCHAR',
-2 => 'BINARY',
-3 => 'VARBINARY',
-4 => 'LVARBINARY',
-5 => 'BIGINT',
-6 => 'TINYINT',
-7 => 'BIT',
-8 => 'WCHAR',
-9 => 'WVARCHAR',
-10 => 'WLVARCHAR',
);
$value = $sql_type{$code};
#print "value:$value\n";
#sleep(1);
return $value;
}
no error for mysql, but error with ODBC:
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver] Connection
is busy with results for another hstmt (SQL-HY000)(DBD:
st_execute/SQLExecute err=1) at meta_data3.pl line 35.
Describe failed during DBI::st=HASH(0x1c175f0)->FETCH(NUM_OF_FILEDS) at
meta_data3.pl line 37.
Peter Gibbons
BBCi at http://www.bbc.co.uk/
This e-mail (and any attachments) is confidential and may contain
personal views which are not the views of the BBC unless specifically
stated.
If you have received it in error, please delete it from your system, do
not use, copy or disclose the information in any way nor act in
reliance on it and notify the sender immediately. Please note that the
BBC monitors e-mails sent or received. Further communication will
signify your consent to this.
