Board index » delphi » I say it CANNOT be done.

I say it CANNOT be done.

Folks,

has anyone actually got back results from a call to a stored procedure from
within a TIBDataSet.QInsert property, where the resulting data was assigned
from within that called stored procedure and the QInsert SQL was called as a
result of a call to TIBDataSet.Append or Insert.

I say it CANNOT be done, purely because nobody can properly answer all the
many queries I have posted about this. People keep saying it can be done,
but nobody has given me even a simple practical example.

Am I right, or does somebody actually know how to do it?

Josh.

 

Re:I say it CANNOT be done.


Quote
Josh Crane wrote:

> Folks,

> has anyone actually got back results from a call to a stored procedure from
> within a TIBDataSet.QInsert property, where the resulting data was assigned
> from within that called stored procedure and the QInsert SQL was called as a
> result of a call to TIBDataSet.Append or Insert.

First off you should not be using that property.  It is not going to be public
much longer and might not even be a IBSQL in the future either.  Secondly the
InsertSQL is design to only be for insert SQL so you are trying to do something
it is not design for.  It is possible the return value of a Stored Procedure
though like

  ShowMessage(IBDataset1.QInsert.Current.ByName('i').AsString);

with the Insert SQL being like

execute Procedure test_return :INITL_DATE

and the SP looking like

set term ^;

create procedure test_return (t timestamp)
returns (i integer)
as
begin
  i = gen_id(visit_gen, 1);
  insert into test_table values (:i, :t);
end^

set term ;^

The correct way to accomplish what you want you want to turn on cached updates,
Setup an OnUpdateRecord and do something like

  if UpdateKind = ukInsert then
  begin
    IBStoredProc1.ParamByName('t').AsDateTime :=
Dataset.FieldByName('INITL_DATE').AsDateTime;
    IBStoredProc1.ExecProc;
    DataSet.FieldByName('ID').NewValue :=
IBStoredProc1.ParamByName('i').AsInteger;
    UpdateAction := uaApplied;
  end;

What you are trying to do is highly not recommended and I will not gaurantee
that it will work in future versions of IBX.

Also do not cross post your messages.  Pick the single most appropriate group.
I skip intentionally cross/multi posted messages since I consider it a potential
waste of my time as some one might have already answered it elsewhere.  The
Borland rules of use prohibit cross posting of messages.

Quote
> I say it CANNOT be done, purely because nobody can properly answer all the
> many queries I have posted about this. People keep saying it can be done,
> but nobody has given me even a simple practical example.

> Am I right, or does somebody actually know how to do it?

> Josh.

--
Jeff Overcash (TeamB)   | Talk about failure
(Please do not email    | To fall is not to fail
 me directly unless     | Failure isn't about falling down
 asked.  Thank You)     | Failure is staying down (Marillion)

Re:I say it CANNOT be done.


"Jeff Overcash (TeamB)" <overc...@onramp.net> wrote in message
news:3A161597.BA21964E@onramp.net...

Quote

>   ShowMessage(IBDataset1.QInsert.Current.ByName('i').AsString);

> with the Insert SQL being like

> execute Procedure test_return :INITL_DATE

> and the SP looking like

> set term ^;

> create procedure test_return (t timestamp)
> returns (i integer)
> as
> begin
>   i = gen_id(visit_gen, 1);
>   insert into test_table values (:i, :t);
> end^

> set term ;^

> The correct way to accomplish what you want you want to turn on cached
updates,
> Setup an OnUpdateRecord and do something like

>   if UpdateKind = ukInsert then
>   begin
>     IBStoredProc1.ParamByName('t').AsDateTime :=
> Dataset.FieldByName('INITL_DATE').AsDateTime;
>     IBStoredProc1.ExecProc;
>     DataSet.FieldByName('ID').NewValue :=
> IBStoredProc1.ParamByName('i').AsInteger;
>     UpdateAction := uaApplied;
>   end;

> What you are trying to do is highly not recommended and I will not
gaurantee
> that it will work in future versions of IBX.

