🚀 go-pugleaf

Hi,

Unfortunately this problem is still not solved since the middle of 2001
in DBExpress. In MySQL theoretically(!) you could use the 
LAST_INSERT_ID() function after an INSERT statement. However, because of
a bug in DBExpress which exists since a looooong time this will not
work. DBExpress opens a NEW connection to the MySQL database for
each end every SQL statement and you cannot execute two SQL
instructions at once.
The only way is to read back the record you just inserted somehow and
get the auto increment field value from there.

Also, be careful because the
"select ... from ... order by ... limit ..." type SQL statements will
not work either (can be fixed though by editing DBCommon.pas) because
DBExpress tries to process the "LIMIT ..." part as a field list in the
"ORDER BY" part.

Also, you cannot specify other ports than 3306 for MySQL connections.

These are the 3 bugs I encountered so far. There are some reports about
memory leaks too if you check these newsgroups...



Suddn wrote:
> I'm not sure which news group would be the best one for this question but 
> here it is:
> 
> When I add a record to a database that uses an auto increment primary key, 
> how do I get the value of the primary key to the record I just added.
> 
> For example, let's say I have the table CUSTOMER it has three fields: id, 
> fname, lname.
> 
> The id field is the PK and is auto incremented.  So I add the record 
> fname="John", lname="Smith".  I don't accually add the value of the PK 
> because it is incremented automaticaly.  Now say that I need to Use the 
> primary key to the John Smith's record to associate this record with 
> something else.  I don't have the PK and I can't reliably search for John 
> and Smith as that could return multiple records.  I can't just get the MAX
> value because in a multi user environment more records may have been added
> right after I created my record.
>  
> I'm using Mandrake 9.1 rc1, MySQL and the dbexpress TClientDataSet component from Kylix 3.
> 
> Thanks.
> 

Arrrrrgggghhhhh!!

This is a SHOW STOPPER!!!


On Mon, 24 Feb 2003 01:04:50 -0800, BoBo wrote:

> Hi,
> 
> Unfortunately this problem is still not solved since the middle of 2001
> in DBExpress. In MySQL theoretically(!) you could use the 
> LAST_INSERT_ID() function after an INSERT statement. However, because of
> a bug in DBExpress which exists since a looooong time this will not
> work. DBExpress opens a NEW connection to the MySQL database for
> each end every SQL statement and you cannot execute two SQL
> instructions at once.
> The only way is to read back the record you just inserted somehow and
> get the auto increment field value from there.
> 
> Also, be careful because the
> "select ... from ... order by ... limit ..." type SQL statements will
> not work either (can be fixed though by editing DBCommon.pas) because
> DBExpress tries to process the "LIMIT ..." part as a field list in the
> "ORDER BY" part.
> 
> Also, you cannot specify other ports than 3306 for MySQL connections.
> 
> These are the 3 bugs I encountered so far. There are some reports about
> memory leaks too if you check these newsgroups...
> 
> 
> 
> Suddn wrote:
>> I'm not sure which news group would be the best one for this question but 
>> here it is:
>> 
>> When I add a record to a database that uses an auto increment primary key, 
>> how do I get the value of the primary key to the record I just added.
>> 
>> For example, let's say I have the table CUSTOMER it has three fields: id, 
>> fname, lname.
>> 
>> The id field is the PK and is auto incremented.  So I add the record 
>> fname="John", lname="Smith".  I don't accually add the value of the PK 
>> because it is incremented automaticaly.  Now say that I need to Use the 
>> primary key to the John Smith's record to associate this record with 
>> something else.  I don't have the PK and I can't reliably search for John 
>> and Smith as that could return multiple records.  I can't just get the MAX
>> value because in a multi user environment more records may have been added
>> right after I created my record.
>>  
>> I'm using Mandrake 9.1 rc1, MySQL and the dbexpress TClientDataSet component from Kylix 3.
>> 
>> Thanks.
>>

Are you sure about that connection thing?  I was looking at the properties
of the TSQLConnection component and it has a "KeepConnection" property.

The docs say:


Description

Use KeepConnection to specify whether TSQLConnection maintains an open
database connection even if no associated dataset components are currently
active.

When KeepConnection is true (the default) the connection is maintained. 
For connections to remote database servers, or in applications that 
frequently open and close datasets, set KeepConnection to true to reduce 
network traffic, speed up applications, and avoid logging in to the server
each time the connection is reestablished.

