AnsweredAssumed Answered

Inserting multiple entries with SQL parameters?

Question asked by ErikN on Aug 1, 2016
Latest reply on Aug 1, 2016 by khaner

I am using ODBC/OLEDB Provider to insert data into the PI archive. I am using Python/pyodbc to submit these queries with SQL parameters.For a single entry this is working well:.

 

sql =  "INSERT INTO piarchive..picomp2 (tag, time, value) VALUES (?, ?, CAST(? AS String))"

params = ('TEST_ODBC', '*', 1)

cursor.execute(sql, params)

 

On Pi Square I found a method to do this using UNION ALL:

 

INSERT INTO piarchive..picomp2 (tag, time, value)

SELECT 'TEST_ODBC', '*', '3'

UNION ALL SELECT 'TEST_ODBC', '*-5s',  '4'

UNION ALL SELECT 'TEST_ODBC',  '*-10s'),  '5'

 

And this works just fine. However if I try to combine the two and try to parameterize the queries I get the following error:

 

     [PIOLEDB] [SQL Parser] [Line 1] Syntax error near 'SELECT'. (0) (SQLPrepare)

 

I am not sure if this is not possible in SQL or if I am simply making a syntax error...or if there is another way to do this.

Outcomes