SQL Adjust Field width
- Thread starter rmdasari
- Start date
I had problems with SQL width in Progress DB, and I found a 4GL program which browse each table and resizes if necessary.
But if some tool is available in DLC, I'll go and check it ! Thanks for the information.
Code:
FORM
_file-name LABEL "Processing File"
_field-name
WITH FRAME xx SIDE-LABELS 1 COL WIDTH 78.
DEFINE TEMP-TABLE sqlw
FIELD fileName AS CHARACTER FORMAT "x(24)"
FIELD fieldName AS CHARACTER FORMAT "x(24)"
FIELD sz AS INTEGER
INDEX ix IS PRIMARY UNIQUE fileName fieldName.
RUN FIND_fields.
FOR EACH sqlw NO-LOCK :
DISPLAY sqlw WITH TITLE "Change SQL-WIDTH for this fields" .
END.
DEFINE VARIABLE vlOK AS LOGICAL.
IF CAN-FIND( FIRST sqlw NO-LOCK ) THEN
MESSAGE "Do you wish to continue?" VIEW-AS ALERT-BOX BUTTONS YES-NO
UPDATE vlok.
ELSE
MESSAGE "Dataserver schema was validated." VIEW-AS ALERT-BOX.
IF vlok
THEN DO TRANSACTION:
FOR EACH _file NO-LOCK WHERE NOT _hidden,
EACH _field OF _file WHERE _data-type = "CHARACTER",
EACH sqlw NO-LOCK WHERE FILENAME = _file-name AND fieldName = _field-name:
ASSIGN _width = sz + 8.
END.
END.
PROCEDURE FIND_fields:
DEFINE VARIABLE vFieldList AS CHARACTER NO-UNDO.
DEFINE VARIABLE vSizeList AS CHARACTER NO-UNDO.
DEFINE VARIABLE vFieldName AS INTEGER NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hField AS HANDLE NO-UNDO.
DEFINE VARIABLE VarBoucle AS INTEGER NO-UNDO.
DEFINE VARIABLE VarSize AS INTEGER NO-UNDO.
FOR EACH _file WHERE NOT _hidden NO-LOCK :
ASSIGN vFieldList = ''
vSizeList = ''.
FOR EACH _field OF _file NO-LOCK WHERE _data-type = "CHARACTER" :
ASSIGN vFieldList = vFieldList + ',' + _field-name
vSizeList = vSizeList + ',' + STRING(INTEGER(_width - 2)).
END.
IF vFieldList = ''
THEN NEXT.
/* Suppression de la virgule de début de liste */
ASSIGN vFieldList = SUBSTRING(vFieldList,2)
vSizeList = SUBSTRING(vSizeList,2).
/* Création du buffer de parcours des enregistrements */
CREATE BUFFER hBuffer FOR TABLE _file-name.
CREATE QUERY hQuery.
hQuery:SET-BUFFERS(hBuffer).
hQuery:QUERY-PREPARE("FOR EACH " + _file-name + " NO-LOCK ").
hQuery:QUERY-OPEN().
DISPLAY _file-name WITH FRAME xx .
IF hQuery:GET-FIRST()
THEN REPEAT:
hBuffer = hQuery:GET-BUFFER-HANDLE(1).
DO vFieldName = 1 TO NUM-ENTRIES(vFieldList):
ASSIGN hField = hBuffer:BUFFER-FIELD(ENTRY(vFieldName,vFieldList)).
VarSize = 0 .
IF HField:EXTENT > 0
THEN DO :
DO VarBoucle = 1 TO HField:EXTENT :
IF (LENGTH(hField:BUFFER-VALUE(Varboucle)) >
INTEGER(ENTRY(vFieldName,vSizeList))) AND
( LENGTH(hField:BUFFER-VALUE(Varboucle)) >
VarSize )
THEN DO :
VarSize = LENGTH(hField:BUFFER-VALUE(Varboucle)).
END.
END.
END.
ELSE DO :
IF LENGTH(hField:BUFFER-VALUE) >
INTEGER(ENTRY(vFieldName,vSizeList))
THEN DO:
VarSize = LENGTH(hField:BUFFER-VALUE).
END.
END.
IF Varsize > 0
THEN DO :
FIND sqlw WHERE FILENAME = _file-name
AND fieldName = ENTRY(vFieldName,vFieldList) NO-ERROR.
IF NOT AVAILABLE(sqlw)
THEN DO:
CREATE sqlw.
ASSIGN FILENAME = _file-name
fieldName = ENTRY(vFieldName,vFieldList).
END.
IF sz < varsize
THEN ASSIGN sz = varsize .
END.
END.
hQuery:GET-NEXT().
IF hQuery:QUERY-OFF-END
THEN LEAVE.
END.
hQuery:QUERY-CLOSE.
DELETE OBJECT hQuery.
DELETE OBJECT hBuffer.
END. /* FOR EACH _file... */
END PROCEDURE.But if some tool is available in DLC, I'll go and check it ! Thanks for the information.
Must be said that if you are on 9.D06 or higher then use dbtool.
Advantages:
Same applies for update statistics, if you use SQL you must perform update statistics on a regular basis. (say once a week).
Casper.
Advantages:
- Much much, much quicker
- Works on an online database
- No need to reconnect in order for the sql client to see the changes
Same applies for update statistics, if you use SQL you must perform update statistics on a regular basis. (say once a week).
Casper.
I have the same problem and I used DataDirectory to change the field. It was X(70), but even makeing it much bigger (eg X(800)) nothing changes. I need to port the data from Progress to SQLServer2005, but when I open tables with such field the DB return error message "Column has value exceeding its max length". Why?
What can I do? What I need to change in the script to use in ProcedureEditor 4GL? My DB is Progress9.1D.
Thank's a lot.
Marco
You are on 9.1D. Use dbtool (assuming your on at least 9.1D06).
if you use the program then the schmea changes aren't seen when the servers are already running. You have to kill the servers or restart the database in order to see the changes. (The schema is chached in a running server).
Apart from dbtool being much much much faster, this is also a good reason to use dbtool.
Casper.
if you use the program then the schmea changes aren't seen when the servers are already running. You have to kill the servers or restart the database in order to see the changes. (The schema is chached in a running server).
Apart from dbtool being much much much faster, this is also a good reason to use dbtool.
Casper.
I use from command line: "dbtool generale.db"
Then appears:
------------------------------------------------------
DATABASE TOOLS MENU - 1.0b
--------------------------
1. SQL Width & Date Scan w/Report Option
2. SQL Width Scan w/Fix Option
3. Record Validation
4. Record Version Validation
5. Read database block(s)
9. Enable/Disable File Logging
Q. Quit
Choice: 2
<connect>: (0=single-user 1=self-service >1=#threads)? 0
<table>: (Table number or all)? all
<area>: (Area number or all)? all
<display>: (verbose level 0-3)? 3
dsmUserConnect failed rc = -1
ERROR connecting -1dsmUserDisconnect chiamato per utente non valido. rtc: -20031. (7132)
Total records read: 0
SQLWidth errors found: 0, Date errors found: 0
SQLWidth errors fixed: 0
------------------------------------------------------
What's wrong? It seems a login problem? Why It doesn't ask userID and pwd?
P.S. My Progress version is 9.1D04 not 06! Is it possible a free upgrade?
Marco
Then appears:
------------------------------------------------------
DATABASE TOOLS MENU - 1.0b
--------------------------
1. SQL Width & Date Scan w/Report Option
2. SQL Width Scan w/Fix Option
3. Record Validation
4. Record Version Validation
5. Read database block(s)
9. Enable/Disable File Logging
Q. Quit
Choice: 2
<connect>: (0=single-user 1=self-service >1=#threads)? 0
<table>: (Table number or all)? all
<area>: (Area number or all)? all
<display>: (verbose level 0-3)? 3
dsmUserConnect failed rc = -1
ERROR connecting -1dsmUserDisconnect chiamato per utente non valido. rtc: -20031. (7132)
Total records read: 0
SQLWidth errors found: 0, Date errors found: 0
SQLWidth errors fixed: 0
------------------------------------------------------
What's wrong? It seems a login problem? Why It doesn't ask userID and pwd?
P.S. My Progress version is 9.1D04 not 06! Is it possible a free upgrade?
Marco
If the database is running then you must choose:
Choice: 2
<connect>: (0=single-user 1=self-service >1=#threads)? here 1 or higher
<table>: (Table number or all)? all
<area>: (Area number or all)? all
<display>: (verbose level 0-3)? 3
at least install sp9 and use the datadirect 4.1 driver.
Casper
Choice: 2
<connect>: (0=single-user 1=self-service >1=#threads)? here 1 or higher
<table>: (Table number or all)? all
<area>: (Area number or all)? all
<display>: (verbose level 0-3)? 3
at least install sp9 and use the datadirect 4.1 driver.
Casper
Ok, used till 100 in "threads", bun nothing... same reply.
P.S. This is the actual ODBC driver version: DataDirect 4.10 Progress SQL92v9.1E, version 4.10.01.00, file PGPRO918.DLL 27/05/2004.
Where can i find the sp9? Please post the url.
Thank's.
Marco
P.S Sorry, I wrote in a previous message that the Progress versione was 9.1D, it is 9.1E04.
I don't understand the reply, translated in english: "No server available for database generale.db (1432)"
P.S. This is the actual ODBC driver version: DataDirect 4.10 Progress SQL92v9.1E, version 4.10.01.00, file PGPRO918.DLL 27/05/2004.
Where can i find the sp9? Please post the url.
Thank's.
Marco
P.S Sorry, I wrote in a previous message that the Progress versione was 9.1D, it is 9.1E04.
I don't understand the reply, translated in english: "No server available for database generale.db (1432)"
Well Progress version should be good enough to let dbtool run.
sp 4 is the last sp for 9.1E.
Number of threads should be something like 1,5 times # cpu's. (well I don;t know if that still is the truth with hyperthreading stuff etc..).
But for the purpose of letting dbtool work 1 should be enough.
I just looked up the error you get in the KB and I didn't find a lot on it.
apart from this:
If your not standing in the directory where the database resides then check this:
http://progress.atgnow.com/esprogre...SolutionLink=1&tabs=true&docPropValue=p104065
you should enter the full pathname to the database if your not in the same directory as the database.
if that doesn't work then maybe this is a solution:
http://progress.atgnow.com/esprogress/Group.jsp?bgroup=progress&id=P96697
which suggests this is happening when you use a different version of Progress for dbtool then where the database was started with. Wich seems not to be the problem since you are standing in the directory where the database resides. But to be sure, are there different versions of progress involved?
HTH,
Casper.
sp 4 is the last sp for 9.1E.
Number of threads should be something like 1,5 times # cpu's. (well I don;t know if that still is the truth with hyperthreading stuff etc..).
But for the purpose of letting dbtool work 1 should be enough.
I just looked up the error you get in the KB and I didn't find a lot on it.
apart from this:
If your not standing in the directory where the database resides then check this:
http://progress.atgnow.com/esprogre...SolutionLink=1&tabs=true&docPropValue=p104065
you should enter the full pathname to the database if your not in the same directory as the database.
if that doesn't work then maybe this is a solution:
http://progress.atgnow.com/esprogress/Group.jsp?bgroup=progress&id=P96697
which suggests this is happening when you use a different version of Progress for dbtool then where the database was started with. Wich seems not to be the problem since you are standing in the directory where the database resides. But to be sure, are there different versions of progress involved?
HTH,
Casper.
tamhas
ProgressTalk.com Sponsor
There are several different issues going on here, but let me clarify one point. The _default_ format in the data dictionary has nothing to do with the amount of data in the field or with SQL access. That format can be overridden by any ABL program and it is common, for example, to have something like X(5) as a default format, but then to have the UI use an editor widget which is 50 wide x 20 lines or something, i.e., a *lot* more data than would fit in the X(50).
Moreover, SQL pays no attention to format at all. It is focused on width and it is width which dbtool will update. The virtue of dbtool over just sticking a new value in _width is that it checks the actual data and makes it wide enough for what is there. The little programs people have supplied do the same thing, but much more slowly.
Moreover, SQL pays no attention to format at all. It is focused on width and it is width which dbtool will update. The virtue of dbtool over just sticking a new value in _width is that it checks the actual data and makes it wide enough for what is there. The little programs people have supplied do the same thing, but much more slowly.
The information in this thread has proven very helpful, but I'm left with one question in regard to this piece of advice:
My question is not how to schedule a task or add a cron job, but what command line to use for the task? I've tried 'echo 2 | dbtool <dbname>' and it does correct any problems with table 2, probably using 2 for each entry. I've tried searching for switches or params for dbtool, so far no luck. Any pointers?
I use this for version 10.1B and higher. The third numberis the number of threads which will be run, so this depends on hte cpu's available and threading capabilities of the cpu.
Casper.
Code:
dbtool pathtodatabase/databasename >/dev/null 2>&2 <<!
9
2
3
0
all
all
3
!Casper.
Similar threads
- Locked
- Alexander Fries
- Progress Blog