API 2.0 for Database, exceptions not always thrown ?

Hi,

I am going through the Your First Web App series in xDev magazine, changing the code to use API 2.0. This is the code for the UpdateRecord Method I have:

[code]Var d As DateTime = DateTime.Now

Try

Var ps As SQLitePreparedStatement = _
Session.mediaDB.Prepare(“UPDATE Media SET TITLE = ?, Type = ?, Year = ?, Notes = ?, Added = ? WHERE ID_MEDIA = ?”)

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(5, SQLitePreparedStatement.SQLITE_TEXT)

ps.ExecuteSQL(txtTitle.Text, popType.Text, txtYear.Text, txtNotes.Text, d.SQLDate, id)

Catch error As DatabaseException

MessageBox("Update record error: " + error.Message)

End Try

webMain.Show
Self.close[/code]

Everything looks fine, thing is the record is not updated. After pulling my hair a lot, I finally found out what’s wrong. In the prepared statement there is an error with the name of a column: I should have typed MediaType instead of Type.

Shouldn’t that throw an exception ? I may understand that at the Prepare Statement it may not get catch, but what about ExecuteSQL, since I try to get a non existent column ?

Does some one have an explanation for that ?

Thanks

one of the two should have thrown an error I would think esp if the column name was wrong

it does give an error in API 1.0 :slight_smile:

Dim mediaDB As New sqlitedatabase
Call mediaDB.Connect

mediadb.SQLExecute("create table Media( TITLE, MediaType, Year, Notes, Added, ID_MEDIA)")
mediadb.SQLExecute("insert into Media( TITLE, MediaType, Year, Notes, Added, ID_MEDIA) values('1','2','3','4','5','6')")

// note the column name mismatch between TYPE and MediaTYPE

Dim ps As SQLitePreparedStatement = _
mediaDB.Prepare("UPDATE Media SET TITLE = ?, Type = ?, Year = ?, Notes = ?, Added = ? WHERE ID_MEDIA = ?")

If mediaDB.Error Then 
  Break
End If
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(5, SQLitePreparedStatement.SQLITE_TEXT)

ps.SQLExecute( "10","20","30","40","50","60" )

If mediaDB.Error Then 
  Break
End If

Thanks @Norman Palardy,

I added the if mediaDB.Error Then and this catches the error. I created a bug with Feedback (#59604).

From now on, I will check errors the old way when an exception is not raises.

API 2.0 needs a test suite where Xojo finds these long before we run into them trying to port code to API 2

Indeed , lately I start to get more and more like those and as Gilles I start to go back to the old way as they new way apparently is not to reliable now

This is serious and confirms in my view that it is not yet the time to transfer existing projects to the new API. Unfortunately. Database interaction is the pillar on which many projects rely.

<https://xojo.com/issue/59615>

I did not make a case since I haven’t seen this issue myself. (I am still on 2019R1.1)
Thanks @Norman Palardy

Ok, then API 2.0 isn’t ready for now. What’s unfortunate is that the IDE autocomplete does not suggest API 1.0 methods anymore (2019 R3.1) . . .

it may or may not depending on the project

if you started the project in 2019r3 it wont
IF the project was started in 2019r1.1 or earlier you may still see the old API suggested

you can get the IDE to show them to you IF you save as text (probably in Xml as well but I havent looked)
editing the main manifest and removing the line that starts

OrigIDEVersion=

and setting

RBProjectVersion=2019.031
MinIDEVersion=20070100

will convince the IDE that the old apis should be show (or seems to here)