I have taken over another Xojo programmer’s app for their client who uses MSSQL. I have been converting the raw SQL INSERT and UPDATE commands to use SQLDatabaseMBS prepared statements instead.
I am having a problem when doing an UPDATE. All fields update OK if I don’t update the eve_freq_mins field. It’s an integer, like others that can update, yet the whole command will not update if I include this field in the update.
In my code I have a method that stores the field name, type and variant aside in three arrays:
commonSQL.doPreparedArraysWAD(fieldNames, fieldTypes, fieldValues, "eve_freq_mins", "Integer", execmins)
Then I use a method to create the SQL command:
tempSQL = commonSQL.getPreparedSQLWAD("UPDATE bas_eve_main SET", fieldNames, "WHERE eve_pk = " + str(pk))Then I set the bind types to those field types and execute the command:
tempSQLPreparedStatementMBS = app.db.Prepare(tempSQL)
commonSQL.doPreparedBindTypeWAD(tempSQLPreparedStatementMBS, fieldNames, fieldTypes, fieldValues)
tempSQLPreparedStatementMBS.SQLExecuteMT
What happens is that there is no error ie app.db.Error is False and so I do a commit.
If app.db.Error Then
commonDatabases.doDisplayDatabaseErrorWAD(app.db, CurrentMethodName)
Return False
Else
app.db.Commit 'gets to here OK, with app.db.Error = False, but an MSSQL error
End If
But if you look at the database ErrorMessage it says:
HY104 [Microsoft][ODBC Driver 17 for SQL Server]Invalid precision value
If I comment out the command to update the eve_freq_mins field, there is no error, ErrorMessage is blank and the commit updates the record.
The MSSQL server is: Microsoft SQL Server Release 11.00.7001. I am debugging it as a 32-bit application on Windows 10 64-bit via Parallels on macOS. Here is the table definition:
CREATE TABLE [dbo].[bas_eve_main](
[eve_pk] [int] IDENTITY(1,1) NOT NULL,
[eve_timestamp] [varchar](30) NOT NULL,
[eve_active] [int] NOT NULL,
[eve_sticky] [int] NOT NULL,
[eve_id] [varchar](100) NOT NULL,
[eve_name] [varchar](100) NOT NULL,
[eve_freq_mins] [int] NOT NULL, <== this is the problem field
[eve_script] [varchar](3999) NULL,
[eve_p1] [varchar](50) NULL,
[eve_p2] [varchar](50) NULL,
[eve_p3] [varchar](50) NULL,
[eve_email] [varchar](299) NULL,
[eve_nextrun] [datetime] NULL,
[eve_quarantine] [int] NULL,
[eve_notes] [varchar](999) NULL,
PRIMARY KEY CLUSTERED
(
[eve_pk] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Internet searches show that others had ‘Invalid precision value’ errors when storing strings, but mine is an Integer. Others had ‘Invalid precision value’ errors connecting to MSSQL 2005, but mine is more recent. On the Xojo forums others had this issue with the Xojo driver, but I am using the most recent MBS driver.
https://forum.xojo.com/30017-64bit-vs-32bit-odbcplugin-windows-invalid-precision-value
I have tried binding the integer as SQLPreparedStatementMBS.kTypeNumeric/kTypeLong/kTypeInt64 but they all give the same error. I assume kTypeLong is the best bindtype for a 32-bit integer.
Any clues on why MSSQL is giving this error when merely updating an Integer?