I’m trying to build a custom Store & Forward component in a project that will take test data from a local MS SQL DB and insert it into a remote MS SQL DB in an Azure Cloud server.
One of the fields in the data being copied is the byte data of a PDF report in the form of a VARBINARY(MAX) data type field.
It looks like that data is being truncated when I try to build the update query for the insert to the remote database.
The reasons for using Ignition for this task are numerous and outside of my control, so this is the method I’ve been directed to use (no SQL to SQL solution), so recommendation otherwise, though appreciated, won’t be helpful here. In short, I have to use Ignition to do the S&F/Replication function.
The details are as follows:
- A flag exists in the test table indicating that the record has, or has not, been replicated to the remote server
- A timed script executes and queries for the next non-replicated record
- The data in that record is assembled in a query in the Gateway timed event script and if the remote db is available, the data is inserted into the remote table
Currently the insert fails because the binary data of the PDF (varbinary) is being truncated.
My thought is that this may be due to size limitations in the variables in python/Ignition.
The record I’m testing with has 209 KBytes in the PDF report field.
It is populating a variable as an array.array of signed characters (array(‘b’, [37, 80, 68,…).
The logic populates a parameter list for a runPrepUpdata call.
The PDF data is the last item in that parameter list.
The attached image is the java exception being logged with a note in red.
Does anyone have any ideas as to why the data is truncating (if that is what is happening), or suggestions on what else may be the issue. Also, if there are better method for implementing this kind of functionality, I’m all ears!
BTW, when I return the PDF byte data in the Script Console, it looks like the data is truncated. That is the main reason I’m guessing the issue is a truncation problem.