Databases Errors into a table

Esme_Lopez · February 21, 2022, 8:21pm

Hello, Did someone know how to get the databases errors and others to create a table with a list?

Vision - Ignition 8.1

bkarabinchak.psi · February 21, 2022, 8:27pm

You mean you want to save the database errors from your code that occur throughout your program? As well as other python errors - save all this to a table?

Esme_Lopez · February 21, 2022, 8:30pm

Yes, I want to do that or show them in labels

bkarabinchak.psi · February 22, 2022, 1:32pm

You will need try and excepts all over the place then. To simplify things a bit at least, could make your own db module that calls the built-ins and logs to this table automatically. Something like

# module name is db or something like that
import system.db

def runNamedQuery(*args, **kwargs):
    try:
        return system.db.runNamedQuery(*args, **kwargs)
    except Exception, e:
        # Python error
        system.db.runNamedQuery("insertIntoErrorTable", {'exceptionType':'python', 'source':'query', 'error':e.cause})
    except java.lang.Exception, e:
        # SQL Error
        system.db.runNamedQuery("insertIntoErrorTable", {'exceptionType':'sql', 'source':'query', 'error':e.cause})
# Proceed to make similar functions with runQuery, scalarQuery, runPrepUpdate, etc

Then through your program instead of calling system.db.runNamedQuery you would call your own db.runNamedQuery (using the exact same arguments provided via the documentation for system.db.runNamedQuery) so that you get the logging of the errors for free to your database table.

As for logging regular scripting errors to a database, you are going to need to want to put all business logic into scripting functions in your library which is best practices anyways. Then you can do stuff like this on a property change event or button press or whatever triggers your script -

import traceback
import myModule

try:
    myModule.doStuff()
except Exception, e:
    system.db.runNamedQuery("insertIntoErrorTable", {'exceptionType':'python', 'source':'script', 'error':traceback.format_exc()})

Maybe there’s a better way to do it but this is what I can think of. I do think this may cause you more headaches than it solves however.

For one, if you’re testing this on a client, now you have to check a database table for all your errors instead of your Log Viewer or having the error bubble up - and having some errors bubble up is appropriate as you need someone to know when it happens.

Another issue with this particular issue would be the silent failures of a database query messing you up. You could do something like

result = db.runNamedQuery("myresults")
for row in result:
    # do stuff

Now with this method if db.runNamedQuery("myresults") has an error, result will get a None type which is not an error and everything will continue on as usual, which can make debugging harder - silent errors are a bad practice imo. If you want to silence a particular error because you expect it and know what it is that is one thing but to silence all errors across the board for a particular scripting function is a bad idea imo.

You could hypothetically get around this by raising an error inside your except clauses like

# module name is db or something like that
import system.db

def runNamedQuery(*args, **kwargs):
    try:
        return system.db.runNamedQuery(*args, **kwargs)
    except Exception, e:
        # Python error
        system.db.runNamedQuery("insertIntoErrorTable", {'exceptionType':'python', 'source':'query', 'error':e.cause})
        raise Exception(e)
    except java.lang.Exception, e:
        # SQL Error
        system.db.runNamedQuery("insertIntoErrorTable", {'exceptionType':'sql', 'source':'query', 'error':e.cause})
       raise java.lang.Exception(e)
# Proceed to make similar functions with runQuery, scalarQuery, runPrepUpdate, etc

but I don’t know I think this thing is starting to be kind of ugly and reeking of code smell.

The way I personally do my try/excepts is not around a particular system.* function but around my units of business logic. For example in my application I am working on now I have a function that calculates employees rates for the entire company for billing purposes. It’s initiated by a client sending a message to the gateway which then runs this -

def handleMessage(payload):
	"""
	Initializes tags that are used to display progress on GUI and does try/except of script with proper logging.
	All gateway message event handlers should be done in this manner.
	Args:
		payload: {data: dataset, clientId: clientId to send response back to}
	Returns:
		None, or logs error
	"""
	data = payload['data']
	clientId = payload['clientId']

	try:
		writeBaseRates(data)
	except Exception, e:
		LOGGER.error("Python error when running base rates script")
		LOGGER.error(str(e.message))
		LOGGER.error(traceback.format_exc())
	except java.lang.Exception, e:
		LOGGER.error("SQL error when running base rate scripts")
		LOGGER.error(str(e.message))
		LOGGER.error(str(e.cause))
	else:
		noErrors = True
	finally:
		if noErrors:
			msg = "Completed updating base rates without any errors."
			LOGGER.info(msg)
		else:
			msg = "Completed updating base rates with errors."
			LOGGER.warn(msg)
                #  Send success/failure to client so they know how it went
		payload = {'msg':msg}
		system.util.sendMessage('MyProject', 'handleBaseRateResponse', scope='C', payload=payload, clientSessionId=clientId)

Now in this sort of setup you could put SQL queries inside the except clauses to save them if you wanted to to your table.

