Dynamic SQL insert?

Ignition
1

I have a CSV file that I want to parse through and insert the data to sql. Trouble is, the columns vary row by row. I therefore want to make a dynamic SQL insert statement.

This is my attempt for now, heading home, hopefully when I log on tomorrow you guru’s will have steered me in the right direction;

file = open(path + filename, 'r')
i = 0
dbase = "Xeon"
for line in file.readlines():
	data = line.split(",")
	#l_l = line_length, l_f = lines in file
	l_l = len(data)
	print "This row has: " + str(l_l) + "columns"
	print "File has: " + str(l_f) + "rows"
	i = i + 1
	
	#prepare the sql statements
	if l_l > 0 and l_l < 10:
		print "insert string is: insert into 1, 9"
		query = """
			insert into ignition.daewah_off (profile_code1) values (?)", [data[x]], dbase"""
	elif l_l == 10:
		print "insert string is: insert into 10, etc"
	elif l_l == 18:
		print "insert string is: insert into 18, etc"
		query = "insert into ignition.daewah_off (profile_code) values (?)" +", "
		print query
		
	#split the data into columns
	for x in range(0, l_l):
		y = data[x]
		print y
		system.db.runSFPrepUpdate(query + "["+y+"]")
		print "Db insert complete"
	print "Batch count (i): " + str(i)

It compiles ok but I get nothing inserted to SQL. First row in the file has 18 columns. When I get one column insert working I’ll extend the code to cover the rest. Thoughts?

2

I think you’re misunderstanding the argument part of that function, it should be something like system.db.runSFPrepUpdate(query, y).

Edit: Also you might not want the “SF” version here, you don’t have datasources listed - maybe just runPrepUpdate() instead.

3

I’ll put in a plug for using Named Queries here. You can make and test your insert queries in advance, and then the dynamic part is which one you call in your script.

2 Likes
4

Is it possible to insert NULL/None values with these Named Queries?

5

I want SF as the end goal is opening around 100 files at once, each file could have 100 lines, each line could have 20 columns. So I don’t want any data dropped.

Looking into Named Queries now, I always have 10 static columns which can then have up to 10 dynamic columns following. So do I need 11 unique Named Queries and based on the column count call the appropriate one?

6

Success with the named queries, thanks to Kathy for the suggestion and to the guy that invented copy and paste!

Bit wordy, but it works

import os
import csv
path = 'c:\\temp\\new haff\\work\\'
filename = "00397943.txt"
file = open(path + filename, 'r')
i = 0
params = {"filename":filename, "order_num":data[0], "piece_id":data[1], "constant":data[2], "description":data[3], "bin_id":data[4], 
"profile_code":data[5], "colour_code":data[6], "length":data[7], "end_cut":data[8]}
nam_q = "dwh_9"

