1

I'm testing a feature in a Progress OpenEdge application that loads data into DB tables. Many of the fields in the table I'm testing are limited by our application to 256 characters, so I'm doing some edge case testing, trying to load in entries that are less than, equal to, and greater than 256 characters. After the data load, I query the DB and validate the results. I'm using the OLE DB provider for ODBC drivers through the testing program TestComplete. This is the query I'm using:

SELECT SUBSTRING(lname, 1, 30)
FROM pub.referral
WHERE lname LIKE 'valid%'

Before I run my test, the ODBC driver has no problem with this query. After I run it, though, there are fields in this table that have 256 characters, and the ODBC driver has problems with data of that length. I thought to avoid that by using SUBSTRING, but I still get a validation error when I try to use this query:

The specified query is invalid. Database engine reports: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Error in row

Informative! That's the error I usually see when there's data in a table that's longer than ODBC likes. Strangely, though, if I take out the WHERE clause, the query validates and returns data in 30 character chunks, like I specified in the SUBSTRING.

Is this happening because I have more characters than the ODBC driver expects in a field, or because of something else having to do with the WHERE clause (incorrect syntax, etc.?) How can I get around this? I would like to do this without changing settings through dbtool - it's important to me to have the particular settings that I have in my DB in order for my tests to be accurate.

Anastasya Lundquist
  • 600
  • 1
  • 7
  • 21

1 Answers1

1

What I suspect is that the engine is reading non-matching records to see if they match the WHERE condition. If the non-matching records have fields longer than their SQL-WIDTH / MAX-WIDTH setting, that might give the result you're seeing.

Doing the search without using a WHERE clause means every record is part of the result set and will get processed by the SUBSTRING() command before being output.

A way to test this is to do a WHERE with an equality match - the engine should only read records which match the WHERE spec, do the SUBSTRING(), and work as expected.

In terms of "getting around" this - either shorten the data or make the SQL-WIDTH or MAX-WIDTH setting bigger for those fields.

Here's a KB on the topic: http://knowledgebase.progress.com/articles/Article/P162121

Here's a KB on dealing with "too big" columns: http://knowledgebase.progress.com/articles/Article/P126779?q=SQL%3A+Data+truncated+and+Invalid+long+datatype+and+error&l=en_US&type=Article__kav&fs=Search&pn=1

Tim Kuehn
  • 3,201
  • 1
  • 17
  • 23
  • When you say "do a WHERE with an equality match", do you mean `WHERE lname = 'validlast1'` or something like that? I tried that and it still gave the error. I did find [this](http://tinman.cs.gsu.edu/~raj/sql/node22.html), though, that spells out the order of evaluation of a SELECT statement, and you're right - it seems it's reading _all_ the records in, then filtering them down. – Anastasya Lundquist Jul 03 '14 at 14:36
  • Yes - that's what I meant. The idea was to have the engine only process records it would display. I've done some more poking around and found a KB which may have a work-around to getting the column down to size. – Tim Kuehn Jul 03 '14 at 14:52
  • I tried the workaround in the second article you linked - adding a value to the registry - and now the error I get when using a substring is "Inconsistent types (7481)". I only get it with substring, not with select all, but regardless of WHERE clause. I found [this KB article](http://knowledgebase.progress.com/articles/Article/000046804), but it's a bit over my head, and there are no time datatypes in this table, so I'm not sure how to proceed. – Anastasya Lundquist Jul 07 '14 at 15:46
  • If your site is under maintenance, I'd suggest contacting PSC Technical support - they're pretty good at sorting out issues like this one way or another. – Tim Kuehn Jul 07 '14 at 16:04