system.db.runPrepUpdate syntax help

Ignition
1

Hi,

I’m trying to execute this line of code in a gateway tag change script:

system.db.runPrepUpdate(“INSERT INTO recipe_interlock (IngredientAdded) VALUES (?) WHERE (RecipeNumber = ‘%i’ AND IngredientNumber = ‘%i’)”[True]%(RecipeID,IngredientNumSelected))

The IngredientAdded column in the recipe_interlock table is boolean. i get the following error on this line of code when the script fires:

TypeError: not all arguments converted during string formatting

Whats the correct syntax for this? :scratch:
Andre

2

Hi Andre!

What’s happening is that you are mixing two different syntaxes. With runPrepUpdate, you need to use a question mark for every placeholder, and the parameters all go in one list:

system.db.runPrepUpdate("INSERT INTO recipe_interlock (IngredientAdded) VALUES (?) WHERE (RecipeNumber = '?' AND IngredientNumber = '?')", [True, RecipeID, IngredientNumSelected])
3

Hi Jordan,

Thanks for the help, when i use your syntax i get this error

Caused by: java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1)

4

Actually, i added an extra variable but still should work

system.db.runPrepUpdate(“INSERT INTO recipe_interlock (IngredientAdded) VALUES (?) WHERE (RecipeNumber = ‘?’ AND ItemID = ‘?’ AND IngredientNumber = ‘?’)”,[True,RecipeID,ItemID,IngredientNumSelected])

5

Don't quote the question marks.

6

I’m not 100% sure how but i got it working with this query. UPDATE and SET must be different?

system.db.runPrepUpdate("UPDATE recipe_interlock SET IngredientAdded=? WHERE RecipeNumber=? AND ItemID=? AND IngredientNumber=?",[True,RecipeID,ItemID,IngredientNumSelected])

Thanks for your help guys

7

[quote=“Andre”]I’m not 100% sure how but i got it working with this query. UPDATE and SET must be different?[/quote]/ me smacks forehead /
I just looked closer at the INSERT statement that I commented on – I was so focused on the quotes that didn’t belong with the question marks. INSERT statements that supply values don’t have WHERE clauses. You list the column names and the values that go with them:INSERT INTO tablename (columnname1, columnname2, ...) VALUES (value1, value2, ...)UPDATE statements list the assignments to make and a WHERE clause that limits the changes to the desired rows (or not – you can change all the rows in the table if you like):UPDATE tablename SET columnname1=value1, columnname2=value2, ... WHERE ....
Hope that helps.

8

That explanation should be added to the help file!

9

[quote=“Andre”]That explanation should be added to the help file![/quote]Well, Ignition is agnostic on which database product you should use, and there are syntax differences, so a referral is a bit more reasonable.
I’m a fan of PostgreSQL, so here you go: Inserting Data and Updating Data.

10

Here’s the corresponding Oracle online links: Inserting Data and Updating Data.