Jim/Karen,
Thanks for the suggestions. Jim’s statement SLEN hint gave me the hint I
needed (See code below).
The tables that were the culprit, I got a weird buffer error.
-ERROR- Insufficient buffer space to process expressions. (2136)
-ERROR- Expression not found (2141)
Went to those tables and simply cleaned them up.
SET FEEDBACK ON
SET VAR +
vc_Cmd TEXT = NULL, +
vc_Tbl TEXT = NULL, +
vc_Col TEXT = NULL, +
vc_FleSze INTEGER = 200000, +
vc_FleNme TEXT = 'temp.dat'
DECLARE c1 CURSOR FOR +
SELECT sys_table_name, sys_column_Name +
FROM sys_columns T1, sys_tables T2 +
WHERE T1.sys_table_id = T2.sys_table_id +
AND sys_type_Name = 'VARCHAR' +
AND sys_table_id NOT IN +
(SELECT sys_table_id +
FROM sys_views)
OPEN c1
WHILE #PI <> 0 THEN
FETCH c1 INTO +
vc_Tbl INDICATOR vc_i, +
vc_Col INDICATOR vc_1
IF SQLCODE <> 0 THEN
BREAK
ENDIF
WRITE 'TABLE:', .vc_Tbl
WRITE 'COLUMN:', .vc_Col
SET VAR vc_Cmd = +
('SELECT (SLEN(' + .vc_Col + ')), * FROM' & .vc_Tbl & +
'WHERE (SLEN(' + .vc_Col + ')) >' & CTXT(.vc_FleSze))
&vc_Cmd
ENDWHILE
DROP CURSOR c1
CLEAR VAR vc_*
RETURN
Now to do an UNLOAD ALL and rebuild to see that all is well……
Thanks again,
Duey
From: [email protected] [mailto:[email protected]] On Behalf Of Jim Buddemeyer
Sent: Wednesday, April 01, 2009 3:07 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Searching for large VARCHAR in database
I don't know how many tables and VARCHAR fields are in the database but the
following command might help locate the rows.
Select UniqueIdentiferColumn (SLEN(varcharColumn)) from Table where
(SLEN(varcharColumn)) > 2000
You can play with the threshold number to reduce the data set. I believe you
need 7.6 and up to use SLEN with VARCHAR.
Jim
>>> "Heffelfinger, Duane" <[email protected]> 3/31/2009 3:28 PM >>>
Good afternoon,
Is there a way with R:Scope or a select trick to figure out what table is
holding a large chunk of data in the .rb4 file?
Even better can I figure out what table and row would be holding large VARCHARs
values in the .rb4?
Someone, somewhere has added several very large VARCHAR data chunks into the
.rb4 and I would really like to know what table and row it’s attached to.
Thanks for any ideas.
Duey
