|
ok has asked for the wisdom of the Perl Monks concerning the following question:
This is a simple SQL insert...
$insert_cmd = "INSERT INTO PhoneNumbers
(cusID, adtID, pntID, phnPart1,
phnPart2, phnPart3, phnExt)
VALUES
(?, ?, ?, ?, ?, ?, ?)";
$phone_insert = $gcssDBH->prepare($insert_cmd);
...
$phone_insert->execute(
$row->{'cust_id'},
$adtID,
$phoneNumberTypes->{'Day'},
$row->{'cust_phac'},
$row->{'cust_phpx'},
$row->{'cust_phsx'},
$row->{'cust_phex'}
);
...but I get this error: You have an error in your SQL syntax near '89, '45')' at line 4 (line 4 of the query, that is)
...with these values assigned to the variables:
$row->{'cust_id'} == 3048770
adtID == 2
pntID == 3
$row->{'cust_phac'} == 704
$row->{'cust_phpx'} == 53
$row->{'cust_phsx'} == 2 89
$row->{'cust_phex"} == 45
I don't see anything wrong with the code. Does something jump out at you? Yes, $row->{'cust_phsx'} == "2 89" but I don't see why that should make a difference. Obviously, I'm wrong. I'm under the impression that using placeholders gives me the right to avoid explicitly escaping naughty characters. |
Re: Using placeholders, getting unexpected SQL errors
by dga (Hermit) on Mar 13, 2003 at 22:59 UTC
|
It should be ok as long as cust_phsx is a text type field with space for 4 characters. However as I expect might be the case it is a number type ( int or float etc ) of field and space is not legal.
Alternately since the database field is a number type, DBI is doing no escaping and your query is actually .... VALUES ( 3048770, 2, 3, 704, 53, 2 89, 45 );
which will appear to the SQL parser to be a missing comma between the 2 and the 89
If either of these hold, then you will have to clean up the numbers before handing them to the query. If the field was a text type DBI would change it to "2 89" which would be ok, but not for a number type of field.
Update: corrected the text in the query to have the correct numbers and added a code tag to make the spaces show up better.
| [reply]
[d/l] |
Re: Using placeholders, getting unexpected SQL errors
by tantarbobus (Hermit) on Mar 14, 2003 at 01:31 UTC
|
What DBD are you using?
From the DBI docs:
If any arguments are given, then "execute" will effec-
tively call "bind_param" for each value before execut-
ing the statement. Values bound in this way are usu-
ally treated as "SQL_VARCHAR" types unless the driver
can determine the correct type (which is rare), or
unless "bind_param" (or "bind_param_inout") has
already been used to specify the type.
So maybe the driver that you are using is guessing incorrectly as to the type of "2 89" and it treating it as an integer. If that is the case, it looks as if you might have stumbled upon a bug in the DBD.
Try doing a DBI->trace(2) (or higher up to level 9) to see what is going on inside the DBI/DBD, and if you are still stumped or if it looks like there might be a bug, post the information to dbi-users.
In the meantime you can probably explicity spell out the bind type of that placeholder using bind_param():
$insert_cmd = "INSERT INTO PhoneNumbers
(cusID, adtID, pntID, phnPart1,
phnPart2, phnPart3, phnExt)
VALUES
(?, ?, ?, ?, ?, ?, ?)";
$phone_insert = $gcssDBH->prepare($insert_cmd);
$phone_insert->bind_param(6,undef,SQL_VARCHAR); #bind undef -- execute
+ will remember the bind type
$phone_insert->execute(
$row->{'cust_id'},
$adtID,
$phoneNumberTypes->{'Day'},
$row->{'cust_phac'},
$row->{'cust_phpx'},
$row->{'cust_phsx'},
$row->{'cust_phex'}
);
| [reply]
[d/l] |
|
|
I am thoroughly freaked out.
Problem solved, no idea why. Lemme explain...
I did not include a piece of code in my post:
if (($row->{'cust_phpx'} != '') && ($row->{'cust_phsx'} != '')) {
#
# Execute the query
#
}
I took your advice and traced the bastard, which showed me how MySQL was choking (even after using bind_param), if you will:
-> execute for DBD::mysql::st (DBI::st=HASH(0x8218138)~0x82180b4 '
+3048770' '2' '1' '704' 53 2 89 '45')
-> dbd_st_execute for 082180a8
Binding parameters: INSERT INTO PhoneNumbers (cusID, adtID, pntI
+D, phnPart1, phnPart2, phnPart3, phnExt)
VALUES ('3048770', '2', '1', '704', 53, 2 89, '45')
ERROR EVENT 1064 'You have an error in your SQL syntax near '89, '
+45')' at line 2' on DBI::st=HASH(0x82180b4)
On a lark I replaced the "!=" in the if test with "ne". No more errors.
I don't get it. It seems like the comparison operator "fiddled with the nature" of the variable it was operating on. Whatever that means. By the way check
this out.
See the fifth bugfix for v.053. (This has nothing to do with the code I'm working on, I don't even know what "WeSQL" is, but when I desperately googled for answers, I found this).
Something smells fishy...I hope it's just my lunch.
| [reply]
[d/l]
[select] |
Re: Using placeholders, getting unexpected SQL errors
by DrManhattan (Chaplain) on Mar 13, 2003 at 23:01 UTC
|
Using placeholders should allow you avoid quoting and escaping. Your DB driver seems to be confused as to whether $row->{'cust_phsx'} is a number or a string. You might try something like this to make the data type explicit:
$phone_insert->execute(
$row->{'cust_id'},
$adtID,
$phoneNumberTypes->{'Day'},
$row->{'cust_phac'},
$row->{'cust_phpx'},
$row->{'cust_phsx'} . "",
$row->{'cust_phex'}
);
-Matt | [reply]
[d/l]
[select] |
Re: Using placeholders, getting unexpected SQL errors
by Ineffectual (Scribe) on Mar 14, 2003 at 00:39 UTC
|
I'd use bind_param just because it sometimes gives you better error messages on what's wrong...
$phone_insert->bind_param(1, $row->{'cust_id'});
$phone_insert->bind_param(2, $adtID);
$phone_insert->bind_param(3, $phoneNumberTypes->{'Day'});
$phone_insert->bind_param(4, $row->{'cust_phac'});
$phone_insert->bind_param(5, $row->{'cust_phpx'});
$phone_insert->bind_param(6, $row->{'cust_phsx'});
$phone_insert->bind_param(1, $row->{'cust_phex'});
$phone_insert->execute or die "$dbh->errstr\n";
Where $dbh is your database handle.
It looks to me like everything is being inserted as a number, thus it doesn't put 's around the values and therefore it's balking at the space in the 2 89 value.
HTH
'Fect | [reply]
[d/l] |
|
|