So, you are saying that the QInsert property is not to be used, and I should
in fact be using an entirely separate IBStoredProc object for inserting and
pass in the parameters from the TIBDataSet fields. Is that right?

If so, what about QUpdate, QDelete? Should I forget these also?

A passing observation... I noticed that I when using a QInsert property with
a select from a procedure that performs an insert (amongst other things),
that data isn't actually inserted at all. I need to execute the procedure
from within QInsert if I want it to perform any modifications. Is this
something that you're aware of or should I look a little more closely at
what I was doing?

Quote
> Also do not cross post your messages.  Pick the single most appropriate
group.
> I skip intentionally cross/multi posted messages since I consider it a
potential
> waste of my time as some one might have already answered it elsewhere.
The
> Borland rules of use prohibit cross posting of messages.

Apologies, I wasn't aware of that. I'll take another peek at the rules.

Thanks for all your ongoing help Jeff, I do really appreciate it.

Re:I say it CANNOT be done.


Quote
Josh Crane wrote:

> So, you are saying that the QInsert property is not to be used, and I should
> in fact be using an entirely separate IBStoredProc object for inserting and
> pass in the parameters from the TIBDataSet fields. Is that right?

Or an IBUpdateSQL or a IBSQL or whatever you choose to use.  The OnUpdateRecord
event is designed for more complex update routines which is what you are trying
to do.  You are not doing an Insert statement you are doing something more.  If
you want to update the record on the client side with the value returned you
have to use the OnUpdateRecord event unless you want to edit/change/post the
value locally.  With OnUpdateRecord you just access the Field's NewValue and
assign it.  

Quote
> If so, what about QUpdate, QDelete? Should I forget these also?

All the QXxxxx should be avoided.  That is allowing you direct access to the
implementation layer.  It should never have been allowed as it restricts future
changes to how an IBCustomDataset handles working directly with IB.  People have
been asking for the ability to script an Insert for instance.  I can not
implement that and keep the current underlying datatype.  As long as it is
public I will not make these type of changes.  I plan on moving all 4 of these
back to protected which will allow for changes in the implementation of an
insert in the future.

Quote
> A passing observation... I noticed that I when using a QInsert property with
> a select from a procedure that performs an insert (amongst other things),
> that data isn't actually inserted at all. I need to execute the procedure
> from within QInsert if I want it to perform any modifications. Is this
> something that you're aware of or should I look a little more closely at
> what I was doing?

I can't think of a reason why this would happen.  Of course QInsert, QDelete and
QModify are all supposed to not return result sets.  None of them will work
correctly if you do return a result set.  Nowhere are those IBSQL's closed
therefor if you return a result set the second time you try to insert, the
result set will still be open, the InternalSetParams will basically fail (since
you set params on a with a closed dataset) and calling ExecQuery on an already
opened dataset will also fail.  Only ResultSQL should return a result set and
IBX closes it after it has refreshed the current record.

--
Jeff Overcash (TeamB)   | Talk about failure
(Please do not email    | To fall is not to fail
 me directly unless     | Failure isn't about falling down
 asked.  Thank You)     | Failure is staying down (Marillion)

Re:I say it CANNOT be done.


Quote
Josh Crane wrote in message <3a162013_2@dnews>...

>So, you are saying that the QInsert property is not to be used, and I
should
>in fact be using an entirely separate IBStoredProc object for inserting and
>pass in the parameters from the TIBDataSet fields. Is that right?

That would be the best way to do it IMO.

Quote
>If so, what about QUpdate, QDelete? Should I forget these also?

Yes. IIRC all of these once were private or protected and as Jeff has said
they are to become so again.

Quote
>A passing observation... I noticed that I when using a QInsert property
with
>a select from a procedure that performs an insert (amongst other things),
>that data isn't actually inserted at all.

Is it wrapped in a transaction? Also in order to select from a stored proc,
it needs to have a Suspend statement. But as Jeff has already said, you are
using this in ways not intended and should choose a more appropriate method.

