MySQLCommunityServer returns no error

Issuing this INSERT INTO statement returns a dbError = 0 even after a commit is issued. This statement is the 3rd of 3 separated by ;

INSERT INTO ledger(id, memberID, points, isPointBoost, note, created, createdBy) VALUES('dCF18FfC-1B7F-4a6C-B8Ad-ccceAfadeDBe', 'd860498d-4d1c-42df-8d0d-e62b190e8035', 5, 0, [SQL] 'Syntax Error', 1508073472, 'ABeDc18C-d5cf-4EcE-B9B5-dCaFfE826d8F')

If I try to execute this statement through the MySQL CLI (Ubuntu) I get

Thoughts?

MySQL: 5.6.33-0ubuntu0.14.04.1 (Ubuntu)
OS (Dev): Mac OS High Sierra
App: Web project
Xojo: 2016r11
Xojo MySQL plugin: 4/27/2016 1:12 PM (10.9MB)

[code]Function ConnectSQL(liveMode As Boolean) As Boolean
db = New MySQLCommunityServer

' Open connection to database
db.Host = App.dbHost
db.Port = App.dbPort
db.DatabaseName = App.dbName
db.UserName = App.dbUser
db.Password = App.dbPassword

if db.Connect then
db.SQLExecute(“SET NAMES ‘utf-8’”)
dbErrMsg = “”
dbErrNum = 0

Return True

else
dbErrNum = db.ErrorCode
dbErrMsg = db.ErrorMessage

Return False

end if

End Function

Function ExecuteSQL(SQL As String) As Boolean
if db.Connect then
db.SQLExecute(“START TRANSACTION;” + SQL + “;”)
end if

if db.Error then
dbErrNum = db.ErrorCode
dbErrMsg = db.ErrorMessage
db.Rollback

App.LogDebug(enumLogLevel.Error, enumLog.Database, CurrentMethodName, dbErrNum, dbErrMsg, SQL)

Return False

else
dbErrMsg = “”
dbErrNum = 0
db.Commit

App.LogDebug(enumLogLevel.Information, enumLog.Database, CurrentMethodName, dbErrNum, dbErrMsg, SQL)

Return True

end if

End Function
[/code]

If the connection fails you will not execute the sql, therefore db.error will be false. I would assume an error after db.commit, but you aren’t checking for one there.

that does not relate to a valid string or numeric value… therefore I would say the error message is telling you exactly what the problem is.

Wayne, if I step through and manually check the db.error, it remains as 0. Statement 1;statement 2 execute fine. It’s when I have a statement with an invalid string that the plugin returns no error. START TRANSACTION;INSERT INTO…;INSERT INTO…;INSERT INTO ledger(…;COMMIT/ROLLBACK.

Dave, with the invalid string executed via the MySQL Community Server plugin, I get a db.error of 0. To illustrate there should be an error returned, I tried to execute the statement via MySQL’s command line interface. If a valid SQL statement and an (in this case) a statement with an invalid string both return no error, how would you be made aware of the invalid statement except to see that the insert never happened in the DB itself?

try taking the “BEGIN TRANSACTION” out and see what happens

As a test try it with each SQL command executed itself to narrow down the source of the error:

db.Execute("BEGIN") If db.Error Then BREAK db.Execute("INSERT INTO...") If db.Error Then BREAK db.Execute("INSERT INTO...") If db.Error Then BREAK db.Execute("INSERT INTO...") If db.Error Then BREAK etc.

Eli, the issue isn’t that there isn’t an error. the issue is he can’t seem to catch it…

It could be that the next statement – even an empty statement, a “lone” semicolon – resets Database.Error to 0.

I have removed the START/BEGIN TRANSACTION with no change in behavior. I tried issuing a statement that specified an invalid column name and got the same behavior. db.Error is always 0.

Have you insured that DB.CONNECT is STILL true in your execute method?
or that a FALSE being returned from ConnectSQL is being dealt with properly?

I would say the answer is “NO” to at least one of those questions

Function ConnectSQL(liveMode As Boolean) As Boolean
  db = New MySQLCommunityServer

    ' Open connection to database
    db.Host = App.dbHost
    db.Port = App.dbPort
    db.DatabaseName = App.dbName
    db.UserName = App.dbUser
    db.Password = App.dbPassword
    
  if db.Connect then
    db.SQLExecute("SET NAMES 'utf-8'")
    dbErrMsg = ""  // <----- PUT A BREAK POINT HERE (examine ErrMsg/ErrNum)
    dbErrNum = 0
    
    Return True
    
  else
    dbErrNum = db.ErrorCode
    dbErrMsg = db.ErrorMessage
    
    Return False // <----- PUT A BREAK POINT HERE
    
  end if
  
End Function

It’s a known issue. The plugin doesn’t handle errors in a multiple statement. I think it only returns the first one. Or none.

Separate the statements and execute them individually.

Hmmmm. Is that something that is being addressed? I’m thinking a workaround would be a stored procedure?

[quote=354775:@Tim Hare]It’s a known issue. The plugin doesn’t handle errors in a multiple statement. I think it only returns the first one. Or none.

Separate the statements and execute them individually.[/quote]
which is why I suggested removing the “BEGIN TRANSACTION” to see if this were the case.

It’s not an issue with a transaction, it’s when you submit multiple statements in a single SqlExecute call. The OP said he’s submitting 3, separated by semicolons (although he only posted the third of the three).

i highly doubt it. What would the plugin return? An array of errors? The first one that had an error? Just don’t submit multiple statements in a single call.

missed that part…
This is an issue with more than just mySQL… SQLite has the same issue… which is why I wrote a routine for Tadpole to properly determine where each statement ended so the user could submit a slew of them as a script… but each was submitted one at a time within a transaction block