Find out the ID issued in a auto-increment field in Mysql

[OldSmelly]

Hello,

I have search but not found an answer to my problem.

I connect to a Mysql database and add some data to tables by means of a remote view. How do I know what ID has been given to my records since the field is and auto-increment field?

The database is a heavy used one so changes are that their is data entered at the same time as I do.

But I need the ID so I can fill releated tables, anyone got an idea how to do this ?

Thanks in advance

Hans

 

[Mike Lewis]

Hans,

Issue a REQUERY() on the view. Then just look in your ID field. That will contain the value that MySQL generated.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[OldSmelly]

I don't think that this will work because the vieuw is filles whith every data except the key field because that will be filled in by the mysql database. So how can you do a requery if you don't know the key field ?

Hans

 

[OldSmelly]

filles must be filled

 

[OldSmelly]

Hmm Why can't I edit my repsonse ?

When you do a requery() wihout the keyfield you will get all the data from that table into your vieuw !!! (as expected)

 

[Mike Lewis]

Hans,

Not sure I understand what you are saying. Just do the REQUERY(), then look at the contents of the ID field.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[OldSmelly]

What I mean is...

When you do a requety() just to get 1 record you need a keyfield to ensure you just get 1 record. And that keyfield is the ID and I don't know what that field is. When you do a requery() on the mysql table whitout a keyfield you just get all the records in that table.

Hope this will help to see my problem

 

[Mike Lewis]

Mike,

I recommend you generate the key in your front end with GUID().

I don't totally agree with you about generating the key in the front end, but I won't labour the point. You may be right.

But I definitely disagree about using a GUID. It means that your primary key will be 128 bits long, rather than, say, 16 bits, which might otherwise be more than adequate.

The longer the key, the bigger the indexes, so the more time it will take to retrieve nodes from the index, so the slower your queries. That performance hit will last throughout the life of the database, whereas the small extra cost of requerying the record after an insert is only paid at the time of the insert.

The IDENTITY (or AutoInc) attribute was invented to solve this problem, so why not use it. (I agree that GUIDs would have a place if you need to do replication.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[OldSmelly]

Soory to sound a bit harsh

But you all keep talking about requery, I CAN'T REQUERY because I have no KEY to ensure me that I get the correct record !!!!!!

And I can't do it on the frontend either because it's not my Mysql server !!!!!

 

[Mike Lewis]

Hans,

You're right. You did make that point earlier.

I guess what you need is the equivalent of SQL Server's @@INDENTY function. I don't know MySQL very well, but the documentation includes a mention of a function called LAST_INSERT_ID(), which looks like it could be the same thing (see

http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html)

The way it works in SQL Server is that you send the INSERT (either via SQLEXEC() or by saving a new record in a remote view). You then use SQLEXEC() to send SELECT @@IDENTITY. This gives you the required ID in a VFP cursor. I assume the technique is the same with MySQL.

However, it's important that both the INSERT and the query of the identity value are done on the same connection. So if you are using a remote view to do the INSERT, you will have to share the connection handle with the SQLEXEC.

Hope this makes sense.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[OldSmelly]

Sorry to come on a bit strong theire

Mike, that might be just the answer and I will give this a try in the morning (it's evening here in holland).

Thanks everone and I will post my results here

Hans

 

[Mike Lewis]

Hans,

No need to apologise. I shouldn't have gone off on the GUID side track.

Before you completely abandon the REQUERY() solution, could you make sure that it doesn't work.

I have just done the following:

1. Created a table on the server with an auto-inc primary key.

2. Created a remote view in VFP, based on this table.

3. Added six records to the view, then closed it. As expected, the records were all present on the serve, with primary keys 1 - 6.

4. Opened the view in VFP. Did a Browse. I could see the six records. I then did Ctrl+Y to append a new record. This showed the PK as 0 (which is expected).

5. I filled in the other fields, then moved the record pointer. This caused the new record to be sent to the server.

6. Finally, I issued REQUERY() from the command prompt. I immediately saw the 0 in the PK field change to 7.

I don't know whether this would work in all cases, but I believe is should. Perhaps you could do your own tests along the same lines.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Mike Lewis]

Mike,

but compared to the fields in the record, another 12 bytes is not really significant.

It's not the size of the record that's signficant. It's the size of the index.

Again, based on my (limited) experience of SQL Server, the smaller the index, the more index nodes you get in a page, so the more nodes you can access with each physical read, and the fewer reads needed to find a record. Keeping index keys small is one of the accepted ways of optmising a database.

Your requery in that example just pulled the entire table, it only looked like you got your new record appended to the existing set.

True. Or, to be more exact, it pulled the entire view. If the view wasn't parameterised, that would equate to the entire table. But in a well-designed client-server system, views would contain a relatively small number of records (perhaps only one, in this case).

Knowing the primary key value(s) is really important when you're doing a parent-child and especially parent-child-grandchild relation.

Of course. No disagreement there. (Personally, I wouldn't use remote views in that situation, but that's just my preference.)

Thanks for the link re GUID performance.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[OldSmelly]

Well that could be the answer to my problem I will ask the administrator of the website I he can put a new field in the database so that I can put my own key in theire --> Problem solved

I don't worry to much about database traffic because it is on a very fast machine and internet connection ....

Thanks again guys and I will keep you posted

 

[Mike Lewis]

But how would you ensure that the candidate key was unique? If it was generated on the client, how do you know that it doesn't clash with one that another client is generating at the same time?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Mike Lewis]

Mike,

So, if I've understood it right, the candidate key would be qualified in some way.

You mentioned customer numbers, but a better option might be, say, the user ID. If the candidate key consists of the user ID plus a sequence number, it would be unique across the table.

And presumably that means you need a mechanism for generating consecutive numbers on the client -- a table to hold the next available number, for example.

I can see how that would work, but I still think it would be easier to go down the REQUERY() route - provided of course that the view is paramterised to avoid the entire table coming down the network.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[pthalacker]

Old Smelly,

Would GetAutoIncValue() solve your problem?

pamela

 

[OldSmelly]

That only applies to VFP tables...

I have managed to get the webmaster to add some fields to the tables wich I will fill with a key so I can retrieve the correct record...

Hans