Databases Errors into a table

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

Vision - Ignition 8.1

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?

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

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

This. This is good.

1 Like

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

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.

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

Post some code and post the error you get from it

#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

A couple of problems.

  1. *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.

  1. 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

  2. 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.

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

I think I don’t understand that .

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.

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.

1 Like