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]