CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in 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 (8286 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 (8280 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 (8276 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 (8266 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 (8256 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 (8229 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 (8227 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 (8224 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) Powered by Gossamer Forum v.1.2.0

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