Need help with Insert

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

Dear monks
I am confuse with these part of the program, I am trying to read a DBF with SELECT then all that put it in a FETCHROW_ARRAY() and from there to do INSERT INTO a MySQL table. But is giving my an error message like this Use of uninitialized value in concatenation (.) or string at pruebatest1.pl line 47. This is the code I hope you can help me to fix these part thank you ! these is the part of the code:

my $dbhX1 = DBI->connect('dbi:XBase(RaiseError=1):');

my $select1 = $dbhX1->prepare("SELECT * FROM r501");
$select1->execute();
print "\nEjecutar SELECT de r501\n";

    my $mysql_dbh1 = DBI->connect("DBI:mysql:database=$datafilename;ho
+st=localhost",
                         "root", "xyz123",
                        {'RaiseError' => 1});
while  ( (my @row) = $select1 -> fetchrow_array() ){


    my $sthsql = $mysql_dbh1->prepare ("INSERT INTO r501 (reg, pat, pe
+d, adnasec, impexp, cveped, adnaent, crupimp, rfcimp, curpaa, tc, fle
+te, seguro, embal, oincrem, odeduc, pesob, transal, tranarr, travent,
+ destino, nomimp, calleimp, numimp, numext, cp, mcpio, entfed, paisim
+p, archivo)
                                       VALUES ($row[0], $row[1], $row[
+2], $row[3], $row[4], $row[5], $row[6], $row[7], $row[8], $row[9], $r
+ow[10], $row[11], $row[12], $row[13], $row[14], $row[15], $row[16], $
+row[17], $row[18], $row[19], $row[20], $row[21], $row[22], $row[23], 
+$row[24], $row[25], $row[26], $row[27], $row[28], $row[29], $row[30]"
+)
}
[download]

Comment on Need help with Insert Select or Download Code

Replies are listed 'Best First'.
Re: Need help with Insert by thezip (Vicar) on Mar 13, 2008 at 23:33 UTC
You haven't quoted (via single quotes) any of the values in your insert statement. This is required for all of the values that are strings, dates, etc. But this begs the question, "Why aren't you using placeholders in the first place?" Your wish is my commandline.	[reply]
Re: Need help with Insert by ikegami (Patriarch) on Mar 13, 2008 at 23:33 UTC
That warning means that one of your `$row[x]` is `undef` and that it got treated as a zero-length string. Using placeholders — see "Placeholders and Bind Values" in the DBI docs — will avoid this problem and many others. You shouldn't be interpolating variables into an SQL query when it can be avoided (as it can here).	[reply] [d/l] [select]
Re: Need help with Insert by driver8 (Scribe) on Mar 14, 2008 at 01:54 UTC
If your @row has 30 elements, there will be no `$row[30]`. The last one will be `$row[29]`. Maybe you would rather do: `my $sthsql = $mysql_dbh1->prepare("INSERT INTO r501 (reg, pat, ped, ad +nasec, impexp, cveped, adnaent, crupimp, rfcimp, curpaa, tc, flete, s +eguro, embal, oincrem, odeduc, pesob, transal, tranarr, travent, dest +ino, nomimp, calleimp, numimp, numext, cp, mcpio, entfed, paisimp, ar +chivo) VALUES (?)") $sthsql->execute( join(', ' , @row) );` [download] -driver8	[reply] [d/l] [select]
(Re^2: Need help with Insert) Joining bind values doesn't work by Narveson (Chaplain) on Mar 14, 2008 at 04:34 UTC
Did you test this? I had to insert the missing semicolon in order to test `$sthsql->execute( join(', ' , @row) );` [download] and then I got execute failed: There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement. What happens is that DBI spots the imbedded quotes in the joined string and cleverly escapes them. See also Can a DBI Placeholder accept multiple values?	[reply] [d/l]
Re: Joining bind values doesn't work by driver8 (Scribe) on Mar 14, 2008 at 11:35 UTC
No, I didn't test it. I don't have a lot of DBI experience, and I don't have a DB running at the moment. Thanks for pointing out the error and the right way to do it. -driver8	[reply]
(Re: Need help with Insert) How to insert a long row by Narveson (Chaplain) on Mar 14, 2008 at 04:41 UTC
`my $posicionadores = join q{, }, ('?') x @row; my $sentencia = <<"FIN"; INSERT INTO r501 ( reg, pat, ped, adnasec, impexp, cveped , adnaent, crupimp, rfcimp, curpaa, tc, flete , seguro, embal, oincrem, odeduc, pesob, transal , tranarr, travent, destino, nomimp, calleimp, umimp , numext, cp, mcpio, entfed, paisimp, archivo ) VALUES ($posicionadores) FIN print "Vamos a decir:\n$sentencia\n"; my $sthsql = $mysql_dbh1->prepare($sentencia); $sthsql->execute( @row );` [download]	[reply] [d/l]