Assign Null to an SQL argument

[Barry64]

Hi,

I am writing a routine to Migrate data from a table in a MySQL database to a table in a new Database. I have an INSERT statement assigning values from the old data to the new using arguments

example,

args.add("EmployeeID",SourceConnection.resultSet.data("EMPLOYEE_ID"))
args.add("EmployeeFirstName",SourceConnection.resultSet.data("EMPLOYEE_FIRST"))
args.add("EmployeeLastName",SourceConnection.resultSet.data("EMPLOYEE_LAST"))

This works unless the original data has a null value. I can't see a way to set the argument to null to pass to the new table. I did try the following but this assigns an empty value rather then a null.

c = SourceConnection.resultSet.data("EMPLOYEE_TITLE")
args.add("EmployeeJobTitle",if(isNull(c),"",c))


Any suggestions?

best regards

[Lance Gurd]

Try this

c = SourceConnection.resultSet.data("EMPLOYEE_TITLE")
args.add("EmployeeJobTitle",if(isNull(c),NULL,c))
​​

[Barry64]

Lance,

Thanks for the suggestion but this didn't work. Using NULL gives an error that the variable 'NULL' cannot be found.

I did then try the following, that also didn't work

c = SourceConnection.resultSet.data("EMPLOYEE_TITLE")
args.add("EmployeeJobTitle",if(isNull(c),null_value(),c))

This was accepted as an argument but the SQL returned the error 'The data type returned by your database is not recognized.'

Finally, I think I have it

args.add("EmployeeSalutation")
c = SourceConnection.resultSet.data("EMPLOYEE_SALUTATION")
if(isNull(c),args.setNull("EmployeeSalutation", "C"),args.set("EmployeeSalutation",c))

This creates the Argument with no value, then sets it to Null, or a value

Thanks for nudging me to the answer

[Lance Gurd]

Glad you got it to work and thanks for posting the solution for future reference. I may need this sometime

[madtowng]

Hi Barry,
I'm going to start with nice job getting the code figured out.
With that said, and I'm sure this will help others in the future, when you say "new database", are you switching from
mySql to something like SQL Server , or are you creating a new schema ( ie: customerB replacing customerA) ?

[Barry64]

I am creating a new structure, still within MySQL.

The current application was created in 2014 and has been used every day, evolving over the years. We have learnt so much about the business and what we need from this application - so time for a complete revamp, both in application and data structures.

Problem is this application is in use every day and has 8 years of Parts, Quotes, purchases, timesheets, notes, uploaded files... etc etc so I needed a way to click a button and move the data over to the new structure to test.
One day the data will move over for the last time, followed by all the users - a major headache but will be so worth it.

[madtowng]

Be sure to create the indexes , trigger, functions, and stored procedures first.
once that is done, the code should be as easy as : insert into <new table>(<new columns>) select <old columns> from <old table>

[CharlesParker]

I am in the same boat Barry64 - I am trying to either cleanup my DB or create anew - either option is difficult.

[madtowng]

The trick is to take manageable steps.
You have knowledge that you didn’t have a decade ago.
Use that knowledge to guide you through this task.