Board index » delphi » Help with Cashed Updates, TUpdateSQL, and stored procedure

Help with Cashed Updates, TUpdateSQL, and stored procedure

I was hoping I could get some help in understanding and
correcting the two errors I appear to be getting.  I am just
trying to learn how to use the UpdateSQL so I need some
guidance.

I am using a query to display info in a grid (cannot request
live so using cashed updates, etc..)

My query passes has 3 input prams and get 3 fields back from
my local IB 4.2 server (I am using D3)

Well call these fields QIn1, QIn2, QIn3, Field1, Field2,
Field3.  The query has an TUpdateSQL connected where the
only SQL syntax provided right now is on the insert.

All the user is trying to do is post their inserts.

So in my code I do the following when the user posts their
inserted data.

    if MyQuery.UpdatesPending then
        MyQuery.Database.ApplyUpdates([MyQuery]);

Here is the MyQueryUpdateRecord for the query
  MyUpdateSQL.Apply(UpdateKind);
 UpdateAction := uaApplied;

The SQL for the insert calls a stored procedure.  Here is
the SQL
execute procedure insert_my_data(:QIn1, :QIn2, :Field1,
:Field2, :Field3);
This stored procedure works perfectly when run in DBExp.

The first error I get is field QIn1 is of unknown type.  The
error occurs on the apply(updatekind) line, which would make
sence...but Why?  Also in this process I get the  db is not
found error.

Then I tried substituting some dummy values in the
code...which yielded the following result.

I added the following lines to the MyUpdateRecord
if (UpdateKind = ukInsert) then
    begin
      TempID := gCurrentId;
      TempDate := Date;
    end;

Then I changed the UpdateSQL insert to
execute procedure insert_my_data(:TempID, :TempDate,
:Field1, :Field2, :Field3);

When I run this it appears to execute the stored procedure
but they the I get the error that the DB is not found.  This
hoses me up so bad that I have to close my project in Delphi
and reopen again.

Hum....

So what am I doing wrong with accessing the db?  What am I
also doing wrong with passing they parameters.

Thanks for your help and wisdom

Barb ;)

PS:  If I just hard code values into the stored procedure
the code works fine.

 

Re:Help with Cashed Updates, TUpdateSQL, and stored procedure


One thing I'am sure is that columns QIn1, QIn2 must be in MyQuery, because
the parameters :QIn1, etc must have a corresponding column (with the same
name) in MyQuery.

Quote
da...@goodnet.com wrote in message <34635CE6.ED018...@goodnet.com>...
>I was hoping I could get some help in understanding and
>correcting the two errors I appear to be getting.  I am just
>trying to learn how to use the UpdateSQL so I need some
>guidance.

>I am using a query to display info in a grid (cannot request
>live so using cashed updates, etc..)

>My query passes has 3 input prams and get 3 fields back from
>my local IB 4.2 server (I am using D3)

>Well call these fields QIn1, QIn2, QIn3, Field1, Field2,
>Field3.  The query has an TUpdateSQL connected where the
>only SQL syntax provided right now is on the insert.

>All the user is trying to do is post their inserts.

>So in my code I do the following when the user posts their
>inserted data.

>    if MyQuery.UpdatesPending then
>        MyQuery.Database.ApplyUpdates([MyQuery]);

>Here is the MyQueryUpdateRecord for the query
>  MyUpdateSQL.Apply(UpdateKind);
> UpdateAction := uaApplied;

>The SQL for the insert calls a stored procedure.  Here is
>the SQL
>execute procedure insert_my_data(:QIn1, :QIn2, :Field1,
>:Field2, :Field3);
>This stored procedure works perfectly when run in DBExp.

>The first error I get is field QIn1 is of unknown type.  The
>error occurs on the apply(updatekind) line, which would make
>sence...but Why?  Also in this process I get the  db is not
>found error.

>Then I tried substituting some dummy values in the
>code...which yielded the following result.

>I added the following lines to the MyUpdateRecord
>if (UpdateKind = ukInsert) then
>    begin
>      TempID := gCurrentId;
>      TempDate := Date;
>    end;

