Information générale
Catégorie:
Codage, syntaxe et commandes
Versions des environnements
Network:
Windows 2003 Server
To the people looking for code on how I do this:
The read is thus:
lcTableName is the name of the table I want to get the data from
lnAgamaHandle is the handle returned by the opening of the AgamaLink
lnResult is the result from the query, to trap for errors.
lcSql is the SQL string to execute
lcSql='SELECT * FROM '+lcTableName
lnResult=SQLEXEC(lnAgamaHandle,lcSql,'Agama')
To get the value for lnAgamaHandle
SQLSETPROP(0,'DispLogin',3) && No Password req'd
lnAgamaHandle=SQLCONNECT('Agama','DBA','')
The above SQLEXEC command takes about 2 hours to execute to get about 27,000 records. AS you state, this is unacceptable.
------------------
After the data is retrieved, I have a translation table that holds the source field name, source table, target field name and target table. I run a routine that places the field names into a 2 dimesional array, first column is the source, second is the target. This the list of fields for the SQL INSERT command. (It is only run once.)
i.e. lcFieldList
lnSysproHandle is gotten in a similar manner to the lnAgamaHandle above.
lcSql is the insert command to be executed
lcValueList is the values to insert.
lcTgtTable is table to insert into
Then I run through the source table, the result of the first query.
Example of the code is below
lcFieldList=GetFieldList()
Select Agama
SCAN
scatter memvar
* GetValues is a routine that translates the memvars to the data for the new system.
lcValueList=GetValues()
lcSql='INSERT INTO '
lcSql=lcSql+lcTgtTable+' ('+ALLTRIM(lcFieldList)+') '
lcSql=lcSql+'VALUES ('+lcValueList+') '
*- Now Write Data To Target Table - lnResult to handle errors
lnResult=SQLEXEC(lnSysproHandle,lcSQL)
ENDSCAN
This usually takes about 1/2 or less time to execute than the read above.
Is there anything else I could do to optimize/improve the speed of retrieval and write?
TIA,
Mike
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement