Problems with MySQL to MSSQL Migration In Scripts

I am having a problem with migrating all my forms and SQL queries from MySQL to MSSQL. The code for the script that is excuted whenever I want to try and open a CSV file is in the attached file. I do not think my syntax is wrong between MySQL and MSSQL but maybe it is because the script works perfectly when pointed at the MySQL database. Thanks for your help!

Hi,

Looks like the main problem is that you’re quoting your column names with back-ticks (`), which is fine for mysql, but not sql server. In case you didn’t know (or anyone reading this), doing that causes the name to become case sensitive. It also let’s you use column names that would otherwise not be allowed. Most of the time they are not necessary, but often if you create the table using them, you must always use them.

Two options: 1) If you didn’t create the table with them- remove the ticks. It should be fine without them.
2) Convert it for SQL Server- which uses quotes (") to do the same thing. So instead of model=? use “model” = ?
Note: Since your queries are strings already enclosed in quotes, you’ll actually have to escape them, so it’ll actually look more like: “model”= ?

Hope this helps. On a side note, if the script isn’t unreasonably large, it’s probably better to post it here using the code formatting option, just so everyone can see what we’re talking about and not have to download it. I’ve posted it below, just for completeness.

Regards,

Colby Clegg
Inductive Automation

path = fpmi.file.openFile('csv') if path != None: fin = open(path) lines = fin.readlines() fin.close() i = 0 for line in lines: if i == 0: i = i+1 else: data = line.split(",") if fpmi.db.runScalarQuery("SELECT COUNT(id) FROM line WHERE id = %s" % (data[0]), "manufacturing") == 0: fpmi.db.runPrepStmt("INSERT INTO line (`id`, `model`, `ser`, `desc1`, `desc2`, `desc3`) VALUES(?,?,?,?,?,?)", "manufacturing", data) else: fpmi.db.runPrepStmt("UPDATE line SET `model` = ?, `ser` = ?, `desc1` = ?, `desc2` = ?, `desc3` = ? WHERE id = ?", "manufacturing", [data[1], data[2], data[3], data[4], data[5], data[0]]) [/b]