Error in ODBC connection

Posted by jmannett@midamericamfg.com on 24-Feb-2011 13:52

Hello,

I'm getting an error message when I try use MS Access through ODBC connection to connect to our Progress database.  The error is "Array element value overflow (12664).  I already have several table linked but I get this error on one of the linked tables.  Does anyone have an idea?

Thanks

Jody

All Replies

Posted by Admin on 24-Feb-2011 14:04

The version might be important too know for this issue.

Also, how does that table look like?

Posted by jmannett@midamericamfg.com on 24-Feb-2011 14:50

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

Posted by Admin on 24-Feb-2011 14:59

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?

Posted by jmannett@midamericamfg.com on 24-Feb-2011 15:16

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

Posted by Admin on 24-Feb-2011 15:24

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.

Posted by jmannett@midamericamfg.com on 24-Feb-2011 15:58

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

Posted by Admin on 24-Feb-2011 16:04

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.

Posted by Admin on 24-Feb-2011 16:21

From the data dictionary:

Select the table, then Options -> Adjust Field Width

See attachment.

Posted by Admin on 25-Feb-2011 01:27

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.

Posted by jmannett@midamericamfg.com on 25-Feb-2011 07:21

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]

Posted by Admin on 25-Feb-2011 07:25

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.

Posted by Thomas Mercer-Hursh on 25-Feb-2011 11:38

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!

Posted by jmannett@midamericamfg.com on 25-Feb-2011 11:57

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

Posted by Thomas Mercer-Hursh on 25-Feb-2011 12:13

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?

Posted by Admin on 25-Feb-2011 12:14

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?

Posted by jmannett@midamericamfg.com on 25-Feb-2011 13:14

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]

Posted by Admin on 25-Feb-2011 13:28

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.

Posted by Thomas Mercer-Hursh on 25-Feb-2011 13:36

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

Posted by jmannett@midamericamfg.com on 25-Feb-2011 13:54

Thomas,

No, I stopped the database.

Thank you,

Jody Mannetter

Posted by jmannett@midamericamfg.com on 25-Feb-2011 13:54

I stopped the database before using the dbtool.

Posted by jmannett@midamericamfg.com on 25-Feb-2011 15:51

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

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

Posted by Admin on 25-Feb-2011 15:59

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.

Posted by Thomas Mercer-Hursh on 25-Feb-2011 16:05

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.

Posted by Admin on 25-Feb-2011 16:29

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.

Posted by jmannett@midamericamfg.com on 25-Feb-2011 16:32

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

Posted by Thomas Mercer-Hursh on 25-Feb-2011 16:38

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

Posted by jmannett@midamericamfg.com on 25-Feb-2011 16:45

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.

Posted by Thomas Mercer-Hursh on 25-Feb-2011 17:30

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.

Posted by Admin on 26-Feb-2011 13:34

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.

Posted by Admin on 26-Feb-2011 13:39

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.

Posted by Thomas Mercer-Hursh on 26-Feb-2011 13:58

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.

Posted by Admin on 26-Feb-2011 14:15

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.

Posted by Thomas Mercer-Hursh on 26-Feb-2011 15:55

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.

Posted by jmannett@midamericamfg.com on 26-Feb-2011 15:59

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

Posted by Admin on 26-Feb-2011 16:04
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)

Posted by Admin on 26-Feb-2011 16:09

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

Posted by jmannett@midamericamfg.com on 26-Feb-2011 16:14

That worked.

Thank you.

Jody

Posted by Thomas Mercer-Hursh on 26-Feb-2011 16:18

They aren't alone ...

Posted by jmannett@midamericamfg.com on 26-Feb-2011 16:19

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

Posted by Admin on 26-Feb-2011 16:29

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.

Posted by Admin on 26-Feb-2011 16:29

They aren't alone ...

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

Posted by Thomas Mercer-Hursh on 26-Feb-2011 16:44

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.

Posted by jmannett@midamericamfg.com on 26-Feb-2011 17:09

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

Posted by Thomas Mercer-Hursh on 26-Feb-2011 18:39

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.

Posted by Admin on 28-Feb-2011 02:28

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]

This thread is closed