DisconnectedMode and Last_Insert_ID()

[penreiter]

Hi,
with the Mydac components 5.00.0.5 for D5 I now have an new effect.
Using DisconnectedMode=true, will result in an value of 0 when requesting the "last_insert_ID()".

What can I do to use the new feature and work in the same way with my select as before.

Thanks in advance for helping

best regards

Peter

[Antaeus]

I can suggest you three ways for that:
1) Open and close the connection explicitly.
2) Make a stored procedure that takes values of a newly inserted record, performs insert operation, requests the LAST_INSERT_ID() value, and returns it as an output parameter. Assign a command to call the procedure to the SQLInsert property of the component that displays data.
3) Use the TMyUpdateSQL component with a linked object for executing INSERT commands and requesting the LAST_INSERT_ID() value. Below is an example. Just copy the following code, open a new form in the IDE designer, and paste this code (Ctrl+V):

Code: Select all

  object MyUpdateSQL1: TMyUpdateSQL
    InsertObject = MyQuery2
    Left = 190
    Top = 45
  end
  object MyQuery1: TMyQuery
    Connection = MyConnection1
    SQL.Strings = (
      'SELECT * FROM dept')
    UpdateObject = MyUpdateSQL1
    Left = 150
    Top = 45
  end
  object MyConnection1: TMyConnection
    Left = 105
    Top = 45
  end
  object MyQuery2: TMyQuery
    Connection = MyConnection1
    SQL.Strings = (
      'INSERT INTO dept'
      '  (DEPTNO, DNAME, LOC)'
      'VALUES'
      '  (:DEPTNO, :DNAME, :LOC);'
      'SELECT LAST_INSERT_ID();')
    AfterOpen = MyQuery2AfterOpen
    Left = 235
    Top = 45
    ParamData = 
  end
  object DataSource1: TDataSource
    DataSet = MyQuery1
    Left = 145
    Top = 95
  end

Add something like this in the AfterOpen event of MyQuery2:

Code: Select all

ShowMessage('Last insert id: ' + MyQuery2.Fields[0].AsString);