Martin,
many thanks for comments that would make sense given the error i am having..
can you modify my code to fix the problem, i am unsure how to redesign my
code, here..
many thanks
Pete
> -----Original Message-----
> From: Martin J. Evans [SMTP:[EMAIL PROTECTED]
> Sent: Thursday, August 14, 2003 2:22 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [dbi] DBD::ODBC
>
> Peter,
>
> As the error says "[Microsoft][ODBC SQL Server Driver] Connection is busy
> with
> results for another hstmt". You have two active statement handles, one for
> the
> list of tables and one doing the select on the current table. The MS SQL
> Server
> driver does not support multiple active statements although there are some
> ugly
> ways around it (that have other side affects) like setting a server-side
> cursor
> such as a dynamic cursor.
>
> See perldoc DBD::ODBC and search for odbc_SQL_ROWSET_SIZE but I'd redesign
> the
> code to use one statement at a time if I were you.
>
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> Development
>
> On 14-Aug-2003 Peter Gibbons-MDG wrote:
> > 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.
>
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.
