ASE Database forum - Problem in SQL Batch

Author

Problem in SQL Batch

Ollie

2006-03-15, 11:24 am

I am experiencing a strange problem. I am trying to remap
the foreign key reference in a table. I did a select from
the child table to retrieve all the rows that matches the
primary and used the value in the primary to update the
child. I am doing this in a batch script. As you can see
from the code illustration below, I display the values I am
about to update prior to the update. The strange behavior is
that the table never get updated and I don't get error. But
if I use the displayed value on a isql command prompt in an
update statement, it works fine.

I tried the same implementation in Perl and I noticed that
the binary datatype gets mangled, therefore a match is never
found by the update so I decided on using SQL. Could this be
related to the binary datatype?

Illustration:
primary table:=94
create table ptable (
ID numeric(10),
Oid binary(12),
.....
)

create table ctable (
ID numeric(10),
ptableOid binary(12), =3d=3d> foreign key (old)
ptableID numeric(10), =3d=3d> foreign key (new)
.....
)

Code illustration:

declare @old binary(12), @new numeric(10)
select @old =3d f.ptableOid, @new=3dp.ID
from ptable p, ctable c
where p.Oid =3d c.ptableOid

if exists (select 1 from ctable where ptableOid =3d @old)
---- sanity check
begin
print =93%1! =3d=3d> %2!=94, @old, @new
update ctable
set ptableID =3d @new
where ptableOid =3d @old
end

Note: this code is for illustration. The first select was
thrown into a temp table and I am iterating throught the
result set using a cursor type process. I don't want to
emphasize to much on that because I don't think is the cause
of my problem.

Ollie

2006-03-15, 8:24 pm

I have now been able to update the table, but I had to do a
update with a join of the 2 table. I strongly belief this
problem has to do with the binary datatype of the columns in
my where clause. Hopefully, I get someone out there to
confirm or shed light into this issue.

> I am experiencing a strange problem. I am trying to remap
> the foreign key reference in a table. I did a select from
> the child table to retrieve all the rows that matches the
> primary and used the value in the primary to update the
> child. I am doing this in a batch script. As you can see
> from the code illustration below, I display the values I
> am about to update prior to the update. The strange
> behavior is that the table never get updated and I don't
> get error. But if I use the displayed value on a isql
> command prompt in an update statement, it works fine.
>
> I tried the same implementation in Perl and I noticed that
> the binary datatype gets mangled, therefore a match is
> never found by the update so I decided on using SQL. Could
> this be related to the binary datatype?
>
> Illustration:
> primary table:=94
> create table ptable (
> ID numeric(10),
> Oid binary(12),
> .....
> )
>
> create table ctable (
> ID numeric(10),
> ptableOid binary(12), =3d=3d> foreign key (old)
> ptableID numeric(10), =3d=3d> foreign key (new)
> .....
> )
>
> Code illustration:
>
> declare @old binary(12), @new numeric(10)
> select @old =3d f.ptableOid, @new=3dp.ID
> from ptable p, ctable c
> where p.Oid =3d c.ptableOid
>
> if exists (select 1 from ctable where ptableOid =3d @old)
> ---- sanity check
> begin
> print =93%1! =3d=3d> %2!=94, @old, @new
> update ctable
> set ptableID =3d @new
> where ptableOid =3d @old
> end
>
> Note: this code is for illustration. The first select was
> thrown into a temp table and I am iterating throught the
> result set using a cursor type process. I don't want to
> emphasize to much on that because I don't think is the
> cause of my problem.

Sponsored Links