Home: Perl Programming Help: DBI:
Inserting a NULL value



big_v
New User

Aug 1, 2002, 9:04 AM


Views: 17203
Inserting a NULL value

Hi

I am trying to insert a NULL value into a table. Details below.....

Code
 
my $stha = $dbh->prepare("select * from mytable");
$stha->execute;

while ($arrayref = $stha->fetchrow_arrayref)
{ $sthb = $dbh->prepare("INSERT INTO $arrayref->[30] VALUES ('$arrayref->[0]','$arrayref->[1]','$arrayref->[2]',$arrayref->[3],'$arrayref->[4]','$arrayref->[5]','$arrayref->[6]','$arrayref->[7]','$arrayref->[8]','$arrayref->[9]','$arrayref->[10]','$arrayref->[11]',$arrayref->[12],$arrayref->[13],$arrayref->[14],$arrayref->[15],$arrayref->[16],$arrayref->[17],$arrayref->[18],$arrayref->[19],$arrayref->[20],$arrayref->[21],'$arrayref->[22]','$arrayref->[23]','$arrayref->[24]','$arrayref->[25]',\"\",\"\",'$arrayref->[28]',$arrayref->[29],'$arrayref->[30]','$arrayref->[31]','$arrayref->[32]','$arrayref->[33]','$arrayref->[34]','$arrayref->[35]','$arrayref->[36]','$arrayref->[37]','$arrayref->[38]'");
}
$sthb->execute or die "can't execute the insert: $sthb->errstr\n";


As you can see, there are a large number of fields returned from the fetchrow_arrayref.

From a previous mysqldump, $arrayref->[26] and $arrayref->[27] are both NULL in value. However, after the fetchrow_arrayref, arrayref->[26] and $arrayref->[27] return '' (an empty string).

If I try to insert this value a new table, I get an error message such as ......

You have an error in your SQL syntax near '' at line 1 at ./sort_mysql line 52. can't execute the insert: DBI::st=HASH(0x820b208)
Line 52 being the $sthb->execute

Any ideas

Thanks in advance


Paul
Enthusiast

Aug 2, 2002, 3:04 AM


Views: 17197
Re: [big_v] Inserting a NULL value

Hmm how about doing a dump of your db which will show NULL values properly then just pass the CREATE TABLE queries through your perl script.


(This post was edited by RedRum on Aug 2, 2002, 3:05 AM)


Jean
User


Aug 4, 2002, 1:09 AM


Views: 17193
Re: [big_v] Inserting a NULL value

IMO you can replace undefined values with 'NULL's in the received results


Jean Spector
SQA Engineer @ Exanet
jean.spector@softhome.net


There are only 10 types of people in the world -
Those who understand binary, and those who don't.


thebitch
User

Aug 9, 2002, 7:55 AM


Views: 17183
Re: [big_v] Inserting a NULL value

wrong approach there buddy, this is perl, and that is sin.

Code
use strict; # or die 
my $sth = $dbh->prepare("select * from mytable");
$sth->execute();
while(my $HASH_HASH_HASH_REF = $sth->fetchrow_hashref() ) {
my $sql = "INSERT INTO ";
$SQL .= $HASH_HASH_HASH_REF->{tablename};
delete $HASH_HASH_HASH_REF->{tablename};
$SQL .=" VALUES ( ";
my @KEYS = sort keys @$HASH_HASH_HASH_REF;
$SQL .= join ", ", @KEYS;
$SQL .= " ) VALUES ( ";
$SQL .= join '', map { '?, ' } @KEYS;
$SQL .=' ) ';

my $sth2 = $dbh->prepare($sql);
$sth2->execute(@{$HASH_HASH_HASH_REF}{@KEYS}) or die "oh crap!!! $@ $! ".$sth2->errstr;
}


You can't call methods within strings, not
by simply " $foo->METHOD", doesn't work that way.
I wouldn't be suprised that you get SQL errors, you need
to learn and use PLACEHOLDERS.

You might wanna check out CPAN
and especially
SQL::Generator
otherwise don't bother.

If you want to know what placeholders are
check out the DBI documentation, perldoc.com got all the
perl documentation you could imagine ;)


Paul
Enthusiast

Aug 10, 2002, 12:01 PM


Views: 17173
Re: [thebitch] Inserting a NULL value


Quote
You can't call methods within strings



Code
my $IN = new CGI; 

print "I wonder what our url is? .. @{ [ $IN->url ] }";



(This post was edited by RedRum on Aug 10, 2002, 12:02 PM)


thebitch
User

Aug 26, 2002, 10:42 PM


Views: 17146
Re: [RedRum] Inserting a NULL value


In Reply To

Quote
You can't call methods within strings



Code
my $IN = new CGI; 

print "I wonder what our url is? .. @{ [ $IN->url ] }";



Quote
You can't call methods within strings, not
by simply " $foo->METHOD", doesn't work that way.


Code
my $Q = new CGI; 
print "Can I get Scalar Context please? ${\$Q->url}";



Paul
Enthusiast

Aug 27, 2002, 3:02 AM


Views: 17144
Re: [thebitch] Inserting a NULL value

Your original answer was:

You can't call methods within strings

But like I showed, you can :)


thebitch
User

Aug 27, 2002, 5:03 AM


Views: 17141
Re: [RedRum] Inserting a NULL value

I don't edit my posts,
and that neat little trick is going away in perl 6.

it will simply be

print "$( qw{any old perl expression} )"
print "@( qw{any old perl expression} )"