SQL field length issue in export of data through ODBC

D

dman535

New Member
First and foremost - I am not a dba by any means.

I am working with a progress database which feeds an automotive aftermarket application "icas - pacesetter"

I am trying to export data into excel using an ODBC connection. I am running into issues where the data in certain fields is longer than the specified precision for that field. In discussions with tech support - they are telling me to alter my SQL queries using a "FORMAT" element. I have not found much documentation on the syntax for this string.

It expressed to them that it would make more sense to adjust the database to accuratley reflect the data schema as oppose to altering the quieries - so that anyone can pull data if need be. So...

1. I am hesitant to run the dbtool for fear that I will take down the application - and it requires a restart of the system. What are the odds that it will have a greater impact?

2. Does anyone know the syntax to query through ODBC and speicfy a different field length so that data for that field is returned?

3. Any other suggestions on how to pull this data from the database aside from excel using ODBC ?

Thanks

Derek.-
 
To solve the problem you need to change the _WIDTH of the _FIELD to be wide enough for the data. The fast, easy, and reliable way to do that is dbtool. Don't know why you are afraid of it.
 
The first time I ran dbtool I was concerned as well, simply because it was something I'd never done before. So I ran it in Test with our test system up and running...and found that it didn't hurt anything at all.

I wound up running dbtool against our live system to update that DB mid day, and nobody ever noticed. Just go ahead and do it--it makes ODBC *SO* much easier.
 
As the others have mentioned dbtool is perfectly safe.

Progress is not SQL. Unlike SQL a Progress database does not use the FORMAT as a "specified precision". FORMATs, in a Progress database, are for default display purposes only -- they are completely unrelated to storage requirements. All Progress data is always stored as variable length data. Recognizing the problems that this poses for SQL oriented users Progress added the SQL-WIDTH attribute and dbtool.
 
Is dbtool a command line application? Is there documentation on any parameters? Is there anything that it can "over-correct"? I just don't want to be reponsible for taking this clients system down.

As it relates to the FORMAT command, the vendor seemed to think I could overcome this through the ODBC database request - is she wrong?

Thanks

Derek.-
 
For your questions: Yes, yes, and no.

There is nothing to overcome about FORMAT, it simply doesn't apply to a SQL connection. It is a default, overridable format used in an ABL interface. In SQL, you get the data and nothing but the data unless you explicitly format it there.

dbtool is actually pretty self-explanatory, but like anything new it would probably make you feel more comfortable to try it on a test database first. A copy of the production database would be ideal since then you would get a sense for how long it will take to run and what sort of issues it will discover. I say "issues" since you might find that there is some field with a default format of say "X(30)" whose usage is such that you expect it to contain anything up to 300 characters, but which turns out to have one instance of 10,000. Nothing wrong with setting it to 10,000 to handle that record, but if it is a mistake or bad data, then they might want to fix it instead.
 
I am interested in running dbtool. I have tried, but I am getting an error. The version of Progress that I am running is 9.1D SP 09 on Red Hat Linux. The app exists and I tried to run from the command line:

dbtool /pkg3/db/my_db01

got the following error:
[FONT=r_ansi]/pkg3/appl/dlc/91d/bin/dbtool: error while loading shared libraries: librocket_r
.so: cannot open shared object file: No such file or directory
[/FONT]
This looks like some library is not updated or is not there. I did not load Progress on the machine. I do not recall when i have loaded this version in the past that there were specific instructions for installing dbtool.

Any help would be great!

:awink:
 
I found out what was wrong and now have it working. I am curious though...

Has anybody, using QAD, done this for the abs_id field? I need to know if this is a one-time deal or am gonna have to keep running this for a while? I just don't know alot about this field and whether the total length that I find will be that length all the time or whether it can get bigger.
 
That's really a QAD question which you would better ask on their forum. If people keep stuffing things in it that are too long, yes, you will need to keep running dbtool. But, in most applications, there is some UI limit which will create an upper bound. Worst case, try it from time to time and see whether it goes up or not.
 
You must log in or register to reply here.
Back
Top