DBI DBD::ODBC, Create a table, drop its constraints, then recreate it again!

blackadder has asked for the wisdom of the Perl Monks concerning the following question:

Bonsoir fantastic Monks,

I need to create a table in MSSQL2008, then drop all its constraints if there are any, then to recreate the same table again!..Simple!

So i put this bit of code together (with help from PerlMonks)

$dbh = DBI->connect("DBI:ODBC:driver={SQL Server};Server=$server;Datab
+ase=$database;UID=$dbusername;PWD=$dbpassword") ||  die "$Error_Messa
+ge $DBI::errstr";
print ".......Connection successful\n\n";

&prep_tbl("Sites");
&prep_tbl("Data_Centers");

sub prep_tbl
{
    my ($tbl) = @_;
    print "\nPreparing Mssql2008 table: $tbl,....";
    
    my $dbh_tmp = DBI->connect("DBI:ODBC:driver={SQL Server};Server=$s
+erver;Database=$database;UID=$dbusername;PWD=$dbpassword");
    
    # Check Database for tables, IF NOT EXISTS  then just create it
    $dbh->do("IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = objec
+t_id(N'[dbo].[$tbl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) 
                CREATE TABLE [dbo].[$tbl] (@{$tables->{$tbl}})");

    my $sth = $dbh->prepare("SELECT constraint_name FROM INFORMATION_S
+CHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = '$tbl'");
    $sth->execute;
    my $temp =  $sth->fetchrow_arrayref;
    foreach my $rel (@$temp)
    {            
        next unless ($rel =~ /ID/);
        $dbh_tmp->do("ALTER TABLE $tbl DROP CONSTRAINT $rel");
    }
    
    #### The code below doesn't work - it doesn't recreate the table a
+gain!
    
    $dbh->do("IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id
+(N'[dbo].[$tbl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) 
                    DROP TABLE [dbo].[$tbl]; 
                    CREATE TABLE [dbo].[$tbl] (@{$tables->{$tbl}})");
    print "Done\n";
}
[download]

But when I ran the code I got the following errors!


DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server Driver]Connection
+ is busy with results for another hstmt (SQL-HY000) at U:\Do
ccuments\Scripts\Test\test.pl line 60.
Preparing Mssql2008 table: Sites,....Done

DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server Driver]Connection
+ is busy with results for another hstmt (SQL-HY000) at U:\Do
ccuments\Scripts\Test\test.pl line 60.
Preparing Mssql2008 table: Data_Centers,....Done
[download]

So I said ok, it deletes the relationships but it doesn't recreate the table again! let me add another handle here!

    my $dbh3 = DBI->connect("DBI:ODBC:driver={SQL Server};Server=$serv
+er;Database=$database;UID=$dbusername;PWD=$dbpassword");
    
    $dbh3->do("IF EXISTS (SELECT * FROM sysobjects WHERE id = object_i
+d(N'[dbo].[$tbl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) 
                    DROP TABLE [dbo].[$tbl]; 
                    CREATE TABLE [dbo].[$tbl] (@{$tables->{$tbl}})");
[download]

Still doesn't work, I don't get any errors but the table does not get re-created!

Can someone enlighten me please why this is happening?

Merci

Blackadder

Comment on DBI DBD::ODBC, Create a table, drop its constraints, then recreate it again! Select or Download Code

Replies are listed 'Best First'.
Re: DBI DBD::ODBC, Create a table, drop its constraints, then recreate it again! by runrig (Abbot) on Nov 11, 2009 at 16:16 UTC
Here it appears you are only fetching one constraint, and so your sth is still "busy" when there are any constraints (you need to fetch once for each row, and then one more time to automatically "finish" the cursor so it's no longer "busy"): `my $sth = $dbh->prepare("SELECT constraint_name FROM INFORMATION_S +CHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = '$tbl'"); $sth->execute; my $temp = $sth->fetchrow_arrayref; foreach my $rel (@$temp) { next unless ($rel =~ /ID/); $dbh_tmp->do("ALTER TABLE $tbl DROP CONSTRAINT $rel"); }` [download] Since you are only fetching one column, you can replace the prepare/execute/fetch with just (and since all the rows will be fetched, the statement won't stay "busy"): `my $temp = $dbh->selectcol_arrayref($your_sql_statement);` [download]	[reply] [d/l] [select]
Re^2: DBI DBD::ODBC, Create a table, drop its constraints, then recreate it again! by blackadder (Hermit) on Nov 25, 2009 at 14:51 UTC
Hi, Thanks for the reply,it worked..	[reply]
Re: DBI DBD::ODBC, Create a table, drop its constraints, then recreate it again! by mje (Curate) on Nov 12, 2009 at 09:10 UTC
What your code requires is Multiple Active Statement support (or as MS calls it MARS) as you have a cursor open (and outstanding rows unread) whilst trying to execute another statement (the alter). You have 2 choices. 1) as runrig suggests or 2) enable multiple active statement support (assuming your sql server driver and database support it. You can do the latter by adding MARS_Connection=yes to your connect string. For this simple case I believe you are better reorganizing your code and avoiding MARS as a) there are a few gotchas with MARS support and b) your script will be compatible with more ms sql servers. UPDATE: There is a 3rd choice. Open 2 connections to the database.	[reply]