--
Wayne Niddery (WinWright Inc.)
RADBooks - http://members.home.net/wniddery/
I love deadlines. I like the whooshing sound they make as they pass by -
Douglas Adams

Re:I say it CANNOT be done.


I've been stuck on this for a week or 2 now, as you can imagine, I'm very
happy to be moving on.
Thanks very much for your help Jeff, and thankyou Wayne for your input as
well.

Quote
"Josh Crane" <develo...@reiq.com.au> wrote in message

news:3a162013_2@dnews...
Quote
> "Jeff Overcash (TeamB)" <overc...@onramp.net> wrote in message
> news:3A161597.BA21964E@onramp.net...

> >   ShowMessage(IBDataset1.QInsert.Current.ByName('i').AsString);

> > with the Insert SQL being like

> > execute Procedure test_return :INITL_DATE

> > and the SP looking like

> > set term ^;

> > create procedure test_return (t timestamp)
> > returns (i integer)
> > as
> > begin
> >   i = gen_id(visit_gen, 1);
> >   insert into test_table values (:i, :t);
> > end^

> > set term ;^

> > The correct way to accomplish what you want you want to turn on cached
> updates,
> > Setup an OnUpdateRecord and do something like

> >   if UpdateKind = ukInsert then
> >   begin
> >     IBStoredProc1.ParamByName('t').AsDateTime :=
> > Dataset.FieldByName('INITL_DATE').AsDateTime;
> >     IBStoredProc1.ExecProc;
> >     DataSet.FieldByName('ID').NewValue :=
> > IBStoredProc1.ParamByName('i').AsInteger;
> >     UpdateAction := uaApplied;
> >   end;

> > What you are trying to do is highly not recommended and I will not
> gaurantee
> > that it will work in future versions of IBX.

> So, you are saying that the QInsert property is not to be used, and I
should
> in fact be using an entirely separate IBStoredProc object for inserting
and
> pass in the parameters from the TIBDataSet fields. Is that right?

> If so, what about QUpdate, QDelete? Should I forget these also?

> A passing observation... I noticed that I when using a QInsert property
with
> a select from a procedure that performs an insert (amongst other things),
> that data isn't actually inserted at all. I need to execute the procedure
> from within QInsert if I want it to perform any modifications. Is this
> something that you're aware of or should I look a little more closely at
> what I was doing?

> > Also do not cross post your messages.  Pick the single most appropriate
> group.
> > I skip intentionally cross/multi posted messages since I consider it a
> potential
> > waste of my time as some one might have already answered it elsewhere.
> The
> > Borland rules of use prohibit cross posting of messages.

> Apologies, I wasn't aware of that. I'll take another peek at the rules.

> Thanks for all your ongoing help Jeff, I do really appreciate it.

Re:I say it CANNOT be done.


Sorry gents, but I still can't see a solution.

Jeff, your example makes sense, but what component would let me do a
dataset.append or dataset.insert without having assigned an update object or
the InsertSQL property?

If I use a TIBDataSet, QInsert has not been assigned, therefore I cannot
insert. Knowing that I can't use the QInsert to return data, I discard the
TIBDataSet and use a TIBQuery... it throws up "Cannot modify a read-only
dataset.", so I add a TIBUpdateSQL, but can't get the output parameter from
a procedure executed from within the InsertSQL property of the update
object.

I just can't seem to get it right.

I guess I need an explicit example of the following including object types
IF it can actually be done?

my criteria is
- a simple insertable dataset returned from a storedprocedure...
- when an insert is made, I need to return the newly inserted ID from within
the called insertion stored procedure by using an output parameter

Again, I appreciate your help. Thankyou.

Josh.

Quote
"Josh Crane" <develo...@reiq.com.au> wrote in message

news:3a162013_2@dnews...
Quote
> "Jeff Overcash (TeamB)" <overc...@onramp.net> wrote in message
> news:3A161597.BA21964E@onramp.net...

> >   ShowMessage(IBDataset1.QInsert.Current.ByName('i').AsString);

> > with the Insert SQL being like

> > execute Procedure test_return :INITL_DATE

