MySQL ODBC AddRow from LotusScript Vs Auto-increment field

HCL Domino Domino Forum
1

Hello,

I have a LotusScript agent which write data to a back-end MySQL database. Everything is working fine.

Here’s the code:

'Access ODBC data source

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim result As New ODBCResultSet

If con.ConnectTo(“data source”, “login”, “password”) Then

'Access MySQL data

Set qry.Connection = con

Set result.Query = qry

qry.SQL = “select * from accounts”

result.Execute

result.LastRow

'Add a new row (a new account)

result.AddRow

'Call result.SetValue(“AccountUnq”, 0)

Call result.SetValue(“Login”, “test”)

Call result.SetValue(“Password”, “test”)

Call result.SetValue(“AddDate”, Now)

Call result.SetValue(“HomeDomain”, 1)

Call result.SetValue(“IgnoreGlobal”, “”)

Call result.SetValue(“Authenticated”, “T”)

Call result.SetValue(“AuthID”, 0)

'Write the new data into the MySQL db

result.UpdateRow

Dim AccountUnq As Integer

Call result.GetValue(“AccountUnq”, AccountUnq)

Print AccountUnq

result.Close(DB_CLOSE)

con.Disconnect

End If

Notice that in the code, I have the AccountUnq field which is an auto-increment integer in MySQL. I’m not giving a value to this field, since MySQL is gonna assign one when I update the row.

However, I was wondering how to get that number?

I tried this:

Dim AccountUnq As Integer

Call result.GetValue(“AccountUnq”, AccountUnq)

Print AccountUnq

But it always prints “0”.

In the Notes help, they are saying this:

“UpdateRow leaves you on the current row. If you perform an AddRow followed by an UpdateRow, you remain on the added row.”

So that means I’m at the right place.

I know I could do the same SQL Query again, and then access the LastRow, and then it works.

But I’m afraid some other rows might have been added by then… and I’m sure there’s a better way to do this.

Any idea?

Thanks!

Pascal

2

Subject: MySQL ODBC AddRow from LotusScript Vs Auto-increment field

Also, just a thought…

why not add the new record then pick up the number of the record before it. You only need to increment this by 1 to end up with the number added to the new record.

Thanks

3

Subject: MySQL ODBC AddRow from LotusScript Vs Auto-increment field

UpdateRow inserts your changes into the database but it does not read the record back, so AccountUnq will still not be set. Your only real option is to query the database using the key of the record you just added and get its AccountUnq.

The suggestions of using Bookmark or AbsolutePosition are referring to ADO, which wouldn’t be of much help in your scenario. Those only apply if the data has not changed since you did your addition, and you said other records could have been added or deleted so any pointers would be invalid. You would still have to requery the data to find the row and retrieve the autoincrement value.

4

Subject: RE: MySQL ODBC AddRow from LotusScript Vs Auto-increment field

“Your only real option is to query the database using the key of the record you just added”- Could you just give me more details on how to do this? Do you mean doing a SQL Query again? Or there’s a way to access directly the row that was just added?

If I use this after my UpdateRow…

	qry.SQL = "select AccountUnq from accounts where Login='test'"

	result.Execute

	Dim AccountUnq As Integer

	Call result.GetValue("AccountUnq", AccountUnq)

	Print AccountUnq

… it’s working.

But I was wondering if there’s a better way to achieve this?

Thanks!

Pascal

5

Subject: RE: MySQL ODBC AddRow from LotusScript Vs Auto-increment field

That’s what I meant. It’s your most reliable option, anything else could get out of sync. You could try moving to the previous row, then doing a move next, but as you said, other rows could have been added or deleted. You’re better off just using a query because it will work consistently and reliably.

6

Subject: RE: MySQL ODBC AddRow from LotusScript Vs Auto-increment field

OK, that’s what I’ll do then!

Thanks to all of you!

Pascal

7

Subject: MySQL ODBC AddRow from LotusScript Vs Auto-increment field

Hi,

There are a few options available to you as below:

Use ‘AbsolutePosition’ property to record the position in the dataset your new record is before requirying and navigating to the record based in this position. This removes the problem you had with people adding records as these go to the end anyway, but dont know if people deleting records is an issue for you…?

There is also a ‘bookmark’ method which you will need to investigate which when recalled after requirying will take you to the same record regardless of additions/removals.

Hope this points you in the right direction.

Regards

8

Subject: RE: MySQL ODBC AddRow from LotusScript Vs Auto-increment field

I avoid the auto increment for this reason. I usually just use my own method / column for the value in the table.

As Tom mentioned, get the last row, and increment it yourself by 1.

If you issue a commit prior to this, it will help avoid new records being added while you do this. Or you can have another table that maintains the incrment counter with one column, read that value, increment and us it that way.

9

Subject: RE: MySQL ODBC AddRow from LotusScript Vs Auto-increment field

Hello and thanks all for your quick answers.Here’s a few precisions:

“why not add the new record then pick up the number of the record before it. You only need to increment this by 1 to end up with the number added to the new record.”

  • I can’t do this because some rows could be deleted… so the number wouldn’t be accurate (and could be duplicated).

“There is also a ‘bookmark’ method which you will need to investigate which when recalled after requirying will take you to the same record regardless of additions/removals.”

  • Where did you find that “bookmark” method? I don’t see it anywhere in the Notes Designer help?

“I avoid the auto increment for this reason. I usually just use my own method / column for the value in the table.”

  • I agree with you, however, the database was created by a third party company… and I’m stuck with it.

Any other idea?

Thanks!

Pascal