SQL Server MSSQLSERVER_TYPE_NULL - how to use it?

Hi,

SQL Server Bind Type MSSQLSERVER_TYPE_NULL

Does anybody know how to use it, please?

My SQL test statement looks like this:
SELECT myvar=COALESCE( ? ,‘Hi’)

My code looks like this:
ps = db.Prepare("SELECT myvar=COALESCE( ? ,‘Hi’) "l)

ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_NULL)
ps.Bind(0, Nil) // I tried with and without this line

rs = ps.SQLSelect()

The result is supposed to be 1 row, but I get rs=Nil.

Thanks,
Stan

Do you have checked the DB.Error? If your access is erroneous the error message is contained in DB.Error and the recordset RS is nil

Thank you Michael,
yes, I checked. There is no error.

Apart from ‘how do you use this’, what are you actually trying to do?
The code above seems to just want to

[quote]
select ‘Hi’[/quote]

Its very artificial.

Im struggling to think of a reason why you would bind a null into a parameterised query.
Are you searching for things that are null?

…where field is null…

This is just a simple query to test this behavior. Proof of concept.
In reality, there are many cases when NULL has to be passed to a stored procedure or query.

I think when you WANT to pass a null, you bind but dont pass anything.

[code]if bIntendToPassNull then
statement.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_NULL)

else
statement.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_TEXT)
end if[/code]

I tried that and it didn’t work. I tried NULL with passing Nil, and with binding but not passing.

[quote=323929:@Jeff Tullin]I think when you WANT to pass a null, you bind but dont pass anything.

[code]if bIntendToPassNull then
statement.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_NULL)

else
statement.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_TEXT)
end if[/code][/quote]

Pity.

If I access directly to a SQL table of a MS SQL server I write

ps = DB.prepare("Select * from customer where C_CUSTNO = '1234'")

and I’ve tested your problem successfully with

ps = DB.prepare("SELECT * FROM customer WHERE PRO_ID IS NULL")

You can also read the other records with

ps = DB.prepare("SELECT * FROM customer WHERE PRO_ID IS NOT NULL")

I do not use any BIND or BINDTYPE.

Your original examples used an “=” in the prepared statement… this you cannot do with NULL… Null does not have an equality… it must be used with “IS” or “IS NOT” , or coverted using statements like “isNULL”,“NVL” etc (depending on database engine)

Your code should work. I tested it with this table:

id myvar 1 Ha 2 He 3 Hi 4 Ho
The record set contains:

0 0 1 0

Actually no, thats the point of coalesce … it returns the first non null value from the list of parameters
select COALESCE( null ,‘Hi’) would return ‘Hi’

That said, I have no idea what select myvar = COALESCE( null ,‘Hi’) was supposed to do
Maybe return one row with TRUE or FALSE depending upon the contents of myvar?

But Myvar is not predefined here or selected from a table…
So that test SQL just makes no sense to me, but hey, maybe its doing something too cool for me to follow…

Especially the "l) bit on the end.

In Sqlite I’ve done this by passing the word “NULL”

[code]ps = db.Prepare(“insert into packages(service, track_num, label, delivered, hidden) values (?, ?, ?, ?, ?)”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)

if l <> “” then
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
else
l = “NULL”
ps.BindType(2, SQLitePreparedStatement.SQLITE_NULL)
end if

ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)

ps.SQLExecute(integer.fromtext(s), t, l, “n”, “n”)[/code]

Now whether this works with SQL Server I don’t know.

Hi Guys, thank you for helping…

The only way I could get it to work was by sending string “NULL” and changing my query to this:

DECLARE @x varchar(8000); SELECT @x= ?; SELECT @x=CASE WHEN @x=‘NULL’ THEN NULL ELSE @x END; SELECT myvar=COALESCE(@x,‘Hey’)

The workaround is far from ideal, but the only way. I could find, to pass nulls.

The conclusion so far is that MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_NULL doesn’t work on MS SQL Server.

BTW, differences from what I see you do on SQLite and SQL Server:

  • passing parameters on SQL Server works only through Bind(), passing directly in SQLExecute() or SQLSelect() doesn’t work,
  • recordset is returned from SQLSelect() only, SQLExecute doesn’t return a recordset.

S.

if using SQLite why not use this function instead of CASE

select ifNULL(InfoDetail,'text') InfoDetail;   // output would be 'text' if input were NULL

or SQL Server

select isNULLl(InfoDetail,'text') InfoDetail;   // output would be 'text' if input were NULL

there are similar functions in almost every major Database Engine… and I’ll bet a WHOLE lot faster than CASE

[quote=323993:@Stan F]
BTW, differences from what I see you do on SQLite and SQL Server:

  • passing parameters on SQL Server works only through Bind(), passing directly in SQLExecute() or SQLSelect() doesn’t work,
  • recordset is returned from SQLSelect() only, SQLExecute doesn’t return a recordset.
    S.[/quote]

SQLExecute is for anything that does NOT return a set of rows as a result
SQLSelect is for anything that returns results as rows (even a stored procedure that returns a set of rows)

One does not return data
The other does

Dave,

the problem all along is that there is no way to pass NULL to SQL Server.

The workaround I found was to pass a ‘NULL’ string to SQL Server, and replace it with real NULL on SQL Server.

I see another serious problem with the SQL Server: it doesn’t return errors.

I am in process of switching to the ODBC driver. The SQL Server driver doesn’t seem to be ready for the prime time yet.

S.

All problems have disappeared when I switched to the ODBC driver.
ODBCPreparedStatement.ODBC_TYPE_NULL works as expected and allows me to pass Nils/Nulls to SQL Server.

S.

Hi Stan,
I am just trying to pass NULL as a Parameter to Sqlserver for a DateTime column using ODBC.
However it does not work whatever I try. I did manage to pass NULL to a numeric column though.
Did you get it to work such datatypes?

Thanks in advance