> > and the SP looking like

> > set term ^;

> > create procedure test_return (t timestamp)
> > returns (i integer)
> > as
> > begin
> >   i = gen_id(visit_gen, 1);
> >   insert into test_table values (:i, :t);
> > end^

> > set term ;^

> > The correct way to accomplish what you want you want to turn on cached
> updates,
> > Setup an OnUpdateRecord and do something like

> >   if UpdateKind = ukInsert then
> >   begin
> >     IBStoredProc1.ParamByName('t').AsDateTime :=
> > Dataset.FieldByName('INITL_DATE').AsDateTime;
> >     IBStoredProc1.ExecProc;
> >     DataSet.FieldByName('ID').NewValue :=
> > IBStoredProc1.ParamByName('i').AsInteger;
> >     UpdateAction := uaApplied;
> >   end;

> > What you are trying to do is highly not recommended and I will not
> gaurantee
> > that it will work in future versions of IBX.

> So, you are saying that the QInsert property is not to be used, and I
should
> in fact be using an entirely separate IBStoredProc object for inserting
and
> pass in the parameters from the TIBDataSet fields. Is that right?

> If so, what about QUpdate, QDelete? Should I forget these also?

> A passing observation... I noticed that I when using a QInsert property
with
> a select from a procedure that performs an insert (amongst other things),
> that data isn't actually inserted at all. I need to execute the procedure
> from within QInsert if I want it to perform any modifications. Is this
> something that you're aware of or should I look a little more closely at
> what I was doing?

> > Also do not cross post your messages.  Pick the single most appropriate
> group.
> > I skip intentionally cross/multi posted messages since I consider it a
> potential
> > waste of my time as some one might have already answered it elsewhere.
> The
> > Borland rules of use prohibit cross posting of messages.

> Apologies, I wasn't aware of that. I'll take another peek at the rules.

> Thanks for all your ongoing help Jeff, I do really appreciate it.

Re:I say it CANNOT be done.


Quote
Josh Crane wrote:

> Jeff, your example makes sense, but what component would let me do a
> dataset.append or dataset.insert without having assigned an update object or
> the InsertSQL property?

        One way around this issue is to use a TClientDataset to display the
data.  You link the TClientDataset to the TIBDataset using a
TDatasetProvider component.  You then add a BeforeUpdateRecord event
handler to the TDatasetProvider, which will give you a chance to
intercept INSERTs (and UPDATEs, DELETEs...) and handle them yourself.
By setting the Applied argument TRUE, your TIBDataset will never see the
INSERT.

        Note that if you do this all on a single app running on a single
machine, no MIDAS license is required.  It does, however, make it very
easy to move to a multitier configuration in the future.

        HTH,

        -Craig

--
Craig Stuntz               Vertex Systems Corporation
Senior Developer           http://www.vertexsoftware.com

Delphi/InterBase weblog:   http://delphi.weblogs.com

Re:I say it CANNOT be done.


Hi:

  Using IBDataSet's etc and IBStoredProc

  I use a stored procedure to insert a new record with the next
sequential Id number and return that Id for use in looking up the
record (refreshed dataset) for updateing and adding child records.
If I cancel, I rollback the edit on the new record and the insert
on the detail records then delete the master record based on the
returned Id.

SET TERM ## ;
CREATE PROCEDURE GetNextMemberId
  RETURNS (gotid INTEGER)
AS
  DECLARE VARIABLE currentid INTEGER;
  DECLARE VARIABLE nextid INTEGER;
  DECLARE VARIABLE startflag INTEGER;