Personally I think I would suggest against making your own db module and do your try/excepts around units of business logic. The first iteration I showed you of the db module has the issue of silent errors and will be very problematic. The second one that raises erorrs after saving to the db gets around that and I suppose could work but I think they’re might be issues doing things that way as well that I just can’t think of now.

Curious @pturmel what you think about this situation

pturmel · February 22, 2022, 2:02pm

This. This is good.

Esme_Lopez · February 22, 2022, 3:10pm

But first I should create a table with that parameters ? >> exeptionType, source, query and error

bkarabinchak.psi · February 22, 2022, 3:13pm

Yes you will need to design your own database table and write your namedQuery or query to track what you think is important regarding the error. This is not a built in functionality so its all on you how to design it.

Again, I would take my third approach with try/except clauses around units of business logic. It will catch database errors - that is what my except java.lang.Exception, e exception is for, and catch python errors with except Exception,e.Inside the except clauses is your opportunity to save the errors to your database table.

Esme_Lopez · February 22, 2022, 4:57pm

Thanks, I design the table but I think something is wrong, I can’t get the errors into the table

bkarabinchak.psi · February 22, 2022, 5:00pm

Post some code and post the error you get from it

Esme_Lopez · February 22, 2022, 6:21pm

#This is in a button to add the data into the table

import system.db

def runNamedQuery(*args, **kwargs):
try:
return system.db.runNamedQuery(*args, **kwargs)
except Exception, e:
system.db.runNamedQuery(“insertIntoErrorTable”, {‘exceptionType’:‘python’, ‘source’:‘query’, ‘error’:e.cause})
except java.lang.Exception, e:
system.db.runNamedQuery(“insertIntoErrorTable”, {‘exceptionType’:‘sql’, ‘source’:‘query’, ‘error’:e.cause})
system.db.refresh(event.source.parent.getComponent(“tableName”),“data”)

#I create a query with idLog, exceptionType, source and error (that is what has the table)
But nothing happens

bkarabinchak.psi · February 22, 2022, 6:30pm

A couple of problems.

*args is a special thing for supplying a list of arguments to a function, **kwargs does the same thing for keyword arguments. To actually use args and kwargs as an argument you would need to define

args = ['argument1', 'seomarguemnt2']
kwargs = {'keywordArgument1':'value1'}

and then call *args, **kwargs in a function like that. As of now even if you did call your function you will get that args/kwargs is not defined.

You are only defining the function (and doing it inside a button)
Put your logic inside a module of your scripting library as I am guessing you will want to use it in more than one place like this

image1231×277 42.5 KB
Then you would call this function from your button like

import db
params = {'someting':1}
db.runNamedQuery('someNamedQuery',params)

and that would take care of the logic.

But again I have to advise against this. I think you’re better off try/excepting around business logic.

Esme_Lopez · February 22, 2022, 7:39pm

Are the parameters exceptionType , source and error with a value?:
\params = {‘someting’:1}

I think I don’t understand that .

bkarabinchak.psi · February 22, 2022, 7:41pm

I just used those as an example. You have to define your own named queries. I just gave those as an example of what columns you might want to save.

Highly recommend going the first part of this course at least - Browse the Lesson Library at Inductive University though the entire course is worth it and you can get a credential for completing it.

pturmel · February 22, 2022, 7:43pm

Exceptions are objects. They won't go directly into a database. You'll need to convert to string, generally. Possibly with the backtrace if you want it. I recommend some basic python tutorials do understand how exceptions work.

kavya_shree · January 6, 2023, 11:01am

Hi,
Can u please tell me how to check SQL query is successful or not by message.
mean:
I am using insert query in my script.
I want to check this insert query is success or not.
is there any method to show success message?

please help.

Thank you

justinedwards.jle · January 6, 2023, 11:57am

You could follow the insert query with a SELECT count() query on the primary key. If it comes back as a 1, then show success.

kavya_shree · January 6, 2023, 12:29pm

Hi,
this is my insert query:

system.db.runNamedQuery("Insert_Line_Assignment",{"LINE":line, "MACHINEID":machineid, "SAPOrPreformatted textderNum":sapordernum, "PartNumber":partnumber, "Status":status, "StartTime":starttime})

this is working and inserting into database on button action.

I want to see this success insertion in script or as u said it should come 1.
I tried errors are coming

Transistor · January 6, 2023, 12:35pm

Tip: please format code using the </> button. That will preserve indentation (essential for Python) and apply syntax highlighting. You can fix your earlier posts with the 🖉 edit link.

e.g.,

system.db.runNamedQuery("Insert_Line_Assignment",
    {"LINE":line, "MACHINEID":machineid, 
      "SAPOrPreformatted textderNum":sapordernum, 
      "PartNumber":partnumber, "Status":status, "StartTime":starttime})

pturmel · January 6, 2023, 1:13pm

