Can not pass NULL as parameter to OdbcPreparedStatement

Databases
1

I can not find the right way to pass NULL as a parameter to SqlServer, I always get:

[Microsoft] [ODBC SQL Server Driver] Ungültiger Genauigkeitswert

from the server. Yes, the message is in german and it translates to “illegal precision”, probably meaning the length of the argument passed.

What I do is:

dim Db as OdbcDatabase
... connect ...
dim prep as ODBCPreparedStatement = Db.Prepare( "Update MyTable set MyDate=? where IdVal=123")
Prep.BindType 0, ODBCPreparedStatement.ODBC_TYPE_NULL
Prep.Bind 0, Nil

I tried several versions of this ( including setting a decimal instead of a datetime column) but can not get this to work.

When using non NULL values everything is fine

[code]
dim mydate as new date
Prep.BindType 0, ODBCPreparedStatement.ODBC_TYPE_DATE
Prep.Bind 0, mydate
/[code]

and so is the direct statement: Db.SqlExecute “Update MyTable set MyDate=NULL where IdVal=123”

I use a german Windows7, Xojo 2017R2.1 and SqlServer 2016.

Did anybody succeed in getting ODBCPreparedStatement.ODBC_TYPE_NULL to work?

2

Leave out this line:

3

Thanks for your response.

Unfortunatelay commenting out the BIND statement just changes the error to:
“Das COUNT-Feld ist nicht korrekt oder es besteht ein Syntaxfehler”
translated:
“The count field is not correct or there is a syntax error”

4

How about

Prep.BindType 0, ODBCPreparedStatement.ODBC_TYPE_DATE
Prep.Bind 0, Nil
5

Thanks for your response too.

By checking it I noted that I made a mistake in my original post by saying ODBC_TYPE_DATE works with a legal Date.

This is wrong as ODBC_TYPE_DATE will always give me: “Optional feature has not been implemented” no matter what i pass.

I can however declare the param as ODBC_TYPE_STRING and pass a legal Sql_Date_Time which works.
Unfortunately this type does not allow to pass NIL (->the value of parameter 0 is not a string value).
Passing an empty string sets the datetime ‘1900-01-01 00:00:00’ instead of NULL.

What does work is passing NIL for a numeric database field.

Just for my datetime values I simply find no way to get it right.

I thought someone posted some years ago that he succeeded with this. Was that on a windows client?

6

Just to summarize what I have so far:

the goal: pass NULL as a parameter for an Sqlserver datetime field
the problem: how to declare the parameter and what to pass
the environment: Xojo 2017R2.1 on german Windows 7, Sqlserver 2016 on english Windows 2012 server

if I declare as:
ODBC_TYPE_DATE or ODBC_TYPE_TIMESTAMP:
==> “Optional feature has not been implemented” no matter what I pass

ODBC_TYPE_NULL:
==> “illegal precision value” no matter what I pass

ODBC_TYPE_STRING:
==> Works fine for normal dates but has no way to pass NULL (empty string -> ‘1900-01-01…’, NIL -> ‘conversion error’ )

Here the Testtable:

CREATE TABLE [dbo].[mytable](
	[idval] [int] NOT NULL,
	[mydate] [datetime] NULL,
	[mynumber] [decimal](7, 1) NULL,
 CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED ([idval] ASC))
GO
insert into mytable (idval, mydate, mynumber) values(123, NULL, NULL)
7

Maybe you can account for it in the SQL statement. Not sure if this will work but maybe something like Set MyDate=coalesce(?,Null).

8

Why don’t you use Xojo’s MSSQL driver?

9

@Robert Riordan
Well, COALESCE looks für the first non-Null expression so that won‘t help. Actually I did look at solutions without parameters but I need to write a generic Routine and I hoped to not having to sanitize every single field value.

@Eli Ott
In the past I had nothing but trouble with the native driver. Plus I found a post in this forum where someone could not get this to work with the native driver but said he succeeded using ODBC. Remember I am on windows, it might be different on a Mac.

10

Thanks so far for the answers.

Just for reference: the post that says that ODBC_Type_Null should work is titled:

SQL-server-mssqlserver-type-null-how-to-use-it