When KeepConnection is false a connection is dropped when there are no 
open datasets. Dropping a connection releases system resources allocated 
to the connection, but if a dataset is later opened that uses the
database, the connection must be reestablished and initialized.


On Mon, 24 Feb 2003 01:04:50 -0800, BoBo wrote:

> Hi,
> 
> Unfortunately this problem is still not solved since the middle of 2001
> in DBExpress. In MySQL theoretically(!) you could use the 
> LAST_INSERT_ID() function after an INSERT statement. However, because of
> a bug in DBExpress which exists since a looooong time this will not
> work. DBExpress opens a NEW connection to the MySQL database for
> each end every SQL statement and you cannot execute two SQL
> instructions at once.
> The only way is to read back the record you just inserted somehow and
> get the auto increment field value from there.
> 
> Also, be careful because the
> "select ... from ... order by ... limit ..." type SQL statements will
> not work either (can be fixed though by editing DBCommon.pas) because
> DBExpress tries to process the "LIMIT ..." part as a field list in the
> "ORDER BY" part.
> 
> Also, you cannot specify other ports than 3306 for MySQL connections.
> 
> These are the 3 bugs I encountered so far. There are some reports about
> memory leaks too if you check these newsgroups...
> 
> 
> 
> Suddn wrote:
>> I'm not sure which news group would be the best one for this question but 
>> here it is:
>> 
>> When I add a record to a database that uses an auto increment primary key, 
>> how do I get the value of the primary key to the record I just added.
>> 
>> For example, let's say I have the table CUSTOMER it has three fields: id, 
>> fname, lname.
>> 
>> The id field is the PK and is auto incremented.  So I add the record 
>> fname="John", lname="Smith".  I don't accually add the value of the PK 
>> because it is incremented automaticaly.  Now say that I need to Use the 
>> primary key to the John Smith's record to associate this record with 
>> something else.  I don't have the PK and I can't reliably search for John 
>> and Smith as that could return multiple records.  I can't just get the MAX
>> value because in a multi user environment more records may have been added
>> right after I created my record.
>>  
>> I'm using Mandrake 9.1 rc1, MySQL and the dbexpress TClientDataSet component from Kylix 3.
>> 
>> Thanks.
>>

We have no problem with this. When we researched this out over a year ago, a 
MySQL team member told us that MySQL had a problem with getting the last 
insert ID if there are active queries on a connection. In other words, 
unless MySQL has changed since I reseached the problem, this is a MySQL 
limitation. I don't know the status of the issue today since we have a 
workaround that works for us.

The workaround that we've used successfully since is to use a clean (new) 
connection for the insert and 'select last_insert_id()'. I know some people 
complain about lack of performance, but we have seen no problems with this.

While the MySQL drivers aren't perfect (I've yet to find a perfect 
development tool anywhere), we use DUnit to stress test the database and 
application and have had no MySQL problems in Kylix 3 so far.

To date we've been *very* pleased with Kylix and Linux having fewer 
fundamental problems during regression testing compared to the identical 
cross-platform code running on Delphi and Windows. This is mainly due to 
Windows acting up; Delphi has had its quirks, but has been reliable.

(Sorry for the long post; it's somewhat a reaction against the frustration 
vented by other people.)

This is not a MySQL limitation, because with PHPMyAdmin it is working
properly as well as in the mysql console application.
If you run the following query from PHPMyAdmin as a single SQL query:

insert into table .... ; select LAST_INSERT_ID();

then it is returning with the right value always.
In Kylix you cannot even execute this as one query (only one SQL
statement can be executed at one time).
If you run it as two queries then the result is always "0"

SQLConnection.Open;
SQL1.SQL := 'insert ....';
SQL1.Execute;
SQL1.SQL := 'select LAST_INSERT_ID()';
SQL1.Open;
//...  SQL1.Fields[0].AsInteger contains the value of "0" always!
SQLConnection.Close;


So Kylix must do it with two connections otherwise it would work.

If you have solved this problem without reconnecting to the
database server then please let us know.

BTW, I forgot to mention that I'am writing Apache DSO modules
where this problem surely exists. Maybe it is working properly
with desktop GUI applications.




