Perl Guru Forums: Perl Programming Help: DBI: Inserting a NULL value

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

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

big_v
New User

Aug 1, 2002, 9:04 AM

Post #1 of 8 (6807 views)

Inserting a NULL value	Can't Post

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

Post #2 of 8 (6801 views)

Re: [big_v] Inserting a NULL value [In reply to]	Can't Post

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

Post #3 of 8 (6797 views)

Re: [big_v] Inserting a NULL value [In reply to]	Can't Post

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

Post #4 of 8 (6787 views)

Re: [big_v] Inserting a NULL value [In reply to]	Can't Post

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

Post #5 of 8 (6777 views)

Re: [thebitch] Inserting a NULL value [In reply to]	Can't Post

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

Post #6 of 8 (6750 views)

Re: [RedRum] Inserting a NULL value [In reply to]	Can't Post

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

Post #7 of 8 (6748 views)

Re: [thebitch] Inserting a NULL value [In reply to]	Can't Post

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

Post #8 of 8 (6745 views)

Re: [RedRum] Inserting a NULL value [In reply to]	Can't Post

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} )"

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives