Error in ODBC connection

The version might be important too know for this issue.

Also, how does that table look like?

Mike,

I'm on version 10.1B.  I'm not sure what you are looking for when you say "Also, how does that table look like?".  Let me know and I will get you the information.

Thanks,

Jody

say "Also, how does that table look like?". Let me know and I will get you the information.

Maybe a field and index report from the dictionary?

Mike,

Attached is a pdf of the data dictionary report.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/26/InvcDtl_5F00_Report.pdf:550:0]

Thanks,

Jody

I guess one of your CHARACTER fields in that table contains more characters than the defined SQL width of that field. That's not an issue for the 4GL (variable field length) but critical for ODBC clients. Use the SQL width dialog of the data dictionary to fix that.

K-Base ID 124558

Title: "[DataDirect][OpenEdge JDBC Driver][OpenEdge] Array element value overflow. (12664)"

Created: 06/22/2007 Last Modified: 06/28/2007

Status: Unverified

Symptoms:

• [OpenEdge JDBC Driver][OpenEdge] Array element value overflow. (12664)

• Char array column test is defined with 2 extents and SQL-Width of 3

• Executing the following query fails:

UPDATE PUB.Customer SET test1='aaa' where "Cust-Num"=1;

SELECT test1 FROM PUB.Customer where "Cust-Num"=1;

Facts:

• OpenEdge 10.1B

Cause:

• Accessing a char field containing more characters than defined with SQL-Width via the SQL-92 engine.

In the specific case each column extent for the specific record was updated to contain 3 characters which are then in total 6 characters for the two extensions. The SQL-Width for the record selection of the individually extent was sufficient but not to select the whole array.

Mike,

It looks like you know what the problem is.  Forgive me, for not understanding how to fix.  Is there more specific steps to fix the problem?   Is it in the ODBC driver that I need to add information or in MS Access?

Jody

It looks like you know what the problem is. Forgive me, for not understanding how to fix. Is there more specific steps to fix the problem? Is it in the ODBC driver that I need to add information or in MS Access?

It's a property of CHArACTER fields in the Progress Data Dictionary. There is nothing (to my knowledge) that you can change at the ODBC driver or Access.

From the data dictionary:

Select the table, then Options -> Adjust Field Width

See attachment.

Your report shows many array fields (extent fields). None of them is character.

I guess it's one of the integer or decimal array's that need a higher SQL width. I'd try to set them to something very large, like 100 and see if that solves the issue. Then I'd try to narrow down to a width that's appropriate. Progress defaults we SQL width based on data-type and character format. For extents the SQL width seems to be for the whole array and not just the individual members.

I found a document that I attached.  In section 6 under troubleshooting there is a SQL Width that looks like what I need to do.  It uses the "dbtool".  By using this tool it doesn't change anything to my database?

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/26/V803400_5F00_Instructions.rtf:550:0]

That should be o.k. ;-) But a backup doesn't hurt either.

Settings the SQL width shouldn't affect your table's CRC values, so that shouldn't require a recompile either.

Glad you discovered dbtool.  That is where I would have pointed you if I had joined this conversation earlier.  Manual adjustment of SQL Width is an easy quick fix, *IF* you know what value you need to set it to.  dbtool not only takes care of figuring out what value you need, but can do so for the entire database.  Use it regularly to avoid surprises!

Thomas,

Thank you for your response.  I'm currently in the process of testing this dbtool on our test database.  Unfortunatly, it has some questions after I make selection #2 that I don't know the answers to. I made a guess at them, but the results showed no changes.  Any idea's.

Jody

I'm not maintaining a production database, so it has been a while since I have run it.  Perhaps you should run us through the questions and your answers?

Thank you for your response. I'm currently in the process of testing this dbtool on our test database. Unfortunatly, it has some questions after I make selection #2 that I don't know the answers to. I made a guess at them, but the results showed no changes. Any idea's.

What answer did you give?

I attached a screenshot of the information I entered.  As you can see the dsmUserConnect failed.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/26/dbtool_5F00_screenshot.rtf:550:0]

jmannett@midamericamfg.com schrieb:
I attached a screenshot of the information I entered.  As you can see the dsmUserConnect failed.

That issue may actually have a number of causes. Did you try to restart the DB server and try again?