Rithban wrote:
> We have no problem with this. When we researched this out over a year 
> ago, a MySQL team member told us that MySQL had a problem with getting 
> the last insert ID if there are active queries on a connection. In other 
> words, unless MySQL has changed since I reseached the problem, this is a 
> MySQL limitation. I don't know the status of the issue today since we 
> have a workaround that works for us.
> 
> The workaround that we've used successfully since is to use a clean 
> (new) connection for the insert and 'select last_insert_id()'. I know 
> some people complain about lack of performance, but we have seen no 
> problems with this.
> 
> While the MySQL drivers aren't perfect (I've yet to find a perfect 
> development tool anywhere), we use DUnit to stress test the database and 
> application and have had no MySQL problems in Kylix 3 so far.
> 
> To date we've been *very* pleased with Kylix and Linux having fewer 
> fundamental problems during regression testing compared to the identical 
> cross-platform code running on Delphi and Windows. This is mainly due to 
> Windows acting up; Delphi has had its quirks, but has been reliable.
> 
> (Sorry for the long post; it's somewhat a reaction against the 
> frustration vented by other people.)
> 

In my Apache DSO modules this problem exists for sure.
Maybe in other applications it is working properly (GUI, Console, etc.).


Suddn wrote:
> Are you sure about that connection thing?  I was looking at the properties
> of the TSQLConnection component and it has a "KeepConnection" property.
> 
> The docs say:
> 
> 
> Description
> 
> Use KeepConnection to specify whether TSQLConnection maintains an open
> database connection even if no associated dataset components are currently
> active.
> 
> When KeepConnection is true (the default) the connection is maintained. 
> For connections to remote database servers, or in applications that 
> frequently open and close datasets, set KeepConnection to true to reduce 
> network traffic, speed up applications, and avoid logging in to the server
> each time the connection is reestablished.
> 
> When KeepConnection is false a connection is dropped when there are no 
> open datasets. Dropping a connection releases system resources allocated 
> to the connection, but if a dataset is later opened that uses the
> database, the connection must be reestablished and initialized.
> 
> 
> On Mon, 24 Feb 2003 01:04:50 -0800, BoBo wrote:
> 
> 
>>Hi,
>>
>>Unfortunately this problem is still not solved since the middle of 2001
>>in DBExpress. In MySQL theoretically(!) you could use the 
>>LAST_INSERT_ID() function after an INSERT statement. However, because of
>>a bug in DBExpress which exists since a looooong time this will not
>>work. DBExpress opens a NEW connection to the MySQL database for
>>each end every SQL statement and you cannot execute two SQL
>>instructions at once.
>>The only way is to read back the record you just inserted somehow and
>>get the auto increment field value from there.
>>
>>Also, be careful because the
>>"select ... from ... order by ... limit ..." type SQL statements will
>>not work either (can be fixed though by editing DBCommon.pas) because
>>DBExpress tries to process the "LIMIT ..." part as a field list in the
>>"ORDER BY" part.
>>
>>Also, you cannot specify other ports than 3306 for MySQL connections.
>>
>>These are the 3 bugs I encountered so far. There are some reports about
>>memory leaks too if you check these newsgroups...
>>
>>
>>
>>Suddn wrote:
>>
>>>I'm not sure which news group would be the best one for this question but 
>>>here it is:
>>>
>>>When I add a record to a database that uses an auto increment primary key, 
>>>how do I get the value of the primary key to the record I just added.
>>>
>>>For example, let's say I have the table CUSTOMER it has three fields: id, 
>>>fname, lname.
>>>
>>>The id field is the PK and is auto incremented.  So I add the record 
>>>fname="John", lname="Smith".  I don't accually add the value of the PK 
>>>because it is incremented automaticaly.  Now say that I need to Use the 
>>>primary key to the John Smith's record to associate this record with 
>>>something else.  I don't have the PK and I can't reliably search for John 
>>>and Smith as that could return multiple records.  I can't just get the MAX
>>>value because in a multi user environment more records may have been added
>>>right after I created my record.
>>> 
>>>I'm using Mandrake 9.1 rc1, MySQL and the dbexpress TClientDataSet component from Kylix 3.
>>>
>>>Thanks.
>>>
> 
> 

1. For the last_insert_id() bug a workaround is
to close the SQLConnection before executing the
insert statement. Then it will be automatically
reopened and the second SQL statement with the
last_insert_id() will work properly.

ex:
.
.
.
SQLConnection.Open;
SQL.CommandText ...
. //some SQL statements executed, etc.
.
.

//Close the connection before the insert
SQLConnection.Close;

//insert the record with the auto-increment field
SQL.CommandText := 'insert into .....';
SQL.Execute;

//Get the last autoinc value
SQL.CommandText := 'select LAST_INSERT_ID()';
SQL.Open;
LastInsertedID := SQL.Fields[0].AsInteger;
.
.
.