>Then I changed the UpdateSQL insert to
>execute procedure insert_my_data(:TempID, :TempDate,
>:Field1, :Field2, :Field3);

>When I run this it appears to execute the stored procedure
>but they the I get the error that the DB is not found.  This
>hoses me up so bad that I have to close my project in Delphi
>and reopen again.

>Hum....

>So what am I doing wrong with accessing the db?  What am I
>also doing wrong with passing they parameters.

>Thanks for your help and wisdom

>Barb ;)

>PS:  If I just hard code values into the stored procedure
>the code works fine.

Re:Help with Cashed Updates, TUpdateSQL, and stored procedure


Thanks that helped.  I added in so that the QIn1 and QIn2
are returned back with the 3 other fields in the query.  So
thank you for your help.

But now I have another question that maybe you can help
with.

If the query comes back with null in one of the QIn1 or QIn2
variables what is the best method to update these data set
values?

I have tried a few different methods without getting the
results I want.  Initially was getting that the dataset was
not in edit or insert mode, if I tried to store values in to
the dataset for MyQuery.

So in the MyQueryUpdateRecord before the apply(updatekind),
I set the MyQuery dataset to edit and then updated the two
values in the code, if they were null.  The problem is the
TUpdateSQL insert where I call the stored procedure and pass
in these values.....errors back saying the values are null.

I have tried OLD_ and the current values.  In the de{*word*81}
after the setting of the values they do appear to be stored
in the dataset.

What am I doing wrong in applying the TUpdateSQL, or in
using the dataset.

thanks for your help and wisdom.

Barb ;)

Quote
Wilson Wu wrote:
> One thing I'am sure is that columns QIn1, QIn2 must be in
> MyQuery, because
> the parameters :QIn1, etc must have a corresponding column
> (with the same
> name) in MyQuery.

> da...@goodnet.com wrote in message
> <34635CE6.ED018...@goodnet.com>...
> >I was hoping I could get some help in understanding and
> >correcting the two errors I appear to be getting.  I am
> just
> >trying to learn how to use the UpdateSQL so I need some
> >guidance.

> >I am using a query to display info in a grid (cannot
> request
> >live so using cashed updates, etc..)

> >My query passes has 3 input prams and get 3 fields back
> from
> >my local IB 4.2 server (I am using D3)

> >Well call these fields QIn1, QIn2, QIn3, Field1, Field2,
> >Field3.  The query has an TUpdateSQL connected where the
> >only SQL syntax provided right now is on the insert.

> >All the user is trying to do is post their inserts.

> >So in my code I do the following when the user posts
> their
> >inserted data.

> >    if MyQuery.UpdatesPending then
> >        MyQuery.Database.ApplyUpdates([MyQuery]);

> >Here is the MyQueryUpdateRecord for the query
> >  MyUpdateSQL.Apply(UpdateKind);
> > UpdateAction := uaApplied;

> >The SQL for the insert calls a stored procedure.  Here is

> >the SQL
> >execute procedure insert_my_data(:QIn1, :QIn2, :Field1,
> >:Field2, :Field3);
> >This stored procedure works perfectly when run in DBExp.

> >The first error I get is field QIn1 is of unknown type.
> The
> >error occurs on the apply(updatekind) line, which would
> make
> >sence...but Why?  Also in this process I get the  db is
> not
> >found error.

> >Then I tried substituting some dummy values in the
> >code...which yielded the following result.

> >I added the following lines to the MyUpdateRecord
> >if (UpdateKind = ukInsert) then
> >    begin
> >      TempID := gCurrentId;
> >      TempDate := Date;
> >    end;

> >Then I changed the UpdateSQL insert to
> >execute procedure insert_my_data(:TempID, :TempDate,
> >:Field1, :Field2, :Field3);

> >When I run this it appears to execute the stored
> procedure
> >but they the I get the error that the DB is not found.
> This
> >hoses me up so bad that I have to close my project in
> Delphi
> >and reopen again.

> >Hum....

> >So what am I doing wrong with accessing the db?  What am
> I
> >also doing wrong with passing they parameters.

> >Thanks for your help and wisdom

> >Barb ;)

> >PS:  If I just hard code values into the stored procedure

> >the code works fine.

Other Threads