BEGIN
  startflag = 0;
  FOR SELECT ID
    FROM MEMBERS
  WHERE ID >= 1 AND ID <= 4999
  ORDER BY ID ASC
  INTO nextid
  DO
    BEGIN
      IF ((nextid > 1) AND (startflag = 0)) THEN
        BEGIN
          gotid = nextid-1;
          EXECUTE PROCEDURE InsertNewMember (gotid);
          SUSPEND;
          EXIT;
        END
      ELSE IF ((nextid IS NULL) AND (startflag = 0)) THEN
        BEGIN
          gotid = 1;
          EXECUTE PROCEDURE InsertNewMember (gotid);
          SUSPEND;
          EXIT;
        END
      ELSE
        BEGIN
          IF (startflag = 0) THEN
            BEGIN
              currentid = nextid;
              startflag = 1;
            END
          IF (nextid = 4999) THEN        
            BEGIN
              gotid = -1;
              SUSPEND;
              EXIT;
            END
          ELSE IF (nextid > currentid + 1) THEN
            BEGIN
              gotid = currentid + 1;
              EXECUTE PROCEDURE InsertNewMember (gotid);
              SUSPEND;
              EXIT;
            END
          ELSE IF (nextid = currentid + 1) THEN
              currentid = currentid + 1;
        END
    END
    BEGIN
      gotid = currentid + 1;
      EXECUTE PROCEDURE InsertNewmember (gotid);
      SUSPEND;
      EXIT;
    END

END ##
SET TERM ; ##

---------------------------------------------------------------------------------------------------------

SET TERM ## ;
CREATE PROCEDURE InsertNewMember (gotid INTEGER)
AS
BEGIN
  INSERT INTO MEMBERS
  (ID)
  VALUES(:gotid);
END ##
SET TERM ; ##

Re:I say it CANNOT be done.


Hi:

   Forgot the rest of it: The waits and goto's are to resolve
collisions with simultaneous requests for a new Id. The parameters
for the IBTransaction:

write
read_committed
no_rec_version
wait
protected
lock_read=MEMBERS

-----------------------------------------------------------------------------------------
   //Get next sequential free member id
   int FullId;
   MemId = GetNextMemberId(Full);
   if (MemId == -1)
     return;
   FullId = MemId;

   //Reindex on Id field
   MainDM->IBM->Close();
   MainDM->IBM->SelectSQL->Clear();
   MainDM->IBM->SelectSQL->Add("SELECT * FROM MEMBERS");
   MainDM->IBM->SelectSQL->Add("ORDER BY ID ASC");
   MainDM->IBM->Open();

   //Locate the newly inserted master record and update it
   TLocateOptions Opts;
   Opts<<loCaseInsensitive;
   IBM->Locate("ID", FullId, Opts);
   IBM->Edit();
   IBM->FieldByName("ROA_ID")->AsString =
SP.Data[UserData]->Strings[UStateId];
   IBM->FieldByName("STATUS_REC")->AsString = "ACTIVE";
   IBM->FieldByName("DATE_UP")->AsDateTime = Date();
   IBM->FieldByName("REF_REV")->AsFloat =
StrToFloat(SP.Data[FileData]->Strings[FTRGRev]);
   IBM->FieldByName("MEM_PIN")->AsInteger = 0;
   IBM->FieldByName("STATUS_MEM")->AsString = "ACTIVE";
   IBM->FieldByName("NUMBER")->AsInteger = 0;
   IBM->FieldByName("POBOX")->AsInteger = 0;
   IBM->FieldByName("NEWS_MAIL")->AsString = "N";
   IBM->FieldByName("EMAIL")->AsString = "  ";

   // Insert Initial Payment Record
   IBM1->Insert();
   IBM1->FieldByName("ID")->AsInteger = FullId;
   IBM1->FieldByName("DATE_APPL")->AsDateTime = aDate;
   IBM1->FieldByName("UNITS_MEM")->AsString =
SP.Data[FeeData]->Strings[FCat1];
   IBM1->FieldByName("APPL_FEE")->AsFloat =
StrToFloat(SP.Data[FeeData]->Strings[FInitial]);
   IBM1->FieldByName("FIRST_DUES")->AsFloat =
StrToFloat(SP.Data[FeeData]->Strings[FMemberCat1]);
   IBM1->FieldByName("CHKCSH")->AsInteger      = 2;
   MainForm->AppCHK->Caption = "Check Number";
   IBM1->FieldByName("CHKCSH_AS")->AsInteger   = 2;
   IBM1->FieldByName("NUMBER_AS")->AsInteger   = 0;
   IBM1->FieldByName("NUMBER_FEE")->AsInteger  = 0;
   MainForm->AssCHK->Caption = "Check Number";

