We had an issue a few weeks ago after archiving data from a few of our MSSQL databases. The "archived" databases had tables with the same name as the "active" databases, and some of our system.db.* queries began to fail if they did not have the database and schema specified within the inline query. For example, this query would fail:
SELECT [column] FROM [table]
But this query would succeed
SELECT [column] FROM [database].[dbo].[table]
The python statements in Ignition had the correct database specified in the statement (e.g. system.db.runScalarPrepQuery(query,args,DB) where DB is the correct database for the active data), but the default database for the Ignition project is a different database.
I suspect that if the queries ran against the default project database, SQL wouldn't know which database (i.e. "Active" or "Archived") to use.
Here is my question: Is it possible that the system.db.* queries will use the default database specified in the project rather than the database specified in the system.db.* statement itself?
I should clarify: we have had multiple programmers review the code, and we are confident that the correct database was specified in the system.db.* statement. However, when the query was run, it would return an error until we added [database].[dbo].[table] in the FROM statement.
If the issues is not that the script is using the default database from the project, is there another explanation as to why the table was not able to be located based on the Ignition database connection specified in the parameters?
Look at the database connection's extra parameters. Check for schema overrides. Check for the schema defaults in that user's account settings on the DB side.
Have you tried using system.db.runPrepQuery() followed by ds.getValueAt(0,0) to emulate runScalarPrepQuery ? To eliminate the possibility of a bug in just that function?
Here is one example of code where we had an issue. Again this issue started as soon as we had a new database called ARCHIVED JNAP Broadcasts that had the same tables as the JNAP Broadcasts database.
DB = 'JNAPBroadcasts'
def nextCSNQuery(currentCSN):
# Set the variables
query = '''SELECT TOP (1) ProcessSequence
FROM LoadDetail
WHERE ProcessSequence > ? and assigned <> 99
ORDER BY ProcessSequence asc'''
args = [currentCSN]
newCSN = system.db.runScalarPrepQuery(query,args,DB)
In order to fix this, the code now reads as follows:
DB = 'JNAPBroadcasts'
def nextCSNQuery(currentCSN):
# Set the variables
query = '''SELECT TOP (1) ProcessSequence
FROM [JNAP Broadcasts].[dbo].[LoadDetail]
WHERE ProcessSequence > ? and assigned <> 99
ORDER BY ProcessSequence asc'''
args = [currentCSN]
newCSN = system.db.runScalarPrepQuery(query,args,DB)
The Database Connection in the Gateway looks like this:
I'm almost certain this isn't the issue because the Global Variable was used by other scripts that were working as long as the database and schema were referenced before the table name.
I do wonder if this could somehow be part of the issue. We didn't try it with just the schema and table reference. Our team stumbled across the solution when they tried to run it in the console and received some sort of odd error that led them to adding the database and schema to the FROM statements.
Thanks for all of the feedback. For now, we've specified internally that all queries need to have the full database, schema, and table names for our queries, and everything seems to be working well. I just wanted to make sure that there wasn't something I was missing.
It's definitely not a bad idea, but here is an example of the exact code that was failing for another query. We literally set the database variable 3 lines before we use it in this case. I'm quite confident in this specific case of the database variable, and it is correct for the table we were trying to query.
