Column keyindex in table PUB.oeeh has value exceeding its max length or precision

[progrmr]

I am writing a .NET windows application which utilizes the DataDirect 4.1 SQL 92 ODBC Driver.

I am getting a strange error when I run select statements against some tables. I can query _user just fine, but when I run a select (select * from pub.oeeh) on a different table I typically get this error:

System.Data.Odbc.OdbcException: ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]Column keyindex in table PUB.oeeh has value exceeding its max length or precision.

I am running this query as sysprogress so there should'nt be any issues with permission. Now if I have the DBA run this query from character mode, he gets the result set without error.

What could be causing this strange error??

 

[dcheatham]

Your SQLLength value is too short. Progress allows variable lenght data behind the scenes. The length of a fields data exceeds the defined length to be returned from SQL. There is a progress utility called dbtool that can analyze the configuration of your database and reset the lengths for SQL. As with any tool, make sure you have good backups before using.

 

[progrmr]

Thank you for the reply.

However I think I might be doomed here. We are talking about a HUGE enterprise database with all the business data in it. I doubt I will ever be able to get the unix/progress guys to modify anything.

Ugh...

 

[dcheatham]

That is the beauty of the utility. Are you using NxTrends SX.Enterprise? If so, you can go to customer care and download a document, "Fixing SQL Lengths with dbtool". I think that's the name.

This tool can be run while the database is up and can analyze the entire database. This will require a large amount of system resources and time to complete. A good estimate would be to double the amount of time an online backup takes. I caution to have the backup as it corrupted my database on one run. I have run it no less than six times on my database and only had the one issue. Better safe than sorry.

The other option is to figure out which field is causing the problem and exclude it from returning.

 

[progrmr]

Actually we are upgrading to SX.Enterprise.

I'll have them take a look at this - thanks again!

 

[Syn]

Sorry to Necro-post but my problem is the same as this one. The only difference is that I'm using 9.1c therefore dbtool is not availible in proenv. Is there another way to report on the fields that exceed there sql length quota?

 

[Casper]

Did you look at KB P13348?
It's a Progress programm which detects and reports problematic sql widths.

HTH,

Casper.

 

[Syn]

Thx Casper,

That's great, how do I use it? I'm RTM at the moment trying to learn Progress.

BackGround: We use a Progress database with a 4GL interface that was bought in. Rather than paying a small fortune for addons I'm trying to dev some myself through odbc. I'm quite litterally 'Junior Progress Talker' :blush:

I have no idea how to compile and run 4GL code, though I may by the time I've finish with the CD. Is there a SAM's 24 hour guide?:lol: It may also be the case that we have a cut-down / client-install of Progress without dev capability, How do I tell?

Many thanks for your time!

Syn

 

[Casper]

To find out what kind of Progress install you have you must look at $DLC (progress install dir). There's a file called progress.cfg if you double click it then you can see what kind of license you have. e.g runtime, query, provision, provision plus.

Another mehod is to choose start, run and then showcfg then you see the same license. If you don't have a provision or developer or query license then you can't run the program uncompiled. Another problem with a runtime license is that you can't modify the sql-width unless you got a compiled program to do that for you.

regards,

Casper.

 

[Syn]

Cheers Casper,

I can get into the Data Dictionary and change the length there. Though I can only run compiled dot r files on our install, you've given me enough information to parlay with the software vendor and get them to send me a tool as I cannot compile here.

During my research I found 4GL can create XML, I'd love to be able to compile myself to utilise this, but hey... I can't have everything my own way

yet again..

Cheers and have a nice weekend to

Syn

 

[ccotter3]

4GL Triggers and ODBC

I believe I am correct in stating that no 4GL triggers will run when accessing a Progress DB from .Net, VB, etc. using ODBC since the 4GL triggers require a 4GL client to execute. If you are running a .Net OpenClient configuration you would be firing 4GL triggers since the Progress side of the OpenClient code is running in AppServer which is a 4GL client. Just wanted to verify my perception and add some useful discussion to the thread.

Also, in the SX.e realm I would be careful about creating applications that update the database directly since many processes in the application have substantial business logic associated with them. NxTrend (now InfoR) provides SXAPI which is an interface to this business logic. It is also possible to integrate with SX.e in other ways, including through eSales.