Database used by system.db.* queries

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?

Yes, if they are running within the project itself and the db connection is not specified within the function.

No.

All said, if you are specifying the db connection, then the connection does not appear to be pointing to the correct database.

Or the database has unexpected schema search/fallback rules that are confusing the issue.

1 Like

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?

Double check the database connection configuration for that DB in the gateway. Confirm that it is pointing at the correct DB.

2 Likes

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 just [dbo].[table] ?

1 Like

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?

Are you trying to pass any arguments using keywords? That isn't supported everywhere (though I understand that IA plans to fix them all).

{ You haven't shown your actual script in this topic yet. }

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:

Also, I should mention that it wasn't just for runScalarPrepQuery; we had this same issue for runPrepQuery and runPrepUpdate as well.

Consider logging your arguments just before each call, to see if anything is smashing that global variable.

I am otherwise out of ideas. Except, maybe, fire Microsoft?

3 Likes

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.

That just means that your cluster is set up for cross-database access. I think a broken DB variable is a very likely cause.

Could spaces in a database name cause issues? We run the rule of no spaces/underscores only for DB/table/column names so I'm not sure.

No idea. Might need to be URL-encoded in a DB extra parameter.

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.

I really think you should log the DB global variable just before your system.db. calls.

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.

		database = 'JNAPBroadcasts'
		query = "SELECT LoadID, PartNumber, VINNumber, PlantCode FROM LoadDetail WHERE ProcessSequence = ?"
		args = [seqNum]
		pydata = system.db.runPrepQuery(query, args, database)

Here is the fix we had to implement to get it to work.

		database = 'JNAPBroadcasts'
		query = "SELECT LoadID, PartNumber, VINNumber, PlantCode FROM [JNAP Broadcasts].[dbo].[LoadDetail] WHERE ProcessSequence = ?"
		args = [seqNum]
		pydata = system.db.runPrepQuery(query, args, database)