[Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)

[silvioprog]

On Fri, Nov 14, 2014 at 3:23 PM, Michael Van Canneyt <michael at freepascal.org
> wrote:
>
> On Fri, 14 Nov 2014, Reimar Grabowski wrote:
>
>> On Fri, 14 Nov 2014 14:50:20 +0100
>> Reimar Grabowski <reimgrab at web.de> wrote:
>>
>>  On Fri, 14 Nov 2014 14:15:08 +0100 (CET)
>>> Michael Van Canneyt <michael at freepascal.org> wrote:
>>>
>>>  Connections for databases that have lastinsertID you can call the
>>>> method created for this:
>>>>
>>>> Function GetInsertID: int64;
>>>>
>>> Unfortunately it does not work and returns only 0, while my custom query
>> using LAST_INSERT_ID() works as expected.
>> Debugging this is very hard as Lazarus crashes on me left and right when
>> trying to step through the code and an exception is encountered.
>> I have one connection, one transaction and some queries bound to them on
>> a FPWebModule.
>>
>> ID1:=MySQL55Connection1.GetInsertID;
>> SQLQuery1.Open;
>> ID2:=SQLQuery1.FieldByName('LastInsertID').AsLargeInt;
>>
>> ID1 is 0 and ID2 the correct id value.
>>
>> Any ideas what I could be doing wrong?
>>
>
> This is what it does:
>
> function TConnectionName.GetInsertID: Int64;
> begin
>   CheckConnected;
>   Result:=mysql_insert_id(GetHandle);
> end;
>
> a) Did you do an insert right before the call to GetInsertID ?
> b) Is the transaction active after the insert ? c) The connection
> transaction should be the same as the transaction of the insert.


Work fine here (MySQL 5.5 32 bits / Lazarus 1.2.6 r46529 FPC 2.6.4
i386-win32-win32/win64 / Windows 7 64 bits):

unit1.lfm:

object Form1: TForm1
  Left = 238
  Height = 240
  Top = 144
  Width = 320
  Caption = 'Form1'
  ClientHeight = 240
  ClientWidth = 320
  LCLVersion = '1.2.6.0'
  object Button1: TButton
    Left = 10
    Height = 25
    Top = 8
    Width = 75
    Caption = 'Button1'
    OnClick = Button1Click
    TabOrder = 0
  end
  object MySQL55Connection1: TMySQL55Connection
    Connected = False
    LoginPrompt = False
    DatabaseName = 'test'
    KeepConnection = False
    Password = 'root'
    Transaction = SQLTransaction1
    UserName = 'root'
    HostName = '127.0.0.1'
    LogEvents = []
    left = 40
    top = 16
  end
  object SQLQuery1: TSQLQuery
    FieldDefs = <>
    Database = MySQL55Connection1
    Transaction = SQLTransaction1
    Params = <>
    left = 88
    top = 116
  end
  object SQLTransaction1: TSQLTransaction
    Active = False
    Database = MySQL55Connection1
    left = 147
    top = 45
  end
end

...

unit1.pas:

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  SysUtils, mysql55conn, sqldb, Forms, Dialogs, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    MySQL55Connection1: TMySQL55Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.SQL.Text := 'insert into test (foo) values (:foo)';
  SQLQuery1.Params.ParamByName('foo').AsString := DateTimeToStr(Now);
  SQLQuery1.ExecSQL;
  ShowMessageFmt('%d', [MySQL55Connection1.GetInsertID]);
end;

end.

Result after three clicks:

[Window Title]
project1

[Content]
4

[OK]

SQL:

-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: 14-Nov-2014 às 17:09
-- Versão do servidor: 5.5.40
-- PHP Version: 5.3.21

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `test`
--

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

--
-- Estrutura da tabela `test`
--

CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL,
  `foo` varchar(20) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `test`
--
ALTER TABLE `test`
 ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `test`
--
ALTER TABLE `test`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

-- 
Silvio Clécio
My public projects - github.com/silvioprog
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20141114/2ddceb6c/attachment-0003.html>