Error Using RIGHT or LEFT with ODBC question

smason

New Member
#1
I'm trying to use an ODBC query in Microsoft SQL Server Manager Studio to get data from an Epicor Progress DB.
The driver we have to use is OpenEdge 102a / DLL file is pgoe1023.dll if that helps.

SELECT
c.Company,
c.CallSeqNum,
c.CallDesc,
RIGHT(c.CallText, 4000),
c.SalesRepCode,
... many other fields ...
FROM MFGSYS.PUB.CRMCall AS c

However when I run that I get an error:
Error: Column "right(CallText,4000)" cannot be found at the datasource. select-error.png

The reason I have to do a right statement is because that field (CallText) has a large number of characters and the Open Edge driver doesn't seem to allow more than 4000 characters. I think the actual DB field can be up to 32000 chars but for whatever reason the driver only supports 4000 chars.

So how can we select with right or even left.
 

Cringer

ProgressTalk.com Moderator
Staff member
#2
I suspect the SQL width on the Progress database is incorrectly set. Formats on a Progress database are purely default display formats. The underlying engine allows you to put anything in there as long as it doesn't hit the physical limits of the datatype. You can override the default display format in the 4GL. But this means that it's possible to put more data into a field than the field is configured for passing across to SQL. There are tools out there to calculate the SQL width for each and every field, but if it's just this one, then ask someone to fix the value on the database to whatever you believe the maximum to be.
Here are some instructions that include how to change the field width: Progress KB - How to adjust the SQL width of a field migrated with DataServer for MS SQL Server
 

smason

New Member
#3
I think I might have found the field with this Data Administration > Data Dictionary thing.

What am I looking at? Width / Format?
. memo-field-width.png

Is my error saying that there is data stored in the database that is longer than the width?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
#4
What am I looking at? Width / Format?
"Width" is the SQL width, the maximum length that can be returned to a SQL client without error from a character field. It corresponds to _field._width. It defaults to two times the display format.

"Format" is the default ABL display format. It corresponds to _field._format. It can be overridden in code and only governs data display, not storage. It applies only to ABL clients.
 
Top