-----------------------------------------------------------------------------------
//---------------------------------------------------------------------------
int __fastcall TMainDM::GetNextMemberId(int Identity)
{
  //Declare and initialize variables
  int GotId, Rand;

  //Select applicable table
  switch (Identity)
      {
       case     0 : //Full Member
                    {
                     MemAgain:
                     try
                       {

IBGetNextMemberIdTransaction->StartTransaction();
                        IBGetNextMemberIdQuery->Open();
                       }
                     catch(...)
                       {
                        Randomize();
                        Rand = random(100);
                        Rand = 100 * Rand;
                        for (int i = 0; i <= Rand; i++)
                          Application->ProcessMessages();
                        IBGetNextMemberIdTransaction->Rollback();
                        goto MemAgain; // Loop to start on error
                       }
                     GotId =
IBGetNextMemberIdQuery->FieldByName("GotId")->AsInteger;
                     IBGetNextMemberIdTransaction->Commit();
                     IBGetNextMemberIdQuery->Close();
                     break;
                    }

       case     1 : //Associate member
                    {
                     AssocAgain:
                     try
                       {

IBGetNextMemberIdTransaction->StartTransaction();
                        IBGetNextMemberIdQuery->Open();
                       }
                     catch(...)
                       {
                        Randomize();
                        Rand = random(100);
                        Rand = 100 * Rand;
                        for (int i = 0; i <= Rand; i++)
                          Application->ProcessMessages();
                        IBGetNextMemberIdTransaction->Rollback();
                        goto AssocAgain; // Loop to start on error
                       }
                     GotId =
IBGetNextMemberIdQuery->FieldByName("GotId")->AsInteger;
                     IBGetNextMemberIdTransaction->Commit();
                     IBGetNextMemberIdQuery->Close();
                     break;
                    }
       case  2 :    //Non-Member
                    {
                     NonMemAgain:
                     try
                       {

IBGetNextNonMemberIdTransaction->StartTransaction();
                        IBGetNextNonMemberIdQuery->Open();
                       }
                     catch(...)
                       {
                        Randomize();
                        Rand = random(100);
                        Rand = 100 * Rand;
                        for (int i = 0; i <= Rand; i++)
                          Application->ProcessMessages();

IBGetNextNonMemberIdTransaction->Rollback();
                        goto NonMemAgain; // Loop to start on
error
                       }
                     GotId =
IBGetNextNonMemberIdQuery->FieldByName("GotId")->AsInteger;
                     IBGetNextNonMemberIdTransaction->Commit();
                     IBGetNextNonMemberIdQuery->Close();
                     break;
                    }
       case  3 :    //Non-Member to Member
                    {
                     NonToMemAgain:
                     try
                       {

IBConvertIdTransaction->StartTransaction();
                        IBConvertQueryNonToMem->Open();
                       }
                     catch(...)
                       {
                        Randomize();
                        Rand = random(100);
                        Rand = 100 * Rand;
                        for (int i = 0; i <= Rand; i++)
                          Application->ProcessMessages();
                        IBConvertIdTransaction->Rollback();
                        goto NonToMemAgain; // Loop to start on
error
                       }
                     GotId =
IBConvertQueryNonToMem->FieldByName("GotId")->AsInteger;
                     IBConvertIdTransaction->Commit();
                     IBConvertQueryNonToMem->Close();
                     break;
                    }
       case  4 :    //Member to Non-Member
                    {
                     MemToNonAgain:
                     try
                       {

IBConvertIdTransaction->StartTransaction();
                        IBConvertQueryMemToNon->Open();
                       }
                     catch(...)
                       {
                        Randomize();
                        Rand = random(100);
                        Rand = 100 * Rand;
                        for (int i = 0; i <= Rand; i++)
                          Application->ProcessMessages();
                        IBConvertIdTransaction->Rollback();
                        goto MemToNonAgain; // Loop to start on
error
                       }
                     GotId =
IBConvertQueryMemToNon->FieldByName("GotId")->AsInteger;
                     IBConvertIdTransaction->Commit();
                     IBConvertQueryMemToNon->Close();
                     break;
                    }
      }

  //Check if all id numbers used
  if (GotId == -1)
    {
     String Header, WhichOne  = "Member";
     switch (Identity)
       {
        case     0 : Header = "Add New Member";
                     break;
        case     1 : Header = "Add New Member";
                     break;
        case     2 : {
                      Header = "Add New Non-Member";
                      WhichOne = "Non-Member";
                      break;
                     }
       }
     char MessageHeader[50] = {0};
     char Message[256] = {0};
     sprintf(Message, "No more %s Id numbers are available. The
attempt will be cancelled. Delete some older, inactive %s records.
", WhichOne.c_str(), WhichOne.c_str());
     sprintf(MessageHeader, "%s", Header.c_str());
     Application->MessageBox(Message, MessageHeader,
                             MB_OK | MB_ICONEXCLAMATION);
     return -1;
    }
  return GotId;
 }

Re:I say it CANNOT be done.


Quote
Josh Crane wrote:

> Sorry gents, but I still can't see a solution.

> Jeff, your example makes sense, but what component would let me do a
> dataset.append or dataset.insert without having assigned an update object or
> the InsertSQL property?

TIBTable will write the InsertSQL for you.  I'm not certain I am following your
question here though.

Quote
> If I use a TIBDataSet, QInsert has not been assigned, therefore I cannot
> insert. Knowing that I can't use the QInsert to return data, I discard the
> TIBDataSet and use a TIBQuery... it throws up "Cannot modify a read-only
> dataset.", so I add a TIBUpdateSQL, but can't get the output parameter from
> a procedure executed from within the InsertSQL property of the update
> object.

Yes you can just like I showed you in the example.  Add an OnUpdateRecord event,
call the IBUpdateSQL's InsertSQL itself, get the value back there and assign it
to the field's NewValue and set the action to uaApplied so that the IBQuery or
IBDataset will not call it again.
IBDataset also accepts UpdateObjects, but not at design time (I've published
this property in 4.4).  So at run time you can assign the TIBUpdateObject to the
IBDataset's UpdateObejct and treat it like an IBQuery.

Quote
> I just can't seem to get it right.

> I guess I need an explicit example of the following including object types
> IF it can actually be done?

> my criteria is
> - a simple insertable dataset returned from a storedprocedure...
> - when an insert is made, I need to return the newly inserted ID from within
> the called insertion stored procedure by using an output parameter

> Again, I appreciate your help. Thankyou.

I'll post something in the attachments group later today.

Quote
> Josh.

--
Jeff Overcash (TeamB)   | Talk about failure
(Please do not email    | To fall is not to fail
 me directly unless     | Failure isn't about falling down
 asked.  Thank You)     | Failure is staying down (Marillion)

Re:I say it CANNOT be done.


Hi:

   IBSQL->Params->Vars[0]->AsInteger = Key;

Re:I say it CANNOT be done.


Hi:

Quote
> I'm looking for IBUpdateSQL parameters please....

    IBUpdateSQL1->Query->ParamByName("MyParamName")->AsInteger =
value;

Re:I say it CANNOT be done.


Quote
Josh Crane wrote:

> Thanks again everyone, I'm getting there.

> How do I set parameters in a TIBUpdateSQL.InsertSQL.

If the Dataset's UpdateObject points to it you don't have to.

Quote
> Is there some
> properties or must I write the SQL dynamically?

I've posted an example for you in the attachments group.

Please watch your over quoting.  Thank you.

Quote
> Cheers, Josh.

--
Jeff Overcash (TeamB)   | Talk about failure
(Please do not email    | To fall is not to fail
 me directly unless     | Failure isn't about falling down
 asked.  Thank You)     | Failure is staying down (Marillion)
Go to page: [1] [2]

Other Threads