----- Original Message ----- From: <ian_...@tiscali.co.uk> To: <win...@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