How to pass DBNull.Value to DB using CacheParameter

Question

var dbParameter = command.CreateParameter(); 
dbParameter.Name = "myParam";
dbParameter.Value = DBNull.Value;
command.Parameters.Add(dbParameter);

sql is like this:

select * from Person where Name is ?

Get an error:

An exception of type 'InterSystems.Data.CacheClient.CacheException' 
occurred in InterSystems.Data.CacheClient.dll but was not handled in user code

Additional information: [SQLCODE: <-1>:<Invalid SQL statement>]

[Location: <Prepare>]
[%msg: < NULL expected, : found^select * from Person where Name is :%qpar>]

When the query is changed to where Name = ? and a value other than DBNull.Value is set, then there is no error.

score 2 · Accepted Answer · answered Apr 14 '14 at 21:55

2

Try this:

SELECT * FROM Person 
WHERE (? IS NULL AND Name IS NULL) OR Name = ?

answered Apr 14 '14 at 21:55

Racil Hilan

24,690
13
50
55

That gives invalid number of parameters error. It thinks the second ? is a different parameter, but if I add it twice with the same value it appears to work. – O.O Apr 14 '14 at 22:11
@O.O What database engine are you using? You probably need to used named parameters. – Racil Hilan Apr 14 '14 at 22:14
@O.O Sorry, my bad. I got confused answering several questions. It seems Intersystems Cache does not support named parameters. [The docs](http://docs.intersystems.com/ens20131/csp/docbook/DocBook.UI.Page.cls?KEY=GZCP_tables#GZCP_table_parameters) clearly state that you will have to provide a value for each `?` "**even if the values are the same**". So what you did is the right way to go (i.e. created two parameters with the same value). – Racil Hilan Apr 14 '14 at 23:01

How to pass DBNull.Value to DB using CacheParameter

1 Answers1