9

I have an MS Access database (intolerably enough), and communicating with it through PHP (ODBC).

There is a DateTime field that I have to include in my INSERT statement. This field is NOT defined as "Required" in Access, meaning that it is indeed NULL-able, and in fact some of the rows in the Access database are already NULL.

The problem I'm having is simple: How to insert NULL through SQL? All the results I've found online have addressed it from something like Visual Basic or C#, whereas I'm using SQL through ODBC in PHP.

I have already tried the following:

INSERT INTO table_name (datetime_field) VALUES (NULL)
INSERT INTO table_name (datetime_field) VALUES (#NULL#)
INSERT INTO table_name (datetime_field) VALUES ('NULL')
INSERT INTO table_name (datetime_field) VALUES ('#NULL#')
INSERT INTO table_name (datetime_field) VALUES ('')

(There's about 30 other columns in my query.)

The exact error I get is 'Data type mismatch in criteria expression' when I try '' or NULL. The others return a parse error (understandably).

Please note that I have to include the field in the INSERT statement. The field has a default value, but in many cases the original data that I'm transporting has a NULL that must also be a NULL in the target database.

Thanks in advance!

BIBD
  • 15,107
  • 25
  • 85
  • 137
Teekin
  • 12,581
  • 15
  • 55
  • 67
  • Are all the other fields in the table you're inserting into auto-incrementing or have defaults assigned? Because that's the only way you can insert a single column of data and create a new row. – Paul Sasik Aug 20 '10 at 21:38
  • 1
    There are about 30 columns, none of them auto-incrementing, some of them have defaults, but all of them are in the query itself. – Teekin Aug 20 '10 at 21:40
  • 2
    ah ok, so you just pared the example down to a single column? Have you tried to update a row and set the field to NULL just to see? – Paul Sasik Aug 20 '10 at 21:44
  • 2
    I agree with Paul Sasik, I have tried a query in Access in both ADO and the query design window, and Access was perfectly happy with null. – Fionnuala Aug 21 '10 at 09:09
  • 1
    Open the database in Access and try your first INSERT statement as a new query ... leaving ODBC and PHP out of the picture temporarily. Based on everything you've told us, I can't see why Access' database engine would reject your first sample INSERT statement. – HansUp Aug 21 '10 at 13:52
  • The first INSERT will work just fine in Access, assuming there are no required fields without default values. If it's not working, there's something interfering between PHP and Jet/ACE. – David-W-Fenton Aug 21 '10 at 20:26
  • @hansUp and @David - I've duplicated the same phenomenon natively in 2007 (no PHP) myself as well. The first one fails. – BIBD Aug 21 '10 at 23:59
  • @CodeSlave: your last comment should be deleted now, no? – David-W-Fenton Aug 22 '10 at 19:49
  • @David I experienced the same phenomena as the OP, so IMHO the comment is still valid. All that's changed is I now have "fix". You, Paul, Remou, and HansUp are all correct. It SHOULD work, but it doesn't for the OP and it didn't for me until I flipped the ANSI SQL standards back and forth. – BIBD Aug 23 '10 at 03:31
  • @CodeSlave: You're very confused. SQL mode is not a Jet/ACE issue, but an Access issue. Jet/ACE knows nothing at all about SQL 89 or SQL 92 mode -- that's why which mode you use is controlled not by the Jet/ACE version, but by the interface you use to interact with Jet/ACE. But this isn't a SQL mode issue, anyway, since the relevant SQL works in versions of Access that predate the introduction of SQL 92 mode. – David-W-Fenton Aug 23 '10 at 19:33
  • @David, either the OP and I are lying or there's something going on here. Believe what you like. I'm telling you I got the same kind of results as the OP. I flipped the SQL from 89 to 92 and then it worked as expected. I flipped it back to 89, and it still worked as expected. It makes no sense, it SHOULDN'T have fixed anything, but that's what happened. And given that the OP appears to have tried my trick, and now it seems to be magically working for them (even if they say it didn't immediately), that makes me more suspicious that I'm right. – BIBD Aug 24 '10 at 02:24
  • 1
    The OP is not using Access. SQL mode is an Access setting, not a Jet/ACE setting. So, it isn't possible that your problem and the OP's have the same source. – David-W-Fenton Aug 24 '10 at 19:26

6 Answers6

3

Try the following. It works for me:

INSERT INTO sometable ( somedate, somethingelse )
SELECT Null AS Expr1, "foo" AS Expr2;

Basically, you are wrapping the null in the select query and letting SQL figure out how to represent it to the insert.

-- EDIT --

This SHOULD also work:

INSERT INTO sometable ( somedate, somethingelse )
values (Null , "foo");

But for some reason it doesn't with my default install.

On I hunch, I switched my DB from ANSI-89 to ANSI-92, and the VALUES method started working. I switched it back to ANSI-89, and it still works. Not only that, on ANY new database I create, it now also works. Weird... something in the installation must be getting changed, (and sticking) by the switching back and forth that's not just ANSI-89/92. This seems to be why we were getting different results.

You can switch the database ocwe by going to Office Logo->Access Options->OBJECT DESIGNERS->QUERY DESIGN. Change SQL Server Compatible Syntax (ANSI 92) - and checking "This database".

Ok, very odd.

BIBD
  • 15,107
  • 25
  • 85
  • 137
  • You must have a from table with Access, that is not going to work. – Fionnuala Aug 21 '10 at 19:35
  • Replace the SELECT... with VALUES (Null, "foo") – David-W-Fenton Aug 21 '10 at 20:21
  • @Remou - I've tried it natively in MS Access 2007 - and it works. That was a straight copy and paste – BIBD Aug 21 '10 at 23:54
  • @David - Like wise, natively in MS Access 2007, values(null,"foo") doesn't work, and clearly it doesn't for the OP either, based upon their first try/example. – BIBD Aug 21 '10 at 23:56
  • 1
    You can INSERT a single-row SELECT without a FROM. If you wanted to UNION two SELECTs for the INSERT, that would be a different story. – HansUp Aug 22 '10 at 03:27
  • 1
    @CodeSlave Interesting, you are right on the select not needing a from table in this instance, however, I do not see why Values does not work for you. It is standard SQL and has worked on every version of Access in ADO and native since I can recall. – Fionnuala Aug 22 '10 at 10:16
  • I just tested this SQL INSERT INTO tblCustomer (LastName, Created) VALUES ("Fenton", Null); in Access (the Created field has a default value), and it worked exactly as expected. I did it in the QBE SQL view, not using any special mode (e.g., default SQL 89). As @Remou says, this has always been supported in Access/Jet/ACE. – David-W-Fenton Aug 22 '10 at 19:45
  • @CodeSlave: there was obviously something wonky with your instance of Access. This has always worked in a default Access install, and there's no need to use SQL 92 mode to make it work. – David-W-Fenton Aug 22 '10 at 19:47
  • @David. What can I say? It was a vanilla install (all defaults), patched up to date, and I'd never swapped ANSI versions before. It failed the same way as the OP, and wouldn't let me insert using "values" at all. Flipping between 89 and 92 and back to 89 made it work. I would hypothesise that you, Remou and HansUp probably have flipped back and forth (or move to 92 permanently) on your installations before testing. The only way to tell is with a fresh installation; possibly on a VIRGIN workstation. I can't try it here right now, but I'll build something it on Tuesday/Wednesday to prove it out. – BIBD Aug 23 '10 at 03:41
  • I'll also note, when I built the insert query the first time in the query builder, it created it exactly as it was in my first example. After doing the 89-92-89 flip, it did it the second way. Anyone have a contact on MS's office development team. This is a neat one. – BIBD Aug 23 '10 at 03:45
  • I don't use SQL 92 except for testing things on SO! So, yes, my installation has been flipped, but support for SELECT-less inserts is not a SQL 92 issue in the first place. It's bog-standard SQL that was supported before SQL 92 mode even existed. I just tested it in A97 and it worked fine. My guess is that you have multiple versions of Access installed and ended up running in a version that had not completed the re-registration process after running one of the other versions. – David-W-Fenton Aug 23 '10 at 19:29
  • Sorry David, no dice. Vanilla install. But the fact that you have switched back and forth previously would be consistent with what I experienced. Like I said, I'll try it on a brand new machine this week. – BIBD Aug 24 '10 at 01:57
  • It's all a red herring as the OP isn't using Access at all, so our installations of Access don't have anything to do with his/her problem. – David-W-Fenton Aug 24 '10 at 19:26
  • I just tested on a different machine with a A2003 install that I know has never been flipped between SQL 89 and SQL 92 mode. The SELECT-less insert worked just fine. Trying the same thing on the same machine in A2010 (also never having SQL mode flipped) also worked. I don't know what the source of your problem is, but it isn't something inherent to Access or Jet/ACE. – David-W-Fenton Aug 24 '10 at 19:41
1

INSERT INTO table_name (datetime_field) VALUES (DbNull.Value)

Bartłomiej Semańczyk
  • 59,234
  • 49
  • 233
  • 358
ramesh kumar
  • 81
  • 1
  • 2
1

I know you've already figured this out but there is also dbNull

masfenix
  • 7,736
  • 11
  • 45
  • 60
0

Try just leaving it blank

(values fld1,,fld2)
Beth
  • 9,531
  • 1
  • 24
  • 43
  • I do not believe that will work, it causes a syntax error both in ADO and the query design window. – Fionnuala Aug 21 '10 at 09:05
  • 1
    You could also try leaving out the reference to the field entirely- insert (fld1,fld3) values (val1,val3) – Beth Aug 21 '10 at 14:05
  • Yeah, you're right, forgot about that. Since it's a date field, try zero. – Beth Aug 21 '10 at 15:15
  • (sigh) OK, we also had an issue with a DB2 ODBC driver where it had to be configured a certain way to pass dates to MS Access dbs. It could be something with the ODBC driver settings. – Beth Aug 21 '10 at 15:59
  • I cannot leave it blank because it has a default value, so if I leave it, it won't be null. – Teekin Aug 22 '10 at 23:53
0

What are the libraries, you are using in order to talk to ODBC?
Could it be a problem with the syntax for null values, the way library interprets it?

See, if this page helps.
Note: I have not worked with PHP.

shahkalpesh
  • 33,172
  • 3
  • 63
  • 88
  • I'm using the ODBC components in PHP. It shouldn't matter though, since I'm just passing SQL to the ODBC driver. – Teekin Aug 23 '10 at 13:57
  • 1
    I don't work in PHP that often, but is there perhaps a PHP magic value ofr Null or something? That is, are you passing the word "Null" as a string in the SQL statement? It's a long shot... – David-W-Fenton Aug 23 '10 at 19:30
0

I have this type of error and Try this.

INSERT INTO table_name (datetime_field) VALUES (DBNull.value)

It works fine for me.

mjb
  • 1
  • 2