Home > Archive > PERL Miscellaneous > March 2004 > INSERT statement works by itself but not in the script??
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
INSERT statement works by itself but not in the script??
|
|
| Bing Du 2004-03-30, 12:39 pm |
| Greetings,
This is perl, v5.8.0 built for i386-linux-thread-multi.
The result of the following INSERT is '0E0' (value of $rv) if run in a
Perl script that uses DBI and DBD::mysql (version 2.9003). The table
$ltm_ssn_table is still empty after INSERT.
=============
$rv = $mysql_dbh->do(qq{INSERT INTO $ltm_ssn_table (ltm_number,ssn,notes)
SELECT
$source_data_table.crse,$source_data_table.ssn,$old_ltm_ssn_table.notes
FROM $source_data_table LEFT JOIN $old_ltm_ssn_table
ON $source_data_table.crse=$old_ltm_ssn_table.ltm_number
AND $source_data_table.ssn=$old_ltm_ssn_table.ssn
WHERE $source_data_table.offer_dept_abrvn='$ltm_string'
});
============
However, the above INSERT works fine with the MySQL command line client:
========
mysql> INSERT INTO ltm_ssn (ltm_number,ssn,notes) SELECT
source_data.crse, source_data.ssn, old_ltm_ssn.notes FROM source_data
LEFT JOIN old_ltm_ssn ON source_data.crse=old_ltm_ssn.ltm_number AND
source_data.ssn=old_ltm_ssn.ssn WHERE source_data.offer_dept_abrvn='L TM;
Query OK, 485 rows affected (0.03 sec)
Records: 485 Duplicates: 0 Warnings: 0
==========
Would anybody tell me what's wrong with the INSERT within the script?
Appreciate any help.
Bing
| |
| Paul Lalli 2004-03-30, 12:39 pm |
| On Tue, 30 Mar 2004, Bing Du wrote:
> Greetings,
>
> This is perl, v5.8.0 built for i386-linux-thread-multi.
>
> The result of the following INSERT is '0E0' (value of $rv) if run in a
> Perl script that uses DBI and DBD::mysql (version 2.9003). The table
> $ltm_ssn_table is still empty after INSERT.
>
> =============
> $rv = $mysql_dbh->do(qq{INSERT INTO $ltm_ssn_table (ltm_number,ssn,notes)
> SELECT
> $source_data_table.crse,$source_data_table.ssn,$old_ltm_ssn_table.notes
> FROM $source_data_table LEFT JOIN $old_ltm_ssn_table
> ON $source_data_table.crse=$old_ltm_ssn_table.ltm_number
> AND $source_data_table.ssn=$old_ltm_ssn_table.ssn
> WHERE $source_data_table.offer_dept_abrvn='$ltm_string'
> });
> ============
>
> However, the above INSERT works fine with the MySQL command line client:
>
> ========
> mysql> INSERT INTO ltm_ssn (ltm_number,ssn,notes) SELECT
> source_data.crse, source_data.ssn, old_ltm_ssn.notes FROM source_data
> LEFT JOIN old_ltm_ssn ON source_data.crse=old_ltm_ssn.ltm_number AND
> source_data.ssn=old_ltm_ssn.ssn WHERE source_data.offer_dept_abrvn='L TM;
> Query OK, 485 rows affected (0.03 sec)
> Records: 485 Duplicates: 0 Warnings: 0
> ==========
>
> Would anybody tell me what's wrong with the INSERT within the script?
>
Have you asked the Database what's wrong first? Check the values of
$dbh->errstr
and
$dbh->{'mysql_error'}
to see if they contain anything useful.
Paul Lalli
| |
| Brian McCauley 2004-03-30, 12:39 pm |
| Paul Lalli <ittyspam@yahoo.com> writes:
> On Tue, 30 Mar 2004, Bing Du wrote:
[color=darkred]
> Have you asked the Database what's wrong first? Check the values of
> $dbh->errstr
> and
> $dbh->{'mysql_error'}
> to see if they contain anything useful.
Unlikely, the command didn't fail - it just inserted zero rows.
--
\\ ( )
. _\\__[oo
.__/ \\ /\@
. l___\\
# ll l\\
###LL LL\\
| |
| Brian McCauley 2004-03-30, 12:39 pm |
| Bing Du <bdu@iastate.edu> writes:
> The result of the following INSERT is '0E0' (value of $rv) if run in a
> Perl script that uses DBI and DBD::mysql (version 2.9003). The table
> $ltm_ssn_table is still empty after INSERT.
>
> =============
> $rv = $mysql_dbh->do(qq{INSERT INTO $ltm_ssn_table (ltm_number,ssn,notes)
> SELECT
> $source_data_table.crse,$source_data_table.ssn,$old_ltm_ssn_table.notes
> FROM $source_data_table LEFT JOIN $old_ltm_ssn_table
> ON $source_data_table.crse=$old_ltm_ssn_table.ltm_number
> AND $source_data_table.ssn=$old_ltm_ssn_table.ssn
> WHERE $source_data_table.offer_dept_abrvn='$ltm_string'
> });
> ============
>
> However, the above INSERT works fine with the MySQL command line client:
>
> ========
> mysql> INSERT INTO ltm_ssn (ltm_number,ssn,notes) SELECT
> source_data.crse, source_data.ssn, old_ltm_ssn.notes FROM source_data
> LEFT JOIN old_ltm_ssn ON source_data.crse=old_ltm_ssn.ltm_number AND
> source_data.ssn=old_ltm_ssn.ssn WHERE source_data.offer_dept_abrvn='L
> TM;
> Query OK, 485 rows affected (0.03 sec)
> Records: 485 Duplicates: 0 Warnings: 0
> ==========
Have you tried putting the SQL command into a variable and printing it out
before you do() it just to be sure that it really is the same as the
command you tried in the command line client?
--
\\ ( )
. _\\__[oo
.__/ \\ /\@
. l___\\
# ll l\\
###LL LL\\
| |
| Bing Du 2004-03-30, 1:36 pm |
| Yes, I have. $mysql_dbh->errstr returned nothing. It finally turned
out something outside of the INSERT statement was not set right. Thanks
for the heads-up.
Bing
Paul Lalli wrote:
> On Tue, 30 Mar 2004, Bing Du wrote:
>
>
>
>
> Have you asked the Database what's wrong first? Check the values of
> $dbh->errstr
> and
> $dbh->{'mysql_error'}
> to see if they contain anything useful.
>
> Paul Lalli
| |
|
| "Bing Du" <bdu@iastate.edu> wrote in message
news:c4c6sl$1bu$1@news.iastate.edu...
> Greetings,
>
> This is perl, v5.8.0 built for i386-linux-thread-multi.
>
> The result of the following INSERT is '0E0' (value of $rv) if run in a
> Perl script that uses DBI and DBD::mysql (version 2.9003). The table
> $ltm_ssn_table is still empty after INSERT.
>
> =============
> $rv = $mysql_dbh->do(qq{INSERT INTO $ltm_ssn_table (ltm_number,ssn,notes)
> SELECT
> $source_data_table.crse,$source_data_table.ssn,$old_ltm_ssn_table.notes
> FROM $source_data_table LEFT JOIN $old_ltm_ssn_table
> ON
$source_data_table.crse=$old_ltm_ssn_table.ltm_number
> AND $source_data_table.ssn=$old_ltm_ssn_table.ssn
> WHERE
$source_data_table. offer_dept_abrvn='$ltm_string'
> });
> ============
>
> However, the above INSERT works fine with the MySQL command line client:
>
> ========
> mysql> INSERT INTO ltm_ssn (ltm_number,ssn,notes) SELECT
> source_data.crse, source_data.ssn, old_ltm_ssn.notes FROM source_data
> LEFT JOIN old_ltm_ssn ON source_data.crse=old_ltm_ssn.ltm_number AND
> source_data.ssn=old_ltm_ssn.ssn WHERE source_data.offer_dept_abrvn='L TM;
> Query OK, 485 rows affected (0.03 sec)
> Records: 485 Duplicates: 0 Warnings: 0
> ==========
>
> Would anybody tell me what's wrong with the INSERT within the script?
>
have you checked what the sql actually is?
there are too many (unknown to us) variables in your insert statement,
for us to be able to guess what's wrong. maybe one of
$ltm_ssn_table ,$source_data_table, $source_data_table,$old_ltm_ssn_table,
or $ltm_string is not what you think
try:
my $sql=qq{INSERT INTO $ltm_ssn_table .....};
print "debug sql=[$sql]\n";
$rv = $mysql_dbh->do($sql);
print "debug rv=$rv\n";
now you can cut and paste the debug sql into your client to make sure
you are trying the same thing.
gnari
| |
| John J. Trammell 2004-03-30, 10:31 pm |
| On Tue, 30 Mar 2004 10:22:39 -0600, Bing Du <bdu@iastate.edu> wrote:
> mysql> INSERT INTO ltm_ssn (ltm_number,ssn,notes) SELECT
> source_data.crse, source_data.ssn, old_ltm_ssn.notes FROM source_data
> LEFT JOIN old_ltm_ssn ON source_data.crse=old_ltm_ssn.ltm_number AND
> source_data.ssn=old_ltm_ssn.ssn WHERE source_data.offer_dept_abrvn='L TM;
> Query OK, 485 rows affected (0.03 sec)
> Records: 485 Duplicates: 0 Warnings: 0
That doesn't look like valid SQL to me.
Perhaps your quoting is off?
|
|
|
|
|