[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-win32
Subject:    Re: Delphi and mySQL
From:       "Fredrick Bartlett" <palmtreefrb () earthlink ! net>
Date:       2004-07-28 15:21:31
Message-ID: 001d01c474b6$8ffa1e20$3c526e45 () REGITLAP
[Download RAW message or body]

Yeah, doesn't make sense but you have to...
select Field1 from customers where Field1= last_insert_id()

Where Field1 is your auto_increment field.

Fredrick

----- Original Message ----- 
From: <ian_t_howarth@tiscali.co.uk>
To: <win32@lists.mysql.com>
Sent: Wednesday, July 28, 2004 2:35 AM
Subject: Delphi and mySQL


Anyone else out there use mySQL and Delphi?

I am using Delphi 5 with mySQL 4.1.2 server and ODBC 3.51 clients. I am
performing insertions, using a TQuery component, into a table with an
auto_increment
primary key. (Table & sample source at end of this message)

When I run the following queries I do not get a result back to identify
the server allocated auto_incremented field.

1.
INSERT INTO customers
SET Status=1, Surname="Howarth", Forename="Ian", Title="Mr",
BuildingName="", BuildingNo="12", Road="A Road", Town="Atown",
County="Acounty", PostCode="PC0 0ES";

2.
SELECT Last_Insert_ID();

No exception is raised when the 2nd query is executed.

I have also tried SELECT Last_Insert_ID(ID); but get the error "Invalid
field name. Unknown column "ID" in field list." even tho the auto_increment
field is actually called ID!  I changed the field name from Ref to ID just
in case it was a special name,  but neither name works.

Any ideas? Could it be a problem with the client drivers?  I can't seem
to find a later one than 3.51 available.  PLEASE help. This problem has
been driving me insane!

Additional details follow:
--------------------------

CREATE TABLE `customers` (
  `ID` int(11) AUTO_INCREMENT,
  `Status` tinyint(4) default NULL,
  `Surname` varchar(30) default '',
  `Forename` varchar(30) default '',
  `Title` varchar(10) default '',
  `BuildingName` varchar(30) default '',
  `BuildingNo` varchar(10) default '',
  `Road` varchar(30) default '',
  `Area` varchar(30) default '',
  `Town` varchar(30) default '',
  `County` varchar(30) default '',
  `PostCode` varchar(10) default '',
  `HomeNo` varchar(20) default '',
  `MobileNo` varchar(20) default '',
  `WorkNo` varchar(20) default '',
  `EMailAddrs` varchar(100) default '',
  `DOB` date default '0000-00-00',
  `DOD` date default '0000-00-00 00:00:00',
  `NINo` varchar(13) default '',
  `PassportNo` varchar(20) default '',
  `UnAvailStart` date default '0000-00-00 00:00:00',
  `UnAvailEnd` date default '0000-00-00 00:00:00',
  `UnavailReason` varchar(128) default NULL,
  `Notes` text,
  `Created` datetime default '0000-00-00 00:00:00',
  `CreatedBy` varchar(20) default '',
  `Updated` datetime default '0000-00-00 00:00:00',
  `UpdatedBy` varchar(20) default '',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Code (DELPHI):
--------------

// Form contains a TQuery, TDatabase, TDataSource, TMemo and
// TDBGrid components.  The DBGrid is linked to the TQuery and
// is just to allow me to see the result set from the
// SELECT last_insert_id() query.

procedure TForm1.Button1Cick(Sender: TObject);

begin
  Query1.SQL.Text:=Memo1.Lines.Text;
  Query1.Prepare;
  Query1.ExecSQL;

  Query1.SQL.Text:='SELECT Last_Insert_ID();';
  Query1.Prepare;
  Query1.ExecSQL;
  If Query1.IsEmpty Then
     ShowMessage('No result.')
  Else
     ShowMessage(Query1.Fields[0].AsString);
end;


__________________________________________________
Broadband from an unbeatable £15.99!

http://www.tiscali.co.uk/products/broadband/home.html?code=SM-NL-11AM




-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=palmtreeFRB@earthlink.net


-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/win32?unsub=mysql-win32@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]