Обсуждение: cast problem 8.3.1

Hello List,

I can't seem to figure out what is this code that worked on 7.4.x.
I've added cast to everything but still
get:

postgres error log:
ERROR:  operator does not exist: text = integer
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
STATEMENT:  update T_MON_DEVICE_STATUS set device_ip  =  $1  :: inet
  , status  =  $2  :: integer  , status_date  = now () ,
last_event_log_no  = case  $3  when 0 then null else  $4  :: integer
end  where unit_serial_no =  $5  :: text   and device_name =  $6  :: text

pgc code:
EXEC SQL UPDATE T_MON_DEVICE_STATUS
SET device_ip = :h_device_ip::inet,
        status = :h_status::integer,
   status_date = now(),
last_event_log_no =
       case :h_event_log_no when 0 then null
           else
            :h_event_log_no::integer end
  WHERE unit_serial_no = :h_unit_serial_no::text
       AND device_name = :h_device_name::text;

table:
\d t_mon_device_status
             Table "public.t_mon_device_status"
       Column       |           Type           | Modifiers
-------------------+--------------------------+-----------
  unit_serial_no    | character varying(15)    | not null
  device_name       | character varying(64)    | not null
  device_ip         | inet                     | not null
  status            | integer                  | not null
  status_date       | timestamp with time zone | not null
  last_event_log_no | integer                  |
Indexes:
     "pk_tmds_usn_dn" PRIMARY KEY, btree (unit_serial_no, device_name)
Foreign-key constraints:
     "fk_tmds_usn" FOREIGN KEY (unit_serial_no) REFERENCES
t_unit_status_log(unit_serial_no) ON DELETE CASCADE

maybe someone else can see where I am going wrong.

Thanks in advance,
Steve

Steve Clark escribió:
> Hello List,
>
> I can't seem to figure out what is this code that worked on 7.4.x.
> I've added cast to everything but still
> get:
>
> postgres error log:
> ERROR:  operator does not exist: text = integer
> HINT:  No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
> STATEMENT:  update T_MON_DEVICE_STATUS set device_ip  =  $1  :: inet
>  , status  =  $2  :: integer  , status_date  = now () ,
> last_event_log_no  = case  $3  when 0 then null else  $4  :: integer
> end  where unit_serial_no =  $5  :: text   and device_name =  $6  :: text
>
> pgc code:
> EXEC SQL UPDATE T_MON_DEVICE_STATUS
> SET device_ip = :h_device_ip::inet,
>        status = :h_status::integer,
>   status_date = now(),
> last_event_log_no =
>       case :h_event_log_no when 0 then null
>           else
>            :h_event_log_no::integer end
>  WHERE unit_serial_no = :h_unit_serial_no::text
>       AND device_name = :h_device_name::text;
>
> table:
> \d t_mon_device_status
>             Table "public.t_mon_device_status"
>       Column       |           Type           | Modifiers
> -------------------+--------------------------+-----------
>  unit_serial_no    | character varying(15)    | not null
>  device_name       | character varying(64)    | not null
>  device_ip         | inet                     | not null
>  status            | integer                  | not null
>  status_date       | timestamp with time zone | not null
>  last_event_log_no | integer                  |
> Indexes:
>     "pk_tmds_usn_dn" PRIMARY KEY, btree (unit_serial_no, device_name)
> Foreign-key constraints:
>     "fk_tmds_usn" FOREIGN KEY (unit_serial_no) REFERENCES
> t_unit_status_log(unit_serial_no) ON DELETE CASCADE
>
> maybe someone else can see where I am going wrong.
>
> Thanks in advance,
> Steve
>
h_event_log_no is not casted to integer....maybe it's getting a string
and the problem is comparing to 0?

<snip>
|>
|> postgres error log:
|> ERROR:  operator does not exist: text = integer
|> HINT:  No operator matches the given name and argument type(s). You
|> might need to add explicit type casts.
|> STATEMENT:  update T_MON_DEVICE_STATUS set device_ip  =  $1  :: inet
|>   , status  =  $2  :: integer  , status_date  = now () ,
|> last_event_log_no  = case  $3  when 0 then null else  $4  :: integer
|> end  where unit_serial_no =  $5  :: text   and device_name =
|>  $6  :: text
|>
</snip>

You're not casting your $3.

Charles Simard wrote:
> <snip>
> |>
> |> postgres error log:
> |> ERROR:  operator does not exist: text = integer
> |> HINT:  No operator matches the given name and argument type(s). You
> |> might need to add explicit type casts.
> |> STATEMENT:  update T_MON_DEVICE_STATUS set device_ip  =  $1  :: inet
> |>   , status  =  $2  :: integer  , status_date  = now () ,
> |> last_event_log_no  = case  $3  when 0 then null else  $4  :: integer
> |> end  where unit_serial_no =  $5  :: text   and device_name =
> |>  $6  :: text
> |>
> </snip>
>
> You're not casting your $3.
>
>
thanks Charles and Rodrigo - that fixed it.

Steve