Is the NamedQuery set to type "Update" ? (It should be.) Then the return value will be the number of rows inserted. Per the documentation.

Capture and check the return value.

kavya_shree · January 9, 2023, 5:05am

Hi,
Yes, this is named query,

line = event.source.parent.getComponent('select line').selectedStringValue
sapordernum = event.source.parent.getComponent('sapordernumber').selectedStringValue
partnumber = event.source.parent.getComponent('select partnum').selectedStringValue
status = event.source.parent.getComponent('status').selectedStringValue
starttime = event.source.parent.getComponent('startdate ').text
machineid = event.source.parent.getComponent('machineID').text
endtime = event.source.parent.getComponent('end date').text

if event.source.parent.getComponent('select line').selectedStringValue == "":
	system.gui.messageBox("Please Select Line")
    
table = system.db.runQuery("select LINE,Status from dbo.[RAPTOR_LINECHANGE_ASSIGNMENT] where LINE='"+line+"'")
if len(table)>0:
	for row in table:
	    status = row["Status"]
	    print status	    
	    if status == 'Completed':
	    	system.gui.messageBox("Already Line is Assigned.")	
	    	break    	
	    else:
	   		system.db.runQuery("update dbo.[RAPTOR_LINECHANGE_ASSIGNMENT] set Status ='Completed', EndTime='"+endtime+"' where LINE='"+line+"' and Status='In Progress'")
	   		system.gui.messageBox("Line Updated Successfully.")	
	   		break
#else:       
elif event.source.parent.getComponent('status').selectedStringValue == "In Progress":
    #event.source.parent.getComponent('status').selectedLabel == "In Progress":
	event.source.parent.getComponent('end date').text == "NULL"
	system.db.runNamedQuery("Insert_Line_Assignment",{"LINE":line, "MACHINEID":machineid, "SAPOrderNum":sapordernum, "PartNumber":partnumber, "Status":status, "StartTime":starttime})
	l = system.db.runScalarQuery("SELECT LINE FROM dbo.[RAPTOR_LINECHANGE_ASSIGNMENT] WHERE SAPOrderNum='"+sapordernum+"'")
	system.gui.messageBox("The selected line is: %s" %l)

---------------------------------------------------------------------------------------------
![image|448x500](upload://7PsFzL9LOP6os5zq4ha7ORmZ57R.png)
 this is my screen, Inserting all these details in to data base. above code is written in "Assign" Button

![image|690x54](upload://e98XWfF7ydK8hEmfhjoNR6zbvUe.png)

My requirement is
I have first dropdown 'Select Line' = R17 and R22
second dropdown 'select status": 'In Progress' and 'Completed'
If any of this line is in 'In Progress ' in database I want my my query to update to 'Completed"
................................................................
for first drop down , i have written the code in Property change event. here is the code
if event.propertyName == "selectedStringValue":
selNewValue = event.newValue
print selNewValue

selectedLine = event.source.selectedStringValue
selectedStatus = event.source.parent.getComponent('status').selectedStringValue
#print selectedLine
#table = system.db.runQuery("select top 1 MACHINEID,SAPOrderNum,PartNumber,Status,StartTime from [dbo].[RAPTOR_LINECHANGE_ASSIGNMENT] where LINE = '"+selectedLine+".strip()' order by StartTime desc")
table = system.db.runQuery("select top 1 MACHINEID,SAPOrderNum,PartNumber,Status,StartTime from [dbo].[RAPTOR_LINECHANGE_ASSIGNMENT] where LINE ='"+selectedLine+"' order by StartTime desc")
#table = system.db.runQuery("select top 1 MACHINEID,SAPOrderNum,PartNumber,Status,StartTime from [dbo].[RAPTOR_LINECHANGE_ASSIGNMENT] where LINE='"+selectedIndex+"' order by StartTime desc")
for row in table:
    machineid = row["MACHINEID"]
    sapordernumber = row["SAPOrderNum"]
    partnum = row["PartNumber"]
    status = row["Status"]
    starttime = row["StartTime"]
     #date = dateFormat(starttime, 'yyyy-MM-dd 00:00:00')
    print starttime
    print status

    if status == 'Completed' and selectedLine == row["LINE"]:
   		system.gui.messageBox("Line is Comlpleted")
   		break
    else:
		system.gui.messageBox("The Line is in Progress,Please Comlplete")
		break
		event.source.parent.getComponent('machineID').text = machineid
		break
		event.source.parent.getComponent('select partnum').selectedStringValue = partnum
		break
		event.source.parent.getComponent('status').selectedLabel = status
		#event.source.parent.getComponent('status').selectedStringValue = status
		break
		print status
		break
		event.source.parent.getComponent('startdate ').text = "2022-12-29 01:00:00"
		break


here Error is ,
after assigning/Inserting values, message is not showing. 
after selecting line which is already there in database, its showing error message.
after that I want all data to be come automatically in all components.

I am confused.

please help.

trying from many days