Insert Into Sql Script Problem

Hello all,

I have a script that running almost 2 months without any problem.
But something is happened on yesterday and script isn’t working now. You can see the diagnostic details in the below. My code is exactly the same since the day it was written and I haven’t changed anything.

But why is this happening? I see some fault related to java.base with (Unknown Source) but I couldn’t understand. I don’t have any problem with other tables in the same database, the problem is only in this table.

Show the code that produces that SQL. (And you really should be using runPrepUpdate, not runUpdateQuery.)

After you paste the code into the forum, highlight it all and click the “preformatted text” button so it will show indentation properly.

Also post the full error. Normally there is more specific information that tells you what went wrong - some column didn’t like some value that was inserted, a key constraint was violated, table doesn’t exist etc. Paste the full error and we can help you parse it.

Thank you for your answer, but already all diagnostic details in here. Is there another place to check the details of errors?

My codes are as following…
Line 42 is starting with “system.db.runUpdateQuery”… Also line 15 already called to “APIcall” function.

Thank you for your interest…

def APIcall (last_updated):
	#create param dictionary
	L2Lparams = dict()
	L2Lparams['offset'] = 0
	L2Lparams['lastupdated__gte'] = last_updated
	
	database_name = system.tag.read('[.]SQL Database Name').value
	
	updated = False
	while True:
		return_value = L2LTR.getKaizen(L2Lparams)
		if return_value['success']:
			if len(return_value['data']) > 0 :
				kaizens = return_value['data']
				
				for kaizen in kaizens:
					record = system.db.runQuery("select L2L_id from L2L_Kaizens where L2L_id='%s'" %kaizen['id'], database_name)
					kaizen['line_ids'] = (str(kaizen['line_ids'])[1:-1]).replace('L','')
					kaizen['area_ids'] = (str(kaizen['area_ids'])[1:-1]).replace('L','')
					kaizen['machine_ids'] = (str(kaizen['machine_ids'])[1:-1]).replace('L','')
					if record.getRowCount() > 0:
						system.db.runUpdateQuery("update L2L_Kaizens set L2L_number='%(number)s', L2L_category='%(category)s', L2L_status='%(status)s', L2L_status_date='%(status_date)s', L2L_owner='%(owner)s', L2L_owner_assigned_date='%(owner_assigned_date)s', L2L_priority='%(priority)s', L2L_complete='%(complete)s', L2L_complete_date='%(complete_date)s', L2L_closed='%(closed)s', L2L_closed_date='%(closed_date)s', L2L_created='%(created)s', L2L_createdby='%(createdby)s', L2L_machine_ids='%(machine_ids)s', L2L_line_ids='%(line_ids)s', L2L_area_ids='%(area_ids)s', L2L_submittedby='%(submittedby)s' where L2L_id='%(id)s'" %kaizen, database_name)
					else:
						system.db.runUpdateQuery("INSERT INTO L2L_Kaizens VALUES ('%(id)s','%(number)s','%(category)s','%(status)s','%(status_date)s','%(owner)s','%(owner_assigned_date)s','%(priority)s','%(complete)s','%(complete_date)s','%(closed)s','%(closed_date)s','%(created)s','%(createdby)s', '%(machine_ids)s', '%(line_ids)s', '%(area_ids)s', '%(submittedby)s')" %kaizen, database_name)
				
			if int(return_value['limit']) == len(return_value['data']):
				L2Lparams['offset'] += int(return_value['limit'])
			else:
				updated = True
				break
		else:
			break
			
	return updated

Where are you running this in? Gateway tag event? Check your server logs and you will see a full diagnostic - what you’re seeing there is truncated.

If it’s a client tag event, check the client diagnostics.

If you are testing this on a development server and therefore don’t mind fake data, the easiest thing to do - copy the executed code that is causing the error from your Diagnostic tab - the `system.db.runUpdateQuery(“INSERT INTO L2L …”) and run it in your script console - you will get a full error trace that way.

Once you get the full error trace it will make debugging much easier. The truncated error doesn’t give enough information on it’s own to figure out what specifically went wrong.

1 Like

This is very fragile. If your kaizen lists don’t come out to exactly the right number of columns for that table, it will blow up. And if any entry doesn’t convert to string exactly as expected, the SQL will have a syntax problem. It isn’t clear which of these two is the problem.

First, stop using string conversion to construct SQL. Use a constant string with explicit column names and question marks where the values go. Then construct a list of values that go with the question marks. Submit with .runPrepUpdate.

3 Likes

Yes, it’s working on gateway… you’re right i’ll write some log and i’ll follow step-by-step. thank you!

Thank you for your advices…
I’ll update my code according to these and i going to try again.

I mean the full stack trace should be on the gateway. You should at some point see some message from the database about the particulars of what’s wrong.

Follow @pturmel 's advice because odds are good it is something to do with the construction of the query and you are best to let .runPrepUpdate to construct the query and sanitize inputs for you.

My second guess is that some value in a VARCHAR column has become too long for the column definition or some type of constraint violation.

1 Like