ODBC error processing query from Indusoft

[shearerg]

Hi all,

In developing an Indusoft (HMI/SCADA) application which sources data from an Ingres database I've come across an annoying problem. When configuring queries for an Indusoft 'grid' object with a 'condition' (WHERE clause) filter, all I get is an error returned from Ingres. I suspect there may a problem with construction of the query string within Indusoft, but really don't know how to interpret ODBC trace files. The trace file for a basic query is attached. The query is intended to be:

select machine from machines where machine = 'NM10'

The query works fine if no filter condition is specified. In Indusoft, the various components of the query string are picked up from configured fields within the development interface. That is, the table name, field names and 'where' condition ... machine = 'NM10' ... are entered into various data entry fields within the configuration sheets for the grid object. Indusoft then gathers all the elements and constructs the complete query. In general this works really well ... but apparently not for conditional filters (WHERE clauses) with Ingres.

I've attached the odbc log for the example query above. The Ingres error log entry differs with database release. A brief extract from a Ingres Linux Version II 3.0.2 (int.lnx/105) release follows, although for a different query than the odbc log:

pid=5704,conn='JITNWMDB::jitnwmdb/INGRES'
JITSNWM ::[32777 , 581b7e80]: Description:
JITSNWM ::[32777 , 581b7e80]: Query: open ~Q cursor for SELECT m
achine, jobnumber, diameter FROM plans
WHERE machine = ~V for readonly
jitsnwm ::[32777 , 581b7e80]: Thu Jun 25 09:25:28 2009 Segmentation Violation (SIGSEGV) @PC 408a4484
ESP 5dcacb40 EBP 5dcacb98 ESI 6032ec26 EDI 642b029a
EAX 000009c0 EBX 408cef4c ECX 6032ec2e EDX 00000000
DS 0000007b ES 0000007b SS 0000007b

