I am writing a script to insert values from a file into a mysql database. The script reads lines from the file and constructs insert queries from them. Sometimes particular columns are empty, so an insert query might look like this...
insert into table (col1, col2, col3, col4) values('abc',100,,'def')
where col3 allows nulls, and also has NULL as its default value.
This insert query throws an error, while if I ran it in MS SQL it would work, and insert null where the insert query has nothing between the commas.
Is it possible to acheive this behaviour in mysql?
I tried doing a replace of
,NULL, but this fails when there are two empty values in a row -
,,, (I end up with
,NULL,,) and also adds extra processing (and therefore extra time taken) to the code. If there is a way to allow insert queries like
insert into table (col,col,col) values(val,,val) I could avoid having to replace nothing values with 'NULL' when constructing the insert query string.
For convenience I include below the code I have written to work around this problem. My code is in a windows script file (vbscript) (not tagged due to it not being directly relevant to the question being asked, but I'll tag it if it should be)
So here is code to replace the 'nothings' with NULL...
row = split(arfile.readline,"|") for i = 0 to ubound(row) row(i) = rtrim(row(i)) if row(i) = "" then row(i) = "NULL" next
Then further down I have a line that combines them all into an insert query (actually it's more complex, it creates 100 insert rows and then executes the command and then creates another 100)
As part of the construction of the query, some values get single quotes added, because they are string or other types that need it. For that reason, wherever there has been a NULL replacement it becomes 'NULL' so I need to also deal with this...
conn.execute( insertquerystart & replace(insertquery,"'NULL'","NULL") & ";")