I suggest that you check the OpenEdge K-Base on http://progress.atgnow.com/esprogress/results.do, enter dsmUSerConnect failed on the search field at find solutions by problem and work through the solutions. The dbtool is not able to connect to your DB. Either the user count or the server count is exceeded or another configuration issue.

Is the DB server up or down when you are doing thig?  If the server is up, you can't pick single-user.

Thomas,

No, I stopped the database.

Thank you,

Jody Mannetter

I stopped the database before using the dbtool.

I finally was able to get the dbtool to run.  I had to start the database and appservers. I used the following parameters;

1.5

Padding % above current max: 10

all

3

It ran and showed 17 changes.

I connected using Excel and got the same error "Array element value overflow. (12664)".  I did isolate the field in the table that is giving me a problem.  Any other ideas?  At least I'm learning a lot.

Thanks,

Jody

It ran and showed 17 changes.

I connected using Excel and got the same error "Array element value overflow. (12664)". I did isolate the field in the table that is giving me a problem. Any other ideas? At least I'm learning a lot.

I'd try much higher values... to see if that helps anything at all.

If not, I'd consider talking to Progress tech support.

AppServers are not relevant to dbtool.

What do you mean by connect 1.5?  As far as I know, it only takes integers for the number of threads.

Tell us about the offending field?  Is it character?  Does it have an extent?  If it has an extent are you using ProElement to access individual elements?

One semi-random theory is that if you had a character field with an extent, that the SQL Width might apply to the element.  Without PROELEMENT(), it will return all the values of all the extents concatenated in one result ... which would be a lot longer!  You might get around this by manually setting the SQL width to something like the maximum sum of all of the lengths of all of the extents plus 10% or something .... but I don't know what you are going to do with the result.

Tell us about the offending field? Is it character? Does it have an extent? If it has an extent are you using ProElement to access individual elements?

One semi-random theory is that if you had a character field with an extent, that the SQL Width might apply to the element. Without PROELEMENT(), it will return all the values of all the extents concatenated in one result ... which would be a lot longer! You might get around this by manually setting the SQL width to something like the maximum sum of all of the lengths of all of the extents plus 10% or something .... but I don't know what you are going to do with the result.

The table in question didn't have CHARACTER Extents. The Dictionary report is attached.

I solved the problem.  Once I figured out the field that was causing the problem I used the Progress Data Dictionary.  The field was set at a width of 4 and the format was >>9.  I changed the width to 25 and it works!!!   If it wasn't for both of your help, I would not have gotten it.

Thank you,

Jody

Of course, now you have to figure out why there is a value in that field larger than 999 ....

The thing that really irritates me is that the name of the field is InvcDtl.Obsolete803-RepSplit.  Epicor in their database renames obsolete fields like this.  So this field is not used in the application at all.

Silly practice on Epicor's part ... but it does raise the question of why you want to pull the value into Excel if the field is not used in the application.

Of course, now you have to figure out why there is a value in that field larger than 999 ....

If I'm not mistaking, Epicor does also use .NET Clients connected thru the .NET Proxy. That won't care about ABL formats at all.

The values might be the result of a calculation, not a user's input.

The values might have been entered in an ABL frame overriding the dictionary default formats.

There are so many reasons why the data might not be compliant to the dictionary format... It's probably very hard to answer above question.

Silly practice on Epicor's part ... but it does raise the question of why you want to pull the value into Excel if the field is not used in the application.

Excel for "reporting" is very often used by non-technical people, controllers, sales reps, etc... They might not know, which fields are used or not used. They may tend to just add all fields to a query - because it's easier than selecting the fields you need in the first place.

Creating SQL views hiding the obsolete fields might be a good idea to avoid all parties unnecessary trouble caused by obsolete fields.

There are two more or less opposite scenarios.  Either the dictionary format is out of sync with use and the data are actually valid or the data are the result of an error of some kind and the dictionary format is a valid guide.  In this case, if the field isn't used any more, one wonders if one couldn't just zero out the field and be done with it.

tamhas schrieb:
There are two more or less opposite scenarios.  Either the dictionary format is out of sync with use and the data are actually valid or the data are the result of an error of some kind and the dictionary format is a valid guide.  In this case, if the field isn't used any more, one wonders if one couldn't just zero out the field and be done with it.

