Hi, my environment : SCO UNIX, Fox Plus on server, Ubuntu on client. DBF driver 3.0 (last release).
Sometimes SELECT SQL don't work, and I need to insert ALLTRIM function in order to find records. example :
SELECT * FROM movimien
WHERE ALLTRIM(ndoc) = '" + numPresupuesto + "' AND
ALLTRIM( valepreven ) = '' AND
(preventa = 'S' OR trasladosn = 'T' ) AND
item <> '999999999' AND
fmov = '" + fact + "' AND
(ALLTRIM(tpref) = '7' OR ALLTRIM(tpref) = '8' OR ALLTRIM(tpref) = '9')
In select sentence the delay is acceptable, but when I try to update this table with the same search criteria, delay is unacceptable....
UPDATE movimien SET
valepreven = ndoc,
refe = '" + numFolio + "',
refe1 = '" + numFolio + "',
clave = '" + tipoDocto + " " + numFolio + "2" + numFolio + "',
nlocal = " + Integer.parseInt( terminal )
WHERE alltrim(ndoc) = '" + numPresupuesto + "' AND
ALLTRIM(tpref) = '" + tipoDocto + "' AND
valepreven = '' AND
preventa = 'S'
additional data, in table "movimien" field "ndoc" is character (9), but if I select without ALLTRIM funcion, sometimes found and sometimes not. actually in the statement "SELECT" is not the problem, the problem is when I try to update the table with the same search criteria...
Client properties connection :
p.setProperty( "maxIdleTime", "1440" );
p.setProperty( "lockType", "FOXPRO4UNIX" );
p.setProperty( "lockTimeout", "90000" );
I appreciate your help,
Oscar
|
I forgot the other data, when UPDATE is running, All other clients will become very slow or hang....
|
>but if I select without ALLTRIM funcion, sometimes found and sometimes not.
Try to remove movimien.cdx file, or run once "reindex all on movimien" since your movimien.cdx mabye has some obsoleted index information.
>when UPDATE is running, All other clients will become very slow or hang....
I guess that issue should be resulted by index lock. You can try to remove your index file by run once "drop index all on movimien;"
|
Ok, I ran "reindex all on movimien" (index files are IDX), and now I do not need ALLTRIM function in sql sentence....and I can SELECT and UPDATE without problem....
But why does this happen??, reindex all instruction was delayed 15 minutes...(movimien table have 380000 records )...
if the problem reoccurs I leave all customers outside while "reindex all" is running ???
Thanks.
|
>But why does this happen??
Because long long ago you haven't use lockType connection property so that your index file is obsoleted by concurrent modification.
>reindex all instruction was delayed 15 minutes...(movimien table have 380000 records )...
Maybe your table has many index. Maybe it wait a long time to acquire a table lock for reindex purpose.
>if the problem reoccurs I leave all customers outside while "reindex all"
>is running ???
After you used lockType connection property, your index file should not be obsoleted again.
|
Finally, if reindex all on movimien is executed in Fox system on server..., this help me ??? or necessarily I need run reindex on java program with hxtt driver ??
Thanks.
|
>Finally, if reindex all on movimien is executed in Fox system on server...,
>this help me ??
You can run REINDEX in Fox system too. That operation is one-tme opeartion, and you needn't to run frequently.
|