last_insert_id()

[sircalleba66]

Hello

How can i get tha last insert id.
my Code like this.

SQLSetprop(_Screen.oApp.nConnectionHandle, 'Transactions', 2)
TEXT to lcSQL noShow
INSERT INTO customer(FirstName,LastName,Address,Phone,Email,CreditLimit,Company,CompanyAdd,CompanyPhone)
VALUES (?.lcFname, ?.lcLname,?.lcAddr,?.lcPhone, ?.lcEmail,?.lnCreditLimit,?.lcCompany,?.lcCompAddr,?.lcCompPhone)
ENDTEXT

 

[Mike Lewis]

This depends on which back-end database you are using.

If you are using Microsoft SQL Server, then SCOPE_IDENTITY() is usually the best choice, but you could also use @@IDENTITY or IDENT_CURRENT(), depending on the circumstaces. This article explains it better than I can.

If you are using a different back-end, tell us which one it is, and someone here will probably have the answer.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

What Database do you adress? MySQL?

SELECT LAST_INSERT_ID(). Put that right after the insert and you will insert and get back the inserted id in one go:

TEXT to lcSQL noShow
INSERT INTO customer(FirstName,LastName,Address,Phone,Email,CreditLimit,Company,CompanyAdd,CompanyPhone)
VALUES (?.lcFname, ?.lcLname,?.lcAddr,?.lcPhone, ?.lcEmail,?.lnCreditLimit,?.lcCompany,?.lcCompAddr,?.lcCompPhone);

SELECT LAST_INSERT_ID() as ID;
ENDTEXT

SqlExec(_Screen.oApp.nConnectionHandle,"curLast")
? curLast.ID

If you ask for DBFs:
Select yourtable (not SQL, select in the sense of choice, make it the active table you can read from)
Insert Into yourtable(...)
? yourtable.ID

Bye, Olaf.

 

[sircalleba66]

Thanks for your quick reply.
BTW I am using MySQL as backend.

 

[Olaf Doschke]

Ok, then does it work?

Another thing I see is you're using parameters like "?.lcFname". That's OK for use within a WITH .. ENDWITH block for an object havin lcFName as property and other properties. In regard of naming convention recommendations, properties would rather not have the l as local prefix and if you SCATTER NAME to a record object you also would simply have the normal field names and in MySQL you rather would not prefix them at all.

If you really mean variable names, that go by that naming convention, you have to remove all the dots or add m in front as in ?lcFname or ?m.lcFName.

Bye, Olaf.

 

[sircalleba66]

Sir I tried you code but I've got an error alias 'curlast' not found.

 

[Mike Lewis]

OK, now we know you're using MySQL, I can tell you that the suggestions I made won't apply. But Olaf's code should work fine, as far as I can see.

Unfortunately, there's no standard way of getting the last inserted ID that works for all databases (as far as I know). It seems to be something that's missing from the ANSI standard. One solution might be to query the highest ID immediately after you have inserted the new row, but you can't guarantee that another user hasn't done another insert in the meantime.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[sircalleba66]

Actually Sir Mike, I am new to MySQL and I know I'll have to dig deeper into MySQL and I thought using MySQL as back end is not complicated.

Thanks you very much.

 

[Mike Lewis]

Sir I tried you code but I've got an error alias 'curlast' not found.

Yes, there is a small error in Olaf's code, although it's got nothing to do with MySQL. The problem is in the SQLEXEC(). Instead of this:

SqlExec(_Screen.oApp.nConnectionHandle,"curLast")

you need this:

SqlExec(_Screen.oApp.nConnectionHandle, [b]lcSQL[/b], "curLast")

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[sircalleba66]

Thanks Sir It works. BTW Sir when to issue a SQLDisconnect command, after closing the form?

 

[Mike Lewis]

You use SQLDISCONNECT() when you have finished with the connection.

With some back ends, there is a low limit on the number of open connections that are permitted at a time, so you want to close the connection as soon as possible after finishing with it. But there is also an overhead in constantly opening and closing connection. So you have a trade-off. I don't know what the best policy is for MySQL.

Where there is no significant cost in keeping the connection open, no great harm will be done if you don't call SQLDISCONNECT(), especially give that the connection will be closed automatically when the application exists. That said, it's probably better always to call it, if only for the sake of tidiness.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

If you have additional operations to perform in the MySQL database, you can continue to use the same ConnecthionHandle as long as you have not issued the SQLDisconnect(0).

But if you do not have anything more to do in MySQL, you can issue the SQLDISCONNECT(0) as soon as you have the results you need.

While it does not need to be done sooner, it also does not have to wait until you close the Form.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Thanks Mike, for correcting my SQLExec(), missed that.

Bye, Olaf.