Help with MSSQL Plugin

I am having an issue with using the MSSQL plugin I hope someone can help me with.
Originally I thought this issue was related to the use of prepared statements, but I am not so sure now. It appears that the Plugin is masking or unable to retrieve the appropriate error from the database when one occurs. For example, if I enter a record that violates a unique index or required field I would expect to get a meaningful error number and error message back. It seems that regardless of the error type the plugin always reports back:

Error Number: 3261
Message: “This statement has been terminated.”

I am doing the error checking right aft the insert. Here is my code:


dim ps As MSSQLServerPreparedStatement
  
ps = App.GBCM.Prepare("INSERT INTO Cores (GBCorePN, MinCoreQty, PartNote, PCRowID) VALUES (?,?,?,?)")
     
  // Specify binding types
  ps.BindType(0,MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
  ps.BindType(1,MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_INT)
  ps.BindType(2,MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
  ps.BindType(3,MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_INT)
  
  If App.GBCM.Error Then
    MsgBox "BINDING Database Query Error" + EndOfLine + EndOfLine + "Error Code: " + Str(App.GBCM.ErrorCode) + " " + App.GBCM.ErrorMessage
    Return
  End If
  
// Now, bind our data
  ps.Bind(0,Trim(fldGBCorePN.Text))
  ps.Bind(1,val(fldMinCoreQty.Text))
  ps.Bind(2,Trim(fldPartNote.Text))
  ps.Bind(3,ClassID)
  
  ps.SQLExecute
  
  // Error check
  If App.GBCM.Error Then
    MsgBox "ADDING Database Query Error" + EndOfLine + EndOfLine + "Error Code: " + Str(App.GBCM.ErrorCode) + " " + App.GBCM.ErrorMessage
    Return
  End If
  

This code works fine if there are no data validation issues, but I want to be able to provide meaningful database errors.

Ideas, do you see issues with my code?

The way to test it is to build the SQL manually and see if you get an error after the insert. I’ve not done much MS SQL Server work in Xojo but I’ve seen enough prepared statement issues to understand that the error you get from a Prepared Statement isn’t necessary the real issue. Xojo has to rely on the database driver to provide the db error and I’ve seen where prepared statements sometimes mask the real issue.

So, my recommendation is to build the SQL manually (for now) and see what happens.

http://www.sql-server-helper.com/error-messages/msg-3001-4000.aspx says:

3261: SQL Server cannot use the virtual device configuration.

I bet you already suspected that, right?

[quote=274329:@Maximilian Tyrtania]http://www.sql-server-helper.com/error-messages/msg-3001-4000.aspx says:

3261: SQL Server cannot use the virtual device configuration.

I bet you already suspected that, right?[/quote]

HI Maximilian,

I am sorry, the error code is 3621, not 3261, my dyslexia kicked in… Error 3621 means “The statement has been terminated.”

Thanks for checking though…

[quote=274325:@Bob Keeney]The way to test it is to build the SQL manually and see if you get an error after the insert. I’ve not done much MS SQL Server work in Xojo but I’ve seen enough prepared statement issues to understand that the error you get from a Prepared Statement isn’t necessary the real issue. Xojo has to rely on the database driver to provide the db error and I’ve seen where prepared statements sometimes mask the real issue.

So, my recommendation is to build the SQL manually (for now) and see what happens.[/quote]

Bob, thanks for the idea. Unfortunately, even the manual SQL returns the same error, regardless of the error type. If I try to enter a record that duplicates an index that has a duplicate value I get code 3621 reported by the plugin.

However, If I execute the same query through Microsoft Management Studio I get this error (which is what I would expect):

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.Cores’ with unique index ‘IX_CoreCorePN’. The duplicate key value is (521-118X).
The statement has been terminated.

It would appear the Plugin is not catching the correct error 2601, but it is catching the last line which I would presume to be the same for any error. It is interesting that the error code 3621 which is what the plugin is reported is not mentioned in the error that MS Management Studio reports.

Unless I am missing something this pretty much makes the entire MS SQL plugin not usable because it returns the same error regardless of type - at least for anyone wanting to return meaningful error descriptions. I really can’t believe this was overlooked during the plugin development or something must have changed after release.

I’m missing something or there has to be a work around for this…

Have you tried using the ODBC plugin instead? That seems to work better with MSSQL than the dedicated plugin and is xPlat.

My limited look into this problem indicates that MS SQL now throws two errors the last being “The statement has been terminated.”, and of course we’re seeing the last error message not the meaningful one.

I would guess that when this is converted to the new framework where exceptions are raised rather than having to check lasterrorcode we’ll get both exceptions thrown.

Hi Wayne,

Thanks for the input and ideas. I am exploring that now. My main concern is speed using the ODBC plugin versus the native MS SQL plugin.

It’s a bit of a workaround but I think I can do field validation in the application, alerting the user of the required fields prior to even sending them to the DB. The duplicate key I can not easily check for (I suppose I could try to query it). I think these are the two primary errors I would be looking for, but who knows. I also don’t know if there are these types of issues, or other ones with the ODBC plugin, which I wouldn’t figure out until I am into it deep. My thought is keep the speed and try to work around the error messages and hope the plugin can be fixed latter.