create table fails with large declaration

squark · #1 (**permalink**) 09-24-05, 17:39

Greetings!

The following big command should be excuted:

Code:

CREATE TABLE inode( found_at DOUBLE, extent_offs DOUBLE, di_inostamp DOUBLE, di_fileset DOUBLE, di_otimetv_sec DOUBLE, di_number DOUBLE, di_gen DOUBLE, di_ixpxdlen DOUBLE, di_ixpxdaddr1 DOUBLE, di_ixpxdaddr2 DOUBLE, di_ixpxdaddress DOUBLE, di_size DOUBLE, di_nblocks DOUBLE, di_nlink DOUBLE, di_uid DOUBLE, di_gid DOUBLE, di_mode DOUBLE, di_atimetv_sec DOUBLE, di_ctimetv_sec DOUBLE, di_next_index DOUBLE, di_mtimetv_sec DOUBLE )

I could only get a new table, if I choped it to the first three fields or by typing it as string directly in the prepare command like this:

Code:

$sth = $dbh->prepare("CREATE TABLE inode( found_at DOUBLE, extent_offs DOUBLE, di_inostamp DOUBLE, di_fileset DOUBLE, di_otimetv_sec [...] )");
$sth->execute;   #this worked but is not what i need
$sth->finish;

But the hole point in using Perl here, is to be able to create various tables at runtime. To accomplish that, the string $tabdef needs to be autogenerated and should then be used as follows:

Code:

for ($j=0; $j<=3; $j++) {  #larger then 3 and it fails
    $tabdef = $tabdef." $column[$j] DOUBLE,";
}
$tabdef =~ s/,$//;
$tabdef =~ s/\.//g;   #no dots!
$tabdef = "CREATE TABLE inode(".$tabdef." )";
$dbh->do("DROP TABLE inode");
$sth = $dbh->prepare($tabdef) or die "Can't prepare $tabdef: $dbh->errstrn";
$sth->execute or die "Can't execute: $dbh->errstrn";
$sth->finish;

And this wont work. I have no idea why. The syntax is correct, bcoz when using it with only 3 fields its ok, otherwise I get the error message:

Code:

Can't execute: DBI::db=HASH(0x82bb848)->errstrn at ./tabulator.pl line 89.

Im new to dbi and quite new to perl. Please point me to my error

squark

senza_nome · #2 (**permalink**) 09-25-05, 08:25

...Where is @column defined?

...Note that $tabdef ends with "DOUBLE,)" which will likely offend your database.

squark · #3 (**permalink**) 09-26-05, 12:27

the filling of the @column field is a bit complex. the script searches for column definitions in a data file and pushes them into @column, but its equivalent to

@column=( 'found_at', 'extent_offs', 'di_inostamp', 'di_fileset', [...] );

the last comma is choped off by $tabdef =~ s/,$//; so thats not the problem.

btw. here are my version infos:
debian testing
perl v5.8.7
mysql-server 5.0.11beta
libdbi-pelr1.48-1

squark

senza_nome · #4 (**permalink**) 09-27-05, 18:14

... my bad, I'll have a look again.

felixg · #5 (**permalink**) 10-12-05, 07:17

Quote:

Originally Posted by senza_nome

Code:

$tabdef = "CREATE TABLE inode(".$tabdef." )";
$dbh->do("DROP TABLE inode");
$sth = $dbh->prepare($tabdef) or die "Can't prepare $tabdef: $dbh->errstrn";
$sth->execute or die "Can't execute: $dbh->errstrn";
$sth->finish;

Why don't you use

Code:

$dbh->do($tabdef) or die ...

You $sth->execute line should read:

Code:

$sth->execute or die "Can't execute: " . $dbh->errstr;

if you want to see the error message.

--
felix

true novice · #6 (**permalink**) 10-12-05, 10:31

Felixg:

Just curious as to how you managed to get squark's code attributed to senza_nome?

felixg · #7 (**permalink**) 10-12-05, 10:35

Quote:

Originally Posted by true novice

Felixg:
Just curious as to how you managed to get squark's code attributed to senza_nome?

Oops... apologies. My mistake.
I should not manually cut and paste and fool around with the tags.

--
felix

Internet Services

Web Development

Digital Marketing

Consumer Tech