Neither does surprise me. Many application vendors I've worked with are very lazy with updating the schema. Especially something "trivial" as the format is very often just "fixed" in the application source code, not the DB schema, because new R-Code can be delivered with zero downtime.

A field that is obsolete today might still contain data that was used in the past. Removing the field makes the data completely inaccessible.

Permit me to be skeptical that a field not referenced anywhere in the application and which is labelled obselete is being accurately maintained and has meaningful data.  I think it is just Epicor being lazy.  Instead of telling people "we are going to delete this field, so if you are using it for something we aren't, you want want to do something else", they just rename it and leave it and obselete data around to confuse people.

Mike and Thomas,

I was successful in doing my test database using Progress Data Dictionary. I waited until today to do my main database. When I try to connect to my main database I get the following error message "Server has -Mm parm 4096 and client has 1024. They must match. (1150)". Any idea's.

Thanks,

Jody

I was successful in doing my test database using Progress Data Dictionary.  I waited until today to do my main database.  When I try to connect to my main database I get the following error message "Server has -Mm parm 4096 and client has 1024. They must match. (1150)".  Any idea's.

Create a copy of the dictionary shortcut and add -Mm 4096 as an additional startup parameter (just after -p _dict)

Permit me to be skeptical that a field not referenced anywhere in the application and which is labelled obselete is being accurately maintained and has meaningful data. I think it is just Epicor being lazy. Instead of telling people "we are going to delete this field, so if you are using it for something we aren't, you want want to do something else", they just rename it and leave it and obselete data around to confuse people.

Thomas, I'm with you saying that this is a bad practice. But it's reality. Maybe Epicor needs some advice

That worked.

Thank you.

Jody

They aren't alone ...

Thomas,

I originally wanted to link the table InvcDtl into MS Access. It only allows the whole table to be linked. I don't have the option in Access to link just individual fields in.

Thank you,

Jody Mannetter

Mid-America Manufacturing

Production System Specialist/IT Administrator

jmannett@midamericamfg.com

Phone: 515-382-3113 Ext. 226

I originally wanted to link the table InvcDtl into MS Access.  It only allows the whole table to be linked.  I don't have the option in Access to link just individual fields in.

When you are a bit familiar with SQL, try to create a view instead. See the "SQL Reference" in the OpenEdge docu set for details. Progress has the "sqlexp" command line tool that serves as a SQL client for admin commands.

CREATE VIEW view_name

AS query_expression

;

For the client application, the VIEW will look like a table and you can skip unneeded fields.

They aren't alone ...

A common picture. Unfortunately. Makes life easy for the vendor, but obviously difficult for their customers.

In addition to getting rid of junk, views can be handy for doing joins so that, for example, a master table and the descriptive values for codes in that table all look like a single table to something like Excel.

BTW, if you do a lot of Excel reporting, you might also want to poke around some other reporting solutions.  There are some which are quite easy to use and which have reasonably pretty output.

Okay, you have my curiosity up. What other "reporting solutions" are you talking about?

Thank you,

Jody Mannetter

Mid-America Manufacturing

Production System Specialist/IT Administrator

jmannett@midamericamfg.com

Phone: 515-382-3113 Ext. 226

I don't pretend to be up on all the options these days, but there are a lot of them.  I did a lot of work with Actuate ... which takes a bit of investment, but is a no holds barred solution.  Birt is sort of son of Actuate.  Crystal Reports is used by a lot of ABL sites.  I saw a demo of CyberQuery a few months ago at a PUG meeting which was impressive for how quick and easy the person was able to whip together options without programming and yet do some minimal override programming to do fancier things, but I don't know how it does at the really hard stuff.  You should consider coming to PUG Challenge Americas ( http://www.pugchallenge.org/index.html ) since there will both be some of the comercial products on display and talks about various integration options.

I like working with t.i.m. from triangle solutions (a German progress partner, product is also localized to English).

A very flexible reporting, ETL, DataWarehouse and OLAP solution. Completly written in Progress - but it works well with other databases as well. Scales perfect from workgroup to large enterprise. I've attached their datasheet.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/26/Triangle_5F00_Folder_5F00_eng.pdf:550:0]