sql and dbi module question

[crackn101]

Hey everyone.
It's been a while since i've dropped by.
This seems to be the right place for a dbi question these days.
I have this sql statement:

  my($SQL_BOTH) = "UPDATE
		   efmserv_pcs.pcs_header_record
		   SET
		   highwarninggnsid = ?,
		   highwarningvalue =
		   (SELECT Value FROM efmserv_uis.CurrentValues WHERE PointIdLong=?),
		   lowwarninggnsid = ?,
		   lowwarningvalue =
		   (SELECT Value FROM efmserv_uis.CurrentValues WHERE PointIdLong=?)
		   WHERE		   efmserv_pcs.pcs_header_record.pointidlong=?;";	

$stboth = $dbh->prepare( $SQL_BOTH );

sample execution:

$stboth->execute(HIALARM,HIP_setpoint, LOALARM,LOP_setpoint, FACILITY_PRESSURE);

The error that I get:
[ATI][OpenRDA ODBC]Invalid Value XoType:12 for column:lowwarningvalue with XoType:8. (SQL-26000)(DBD: st_execute/SQLExecute err=-1)

Not much code i know.
This is but a small piece in a really long script.
The error recieved indicates an invalid type.
ie it's expecting the value to be a number (zero in this case) and interpreting the recieved value to actually be
a capital letter O.
You can see in the query that i never handle the actual
data. The manufactures of the software, who's database we are connected to,
insists that my script is 'modifing' that data somehow.
(Only zeros though) all other non-zero numbers work just fine.
I have even split this sql statement into the 3
individual queries, and checked the data from the
select queries before passing it to the update query.
The returned values are indeed numbers, not letters.
So this error should not exist.
My question is this, has anyone ever heard of the dbi module altering data ?
Even something as simple as auto inserting single
tic marks around data ?
I've never heard of this, and the dbi module has always
worked flawlessly for me.
The database in question, is not an access or sql server db.
It's their own proprietary db, but registers itself with
windows as a system DSN.
(Call it what you want, but the table structure is the same. hehe)

Any thoughts on this ?
TIA
crackn101

 

[crackn101]

I forgot to mention the fact the in the sample
execution of the above sql statement, that the
actual data passed to this query are perl variables,
not literal text like i have shown above.

Thanks.

 

[fishiface]

It's an SQL error rather than a perl error. The column types of Value in table efmserv_uis.CurrentValues
and of lowwarningvalue in table efmserv_pcs.pcs_header_record disagree.

Yours,

fish

"As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs."
--Maurice Wilkes

 

[mlibeson]

Look into bind_param, it may help


Michael Libeson

 

[crackn101]

Thanks for the suggestions.

I did find that the uis.CurrentValues.Value table column
is formated for text, and the target column
pcs_header_record.lowwarningvalue is formated as a double.
I do understand that it was a mismatch error that i was getting.
The software vendor deemed this not to be a bug on their side. So support from them was over.
But in the end, even though it was a tad bit more work,
i broke this sql query down into multiple queries.
A SELECT query to retrieve the current value,
and then trap the current value looking for the value of zero.
When found, instead of using the variable that represents
the value of zero, simple switching to the appropriate
UPDATE query that had the zero value hardcoded into it.
Accounting for each of the possible combinations of zero.
This is not nearly as compact as my original (single) sql statement,
but it's the only workaround that i've found that works.
I will look into the 'bind param' option as well.
Thanks.