2. For the MySQL GROUP BY ... LIMIT ... bug
edit DBCommon.pas (need Kylix Enterprise Edition to have
the source code) in
function GetIndexForOrderBy(const SQL: string; DataSet: TDataSet): 
TIndexDef;
Do this only if you use MySQL and you do not use a field
called "limit" in your tables! :)
.
.
.
       repeat
         SQLToken := NextSQLToken(Start, Token, CurSection);
         if SQLToken in SQLSections then
           CurSection := SQLToken else
           case SQLToken of
             stTableName: ;
             stFieldName:
             begin
               LastField := Token;
//Start of fix for MySQL LIMIT command
               if UpperCase(LastField) = 'LIMIT' then Break;
//End of fix for MySQL LIMIT command
               { Verify that we parsed a valid field name, not something 
like "UPPER(Foo)" }
               if not Assigned(Dataset.FindField(LastField)) then continue;
               Result.Fields := AddField(Result.Fields, LastField);
               SaveField := LastField;
             end;
             stAscending: ;
             stDescending:
               Result.DescFields := AddField(Result.DescFields, SaveField);
             stNumber:
.
.
.

BoBo wrote:
> In my Apache DSO modules this problem exists for sure.
> Maybe in other applications it is working properly (GUI, Console, etc.).
> 
> 
> Suddn wrote:
> 
>> Are you sure about that connection thing?  I was looking at the 
>> properties
>> of the TSQLConnection component and it has a "KeepConnection" property.
>>
>> The docs say:
>>
>>
>> Description
>>
>> Use KeepConnection to specify whether TSQLConnection maintains an open
>> database connection even if no associated dataset components are 
>> currently
>> active.
>>
>> When KeepConnection is true (the default) the connection is 
>> maintained. For connections to remote database servers, or in 
>> applications that frequently open and close datasets, set 
>> KeepConnection to true to reduce network traffic, speed up 
>> applications, and avoid logging in to the server
>> each time the connection is reestablished.
>>
>> When KeepConnection is false a connection is dropped when there are no 
>> open datasets. Dropping a connection releases system resources 
>> allocated to the connection, but if a dataset is later opened that 
>> uses the
>> database, the connection must be reestablished and initialized.
>>
>>
>> On Mon, 24 Feb 2003 01:04:50 -0800, BoBo wrote:
>>
>>
>>> Hi,
>>>
>>> Unfortunately this problem is still not solved since the middle of 2001
>>> in DBExpress. In MySQL theoretically(!) you could use the 
>>> LAST_INSERT_ID() function after an INSERT statement. However, because of
>>> a bug in DBExpress which exists since a looooong time this will not
>>> work. DBExpress opens a NEW connection to the MySQL database for
>>> each end every SQL statement and you cannot execute two SQL
>>> instructions at once.
>>> The only way is to read back the record you just inserted somehow and
>>> get the auto increment field value from there.
>>>
>>> Also, be careful because the
>>> "select ... from ... order by ... limit ..." type SQL statements will
>>> not work either (can be fixed though by editing DBCommon.pas) because
>>> DBExpress tries to process the "LIMIT ..." part as a field list in the
>>> "ORDER BY" part.
>>>
>>> Also, you cannot specify other ports than 3306 for MySQL connections.
>>>
>>> These are the 3 bugs I encountered so far. There are some reports about
>>> memory leaks too if you check these newsgroups...
>>>
>>>
>>>
>>> Suddn wrote:
>>>
>>>> I'm not sure which news group would be the best one for this 
>>>> question but here it is:
>>>>
>>>> When I add a record to a database that uses an auto increment 
>>>> primary key, how do I get the value of the primary key to the record 
>>>> I just added.
>>>>
>>>> For example, let's say I have the table CUSTOMER it has three 
>>>> fields: id, fname, lname.
>>>>
>>>> The id field is the PK and is auto incremented.  So I add the record 
>>>> fname="John", lname="Smith".  I don't accually add the value of the 
>>>> PK because it is incremented automaticaly.  Now say that I need to 
>>>> Use the primary key to the John Smith's record to associate this 
>>>> record with something else.  I don't have the PK and I can't 
>>>> reliably search for John and Smith as that could return multiple 
>>>> records.  I can't just get the MAX
>>>> value because in a multi user environment more records may have been 
>>>> added
>>>> right after I created my record.
>>>>
>>>> I'm using Mandrake 9.1 rc1, MySQL and the dbexpress TClientDataSet 
>>>> component from Kylix 3.
>>>>
>>>> Thanks.
>>>>
>>
>>
>