Loosing AutoInc Value when Using "Order By" on a complex querie

Loosing AutoInc Value when Using "Order By" on a complex querie

Postby Guest » Fri 14 Jan 2005 10:20

Hello,

I finally found "my bug".
When using complex queries and "Order by" keyword, the AutoInc key isn't retreive. I have test this behaviour on MySQL 4.0.x and MySQL 4.1.x

Just test this simple query (You must have an AutoInc field on the 1st table)

Select
Table1.*,
Table2.Name,
Table3.Rate
From
Table1
INNER JOIN Table2 on (Table1.Id = Table2 .Id)
LEFT OUTER JOIN Table3 ON (Table2.Id = Table3 .Id)
Order by
Table1.Id


When inserting a new record, and after posting , I can't see the new created ID number. I have to refresh the dataset to get it (not useful)

When I remove the Order by clause, I can see the new ID after posting.

Is this a MySQL issue , or an component limitation ?
Is there a solution ?

Thank you and keep going on with your wonderfull product
Guest
 

Re: Loosing AutoInc Value when Using "Order By" on a complex querie

Postby Ikar » Mon 17 Jan 2005 16:15

It is a restriction of MySQL. To avoid it use instead JOIN WHERE.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby Guest » Tue 18 Jan 2005 12:32

Humm. I have more than 100 queries using the join keyword.
I can't afford to rewrite and re-test all of them.

When calling the refreshrecord method, I can get the auto inc field !. So, i'm using it in the after insert statement (it is not really "clean" but it must works )
Guest
 

Postby Ikar » Wed 19 Jan 2005 08:49

You are right, it is not the best solution. But on using JOIN values of autoinc-fields aren't retrieved from the server.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56


Return to MySQL Data Access Components