cast needed - but where and why?
Hello list,
I am getting the following error after upgrading from 7.4.6 to 8.3.6
and can't figure out what is wrong. Any help would be greatly appreciated.
from our program:
sqlcode=-400 errmsg='column "event_ref_log_no" is of type integer but expression is of type text' in line 4138.
from pg_log:
2009-04-02 10:45:10 EDT:srm2api:ERROR: column "event_ref_log_no" is of type integer but expression is of type text at character 146
2009-04-02 10:45:10 EDT:srm2api:HINT: You will need to rewrite or cast the expression.
2009-04-02 10:45:10 EDT:srm2api:STATEMENT: insert into t_unit_event_log ( event_log_no , unit_serial_no , event_type
, event_category , event_mesg , event_severity , event_status , event_ref_log_no , event_logged_by , event_date ,
alarm , last_updated_by , last_updated_date ) values ( nextval ( 'seq_event_log_no' ) , $1 , $2 , $3 , $4 , $
5 , $6 , case when $7 > 0 then $8 else null end , current_user , now () , $9 , current_user , now () )
from our program:
exec sql begin declare section;
int h_cnt = 0;
int h_event_ref_log_no = NULL;
...
// insert into uel
exec sql insert into t_unit_event_log
(event_log_no,
unit_serial_no,
event_type,
event_category,
event_mesg,
event_severity,
event_status,
event_ref_log_no,
event_logged_by,
event_date,
alarm,
last_updated_by,
last_updated_date)
values (nextval('seq_event_log_no'),
:h_serial_no,
:h_type,
:h_category,
:h_mesg,
:h_sev,
:h_status,
case when :h_event_ref_log_no > 0
then :h_event_ref_log_no
else null end,
current_user,
now(),
:h_alarm,
current_user,
now());
Thanks,
Steve
Steve Clark <sclark@netwolves.com> writes:
I am getting the following error after upgrading from 7.4.6 to 8.3.6
and can't figure out what is wrong. Any help would be greatly appreciated.
2009-04-02 10:45:10 EDT:srm2api:ERROR: column "event_ref_log_no" is of type integer but expression is of type text at character 146
I don't know ecpg very well, but if it doesn't provide any information
about parameter datatypes then the backend would resolve this:
case when $7 > 0 then $8 else null end
as producing a result of type "text". 7.4 would have allowed that to be
cast to int silently, but 8.3 won't (and the runtime cast involved
would've been expensive anyway). I suggest sticking a cast directly
on the ambiguous parameter, ie
case when :h_event_ref_log_no > 0
then :h_event_ref_log_no :: integer
else null end,
(You needn't cast the null, since the type attached to the other case
arm is a sufficient cue.)
regards, tom lane
Tom Lane wrote:
Steve Clark <sclark@netwolves.com> writes:
I am getting the following error after upgrading from 7.4.6 to 8.3.6
and can't figure out what is wrong. Any help would be greatly appreciated.
2009-04-02 10:45:10 EDT:srm2api:ERROR: column "event_ref_log_no" is of type integer but expression is of type text at character 146I don't know ecpg very well, but if it doesn't provide any information
about parameter datatypes then the backend would resolve this:case when $7 > 0 then $8 else null end
as producing a result of type "text". 7.4 would have allowed that to be
cast to int silently, but 8.3 won't (and the runtime cast involved
would've been expensive anyway). I suggest sticking a cast directly
on the ambiguous parameter, iecase when :h_event_ref_log_no > 0
then :h_event_ref_log_no :: integer
else null end,(You needn't cast the null, since the type attached to the other case
arm is a sufficient cue.)regards, tom lane
Thanks Tom,
that fixed the problem. I wasn't thinking about what the back end
was seeing, only that it was defined in my pgc program as an int.
Regards,
Steve