for line in file.readlines():
	data = line.split(",")
	#l_l = line_length
	l_l = len(data)
	print "This row has: " + str(l_l) + "columns"
	i = i + 1
	
	if l_l == 10:
		print "insert string is: insert into 10, etc"
		params = {"filename":filename, "order_num":data[0], "piece_id":data[1], "constant":data[2], "description":data[3], "bin_id":data[4], 
		"profile_code":data[5], "colour_code":data[6], "length":data[7], "end_cut":data[8], "macro_1":data[9]}
		nam_q = "dwh_10"
	elif l_l == 11:
		print "insert string is: insert into 11, etc"
		params = {"filename":filename, "order_num":data[0], "piece_id":data[1], "constant":data[2], "description":data[3], "bin_id":data[4], 
		"profile_code":data[5], "colour_code":data[6], "length":data[7], "end_cut":data[8], "macro_1":data[9], "macro_2":data[10]}
		nam_q = "dwh_11"
	elif l_l == 12:
		print "insert string is: insert into 12, etc"
		params = {"filename":filename, "order_num":data[0], "piece_id":data[1], "constant":data[2], "description":data[3], "bin_id":data[4], 
		"profile_code":data[5], "colour_code":data[6], "length":data[7], "end_cut":data[8], "macro_1":data[9], "macro_2":data[10], "macro_3":data[11]}
		nam_q = "dwh_12"
	elif l_l == 13:
		print "insert string is: insert into 13, etc"
		params = {"filename":filename, "order_num":data[0], "piece_id":data[1], "constant":data[2], "description":data[3], "bin_id":data[4], 
		"profile_code":data[5], "colour_code":data[6], "length":data[7], "end_cut":data[8], "macro_1":data[9], "macro_2":data[10], "macro_3":data[11],
		"macro_4":data[12]}
		nam_q = "dwh_13"
	elif l_l == 14:
		print "insert string is: insert into 14, etc"
		params = {"filename":filename, "order_num":data[0], "piece_id":data[1], "constant":data[2], "description":data[3], "bin_id":data[4], 
		"profile_code":data[5], "colour_code":data[6], "length":data[7], "end_cut":data[8], "macro_1":data[9], "macro_2":data[10], "macro_3":data[11],
		"macro_4":data[12], "macro_5":data[13]}
		nam_q = "dwh_14"
	elif l_l == 15:
		print "insert string is: insert into 15, etc"
		params = {"filename":filename, "order_num":data[0], "piece_id":data[1], "constant":data[2], "description":data[3], "bin_id":data[4], 
		"profile_code":data[5], "colour_code":data[6], "length":data[7], "end_cut":data[8], "macro_1":data[9], "macro_2":data[10], "macro_3":data[11],
		"macro_4":data[12], "macro_5":data[13], "macro_6":data[14]}
		nam_q = "dwh_15"
	elif l_l == 16:
		print "insert string is: insert into 16, etc"
		params = {"filename":filename, "order_num":data[0], "piece_id":data[1], "constant":data[2], "description":data[3], "bin_id":data[4], 
		"profile_code":data[5], "colour_code":data[6], "length":data[7], "end_cut":data[8], "macro_1":data[9], "macro_2":data[10], "macro_3":data[11],
		"macro_4":data[12], "macro_5":data[13], "macro_6":data[14], "macro_7":data[15]}
		nam_q = "dwh_16"
	elif l_l == 17:
		print "insert string is: insert into 17, etc"
		params = {"filename":filename, "order_num":data[0], "piece_id":data[1], "constant":data[2], "description":data[3], "bin_id":data[4], 
		"profile_code":data[5], "colour_code":data[6], "length":data[7], "end_cut":data[8], "macro_1":data[9], "macro_2":data[10], "macro_3":data[11],
		"macro_4":data[12], "macro_5":data[13], "macro_6":data[14], "macro_7":data[15], "macro_8":data[16]}
		nam_q = "dwh_17"
	elif l_l == 18:
		print "insert string is: insert into 18, etc"
		params = {"filename":filename, "order_num":data[0], "piece_id":data[1], "constant":data[2], "description":data[3], "bin_id":data[4], 
		"profile_code":data[5], "colour_code":data[6], "length":data[7], "end_cut":data[8], "macro_1":data[9], "macro_2":data[10], "macro_3":data[11],
		"macro_4":data[12], "macro_5":data[13], "macro_6":data[14], "macro_7":data[15], "macro_8":data[16], "macro_9":data[17]}
		nam_q = "dwh_18"
	elif l_l == 19:
		print "insert string is: insert into 19, etc"
		params = {"filename":filename, "order_num":data[0], "piece_id":data[1], "constant":data[2], "description":data[3], "bin_id":data[4], 
		"profile_code":data[5], "colour_code":data[6], "length":data[7], "end_cut":data[8], "macro_1":data[9], "macro_2":data[10], "macro_3":data[11],
		"macro_4":data[12], "macro_5":data[13], "macro_6":data[14], "macro_7":data[15], "macro_8":data[16], "macro_9":data[17], "macro_10":data[18]}
		nam_q = "dwh_19"
		
	system.db.runNamedQuery(nam_q, params)
	print "Db insert complete"
	print "Batch count (i): " + str(i)
7

Sam, we added that in 7.9.10 which should be out shortly after ICC.

1 Like
8

I’ll re-visit this thread as I’m trying to re-factor some elements of this task.

Can a standard Scalar Prep use a ? for the column to select or just for WHERE and AND clauses?

Initial testing suggests it retuns the column name not the data in that column?

refactor query.png3799×729 127 KB

9

Parameter substitution with ‘?’ (and the corresponding named parameters in named queries) can only pass data to the query, not structural elements of the query. Keywords and schema/table/column/function identifiers are structural. The biggest reason for this is security – structural changes can result in different data types and transformations, as well as potential damaging subqueries. This is not an Ignition restriction, it is a restriction and the DB driver level (JDBC).

1 Like
10

OK, thanks. So this function has 9 variable/dynamic select columns. Are named queries the best route or are there any other tricks?

11

OK so how bad practice is this?

def query(x):
		maci = 8744
		mcn = x
		print mcn
		query_a = "select macro_"
		query_b = " from ignition.daewah_off where index_daewah_off = ? and ? is not null order by index_daewah_off asc limit 1"
		mcq = system.db.runScalarPrepQuery(query_a + str(mcn) + query_b, [maci, "macro_"+str(mcn)], "Xeon")
		print mcq

for x in range(1, 3):
	print "x: " + str(x)
					
	query(x)




>>> 
x: 1
1
A75.0;FW1-01
x: 2
2
A314.5;FW3-01
>>>
12

You're passing a column name to that parameter. Won't work as you think. It'll always be true -- it is the string value 'macro_x'. The part where you concatenate mcn into the query structure will work. But you can't do this in the client without legacy DB access. Concatenating to produce the query string is the very big security hole named queries are intended to close. If you really need this sort of thing, and you truly control the strings being concatenated (none of it from user string inputs), you could encapsulate this in a gateway message handler that returns the response.

13

Thanks for your advice. I know for this ‘maci’ that there are only 3 columns with data, the rest are null. Changing my for loop to 8 iterations resulted:


>>> 
x: 1
1
A75.0;FW1-01
x: 2
2
A314.5;FW3-01
x: 3
3
A589.0;FW1-01

x: 4
4
None
x: 5
5
None
x: 6
6
None
x: 7
7
None
x: 8
8
None
>>>

This is a conversion script to change one file ‘format’ into another. It’s completely behind the scenes at gateway scope with absouletly no user interaction.