I'm not sure if the Ingres Linux Version II 9.3.0 (int.lnx/143)NPTL release is more or less informative? It doesn't display the SQL string.

]: Fri Jun 26 11:09:04 2009 E_PS037B_UNORM Error normalizing data value
(27,10)-> (27,34)
CATNWM::[40864, 4868 , a9df2100, scsqncr.c:3978
]: Fri Jun 26 11:09:04 2009 E_SC0207_UNEXPECTED_ERROR Facility returned an
undocumented error.
CATNWM::[40864, 4868 , a9df2100, scsqncr.c:3994
]: ULE_FORMAT: sc0e.c:235 Couldn't look up message 0 (reason: ER error 10
902)
E_CL0902_ER_NOT_FOUND No text found for message identifier
CATNWM::[40864, 4868 , a9df2100, scsqncr.c:3995
]: Fri Jun 26 11:09:04 2009 E_SC0215_PSF_ERROR Error returned by PSF.
CATNWM ::[40864 , 4868 , a9d04580, pslsscan.c:4696

Any suggestions on the likely cause of the problem would be appreciated.

Regards,
Greg Shearer

[thoda04]

Hi Greg,

The query is more accurately "select machine from machines where machine = ?" with a parameter passed of a Unicode string (presumably 'NM10'). The error log message "E_PS037B_UNORM Error normalizing data value (27,10)-> (27,34)" is from the Parser on the DBMS server side. "Normalizing" may mean that the Parser is trying to transform the Unicode string into an equivalent sequence of Unicode characters of the same representation so that the comparing, searching, and sorting of the Unicode characters will be consistent.

I don't why the Parser had a problem normalizing, but I hope these clues help others that might have more information.

Hope this helps,
Dave

[loera01]

Hi Greg,

Based on the ODBC trace, it looks like you are connecting to the Linux server from a Windows client. As Dave pointed out, the call to SQLBindParameter() shows that the ODBC is attempting to pass a Unicode string to a Unicode column in the "machines" table. Furthermore, the column size argument shows a character length of 4, and a byte length of 8 is passed in the length indicator argument. Assuming the value for variable used for the dynamic parameter was something like "NM10", this is all fine.

Even if the target table uses a varchar for "machine" instead of nvarchar, on an Ingres 9.3 database, this ought not to be a problem. The Ingres database converts the Unicode string to multi-byte silently.

Just in case, and assuming the target type is actually varchar, not nvarchar, you might want to try specifying a parameter type of ASCII in your application, if this is possible. If nothing else, this may help shed some light on the problem.

Are you sure you have filled your Unicode string with a valid Unicode value? What is the value of II_CHARSETxx on the client and server? I'm especially interested if UTF8 has been specified as the character set anywhere.

Regards,
Ralph

[kschendel]

If you can arrange to feed a "set trace point ps147" sql statement to the session before it gets the error query, it ought to dump the bad data to the DBMS log. (You'll also need to ingsetenv II_DBMS_LOG 'full-path-to-some-directory-writable-by-user-ingres' and stop/restart the installation, if you don't already have II_DBMS_LOG set. Do an ingprenv to see if it's set.)

As best as I can tell, this error is saying that it got some bad unicode data. I suppose the database is unicode-enabled? Does the query work if you run it in the sql or isql terminal monitor? Does the query work in the terminal monitor if you say it as:

select machine from machines where machine = U&'NM10';

[shearerg]

Dave/Ralph/kschendel,

Thanks very much for the replies ... and you all seem to be on the right track.

kschendel,
Just tried the sql suggested within isql ... and exactly the same error results!
Without the U& prefix all behaves correctly ... which is what I'm used to!
I'm away from work for a couple of days but will look at a trace, if required, when I return.

Ralph,
II_CHARSETII is WIN1252 on the client and ISO88591 on the server, which I'm sure are the standard settings. The column format on the server is char(8), and in Indusoft is either within a string 'tag' or simply a quoted string. Also correct ... Windows client and SUSE Linux server.

So, am I right in summarising that it appears Indusoft is attempting to pass a Unicode string to Ingres, intended to match data which is not Unicode? I certainly haven't made any adjustments to a 'standard' Ingres installation in regard to Unicode support. Are there any (not too disruptive!) steps I could take at the server end to get around this? Alternatively, could I try to modify the Indusoft 'condition' expression to ensure correction at the server end? Looks like it's back to the sql manual!

Is it likely that Indusoft is incorrectly contructing the query string? I so, its unlikely I'll be able to overcome this problem ... other then waiting for a patch or new release ...

The Indusoft documentation makes several mentions of managing both ASCII and UNICODE characters. However, in other sql functions I haven't experienced any problems. This attempted configuration of the 'grid' object is the first real sql problem I've uncovered. Any further suggestions will certainly be appreciated!

Thanks,
Greg Shearer

[kschendel]

Originally Posted by shearerg@onesteel.com

Just tried the sql suggested within isql ... and exactly the same error results!
Without the U& prefix all behaves correctly ... which is what I'm used to!

When you tried it in isql with the U& prefix, did you get just the E_PS037B error, or did you get other error indications as well? I would have expected to see more, but it may be that the underlying ADF error code is being swallowed up.

My guess is that the database wasn't created as a unicode-capable database. If you can recreate the database, the simplest solution might be to create it as such; there's a createdb flag (actually two, one for NFC, one for NFD). You can create a database as unicode-capable and it doesn't impact performance to any significant degree, as far as I know; nor does it require you to use unicode in the database, it simply allows it. I am not sure of the theory behind the distinction between unicode-capable and non-unicode-capable databases, but character sets are an area I've always tried to ignore.

Or, if you can convince your front-end tool to not do unicode things, that would be even better.

[shearerg]

Hi there once more,

I haven't checked since this morning, but when I did the error indications appeared identical to those already posted. This didn't surprise me at the time, as I thought it pretty conclusively identified the cause of the problem.

Also, I can fairly certainly state that the database wasn't created as Unicode-capable as:
1. I created it, and
2. ... I've also always tried to ignore character sets, as they haven't been relevant to my systems

Anyway, in this particular case, recreating the database as Unicode capable shouldn't be an issue, as it is really still in development ... but I'll do some reading on it first. I'm still a bit concerned that the application may not be constructing the query correctly, in which case rebuilding the database could be a waste of time.

I'll also investigate disabling Unicode within the application ... but don't fancy my chances!

Will let you know how I go!

Thanks,
Greg

[skortier]

Greg,

We are doing some investigation on our end. While I am not familiar with the Ingres Database, there are others in our company that may be.

If we find anything that looks promising we will let you know.

We aim to make your project easier! And we really appreciate your business.

Please make sure you have the latest version: ver 6.1 SP5 with Patch1. Patch 1 came out a couple of months ago.

If you feel this is an InduSoft issue, please contact our support directly at support@indusoft.com.

skortier
InduSoft, Marketing Communications Manager
Visit the InduSoft web site here

[loera01]

Hi Greg,

I'm happy to see that Indusoft has taken the trouble to assist on this issue. It's very interesting to see how the Community works sometimes.

It does sound like you and Karl put your respective fingers on the problem. The Indusoft application is attempting to pass a Unicode string to a database that does not support Unicode. I'm a bit surprised by the DBMS error, though. I would have thought that the DBMS would have kicked out the query instead of crashing the session with this rather abstract error message. So it does seem like there is a DBMS component of this problem that requires further investigation.

Perhaps skortier can provide instructions on setting the data type of an Indusoft string to ASCII or multi-byte. Regardless, changing the database characteristics to Unicode support should help resolve the problem.

Regards, Ralph

[shearerg]

Skortier,

I'm also very pleased to see your response. As Ralph said, it really does show how well our online communities can work. Just FYI, I do have the SP5 release and patch installed. Also, I have been documenting the issue on the Indusoft forum. Assuming a database rebuild corrects the problem, I agree with Ralph that the real problem is with neither Indusoft or Ingres ... but more associated with my lack of experience with Unicode character sets.

If that is the case, some additional notes within the Indusoft documentation may be warranted, or perhaps the option to allow/prevent use of Unicode characters? Also some more informative information may result within the Ingres error log. In the end ... we should all be happy!! Anyway, I'll try the database rebuild before getting too excited ...

Also, although I would like to resolve this issue, if all else fails I do have a work-around. That involves using a standard query to populate a class tag array, and displaying the array within the grid object. I know this approach works, as I have used it before. However it has the downside of potentially consuming a large number of additional tags.

This issue aside, I have to say that I'm quite excited by the prospect of using such powerful products as Ingres and Indusoft together within industrial systems. I think I've already mentioned this in an earlier post. But seriously, this particular combination together with current industrial PC technology (especially touch panel PCs), provides the opportunity to construct very direct interfaces between shop floor equipment, process support & control databases and corporate systems, at what I would suggest are historically low costs.

Compared to the kind of systems I've been constructing over the last 23 or so years (embedded proprietary single board computers, serial network protocols to host applications, serial terminal devices), the power and flexibility offered by this kind of technology pairing is simply amazing! For example ...
using Ingres and Indusoft I can:
- collect data from and provide supervisory control of a wide range of machinery
- talk directly to our corporate SAP system utilising Visual Basic Script, ftp etc
- record data directly into Ingres for performance and production measurement
- display/utilise/update data held in other associated shop floor Ingres databases
- utilise the flexibility of Ingres to make relatively rigid corporate data downloads accessible
- provide graphic, flexible and informative operator interfaces

From an industrial systems point of view, I see Ingres and Indusoft as both being excellent products, but for me the combination is more then the sum of the two parts! SCADA is great for shop floor use, but generally doesn't provide easily accessible performance data. Shop floor databases are potentially great resources, but due to manual recording etc can be unreliable. However, data collected frequently, reliably and automatically from the manufacturing process .... is difficult to ignore!!!

I just wish I was a bigger potential user .... Unfortunately no one will make their fortune from any systems I put in place, as the scale is relatively small!

Here ends the sermon

Regards,
Greg Shearer