And the easiest way to fetch data from DB is using ADO, something like this (here your Sql command will be your SELECT * FROM table):
Connection = ADO.CreateConnection();
Connection.ConnectionString = ConnectionStr;
Connection.Open();
Command = ADO.CreateCommand();
while (Connection.State == adStateConnecting) {
aqUtils.Delay(qa.system.time.smaller);
i = i + 1;
if (i > 100) {
throw Error("Connection not opened within 10s");
}
}
Command.CommandText = Sql;
Command.CommandTimeout = CommandTimeout;
Command.ActiveConnection = Connection;
Results = Command.Execute();
ErrCount = Connection.errors.count;
if (ErrCount !== 0) {
for (Err = 0; Err == ErrCount; Err++){
Err = Connection.errors.item;
Error = Error + Err;
}
Connection.Errors.Clear;
throw Error;
}
else {
if ((Results != null) && (Results.RecordCount !== 0)) {
FunctionResult.Lines = Results.GetRows().toArray();
}
FunctionResult.Count = Results.RecordCount;
}
BenoitB Thanks much for providing some insights into this.
I see that you have a function as an array variable which has a JSON object 'functionResult'. That would store the 'data table' in 'data' array as a value. 'data' gets all the elements from 'temp' object which in turn contains row data separated by commas(,) ?
I have a few questions.
I really appreciate your help and the direction you've given. I'm still trying to understand this piece.
On the other hand, I have another function which has an ADO connection and runs a query. After that it makes a call to the routine:
dbRecordToObject(record)This fetches single record at a time and that gets pushed to a set like this: but this is really slow
var set = conn.Execute_(exeQuery);
//declare an array
var elementList = [];
Log.Message("INFO: set.EOF is: " +set.EOF);
if(set.EOF == false){
set.MoveFirst();
while(!set.EOF)
{
//get the result set into the array
elementList.push(dbRecordToObject(set));
set.MoveNext();
}
//Closing the connection
conn.Close();
return elementList;
Thank you
Al2
Ok, i'd done two posts because you asked about two things if i understood correctly.
First post is about transforming linear data in 2D array.
Use google translate to translate the documentation because i made effort to document then make the effort to translate 😁
And getTokenBySeparator take a token from a string, using a separator to find tokens ... 😂
Second post is about, root cause of your problem, speed.
If you look the second post, the speed problem could be resolved simply, call the set.GetRows() instead of navigating into ADO result set, so it would be:
let set = conn.Execute_(exeQuery);
let elementList = [];
if ((set != null) && (set.RecordCount != 0))
elementList = set.GetRows().toArray();
return elementList;
What you do was to browse Ado result set one by one to rebuild an array instead of fetching all data directly, so it's very slow.
Some info on GetRows() is here:
https://www.w3schools.com/asp/met_rs_getrows.asp
BTW try to implement error management and connection established waiting as it is in my code. Your code will be robust and in case of errors you can know where and what.
And don't put comment on trivial things (e.g. // Declare an array) or // Closing connection), comments are for advanced technical or algorithm points.
I invite you to take a glance to this post about method writing:
BenoitB Thanks much for help. I tried adjusting my code based on your suggestions. Getrows() is definitely the best way to get the data into the record set quickly. I wasn't aware of this method.
I could get all the data into the array, and all the data here is in the form of a liner array.
FunctionResult
One thing that I struggled with was to convert this into 2D array and append the column names so that I can reference these values using the Array, Index and the column name. I had spent some time on it earlier when you provided these suggestions, but haven't had any tangible success. I tried to implement an idea similar to what you suggested in this piece, but haven't got that working.
var FunctionResult = { data : [],
count : 1,
error : "" };
try {
if (Donnees == null)
throw Error("Le paramètre obligatoire Donnees n'est pas renseigné.")
var fields;
var temp;
temp = qa.system.getTokenBySeparator(Donnees, FunctionResult.count, Separator);
while (temp != "") {
fields = temp.split(ValueSeparator)
if (Normalize) {
fields[0] = qa.system.trim(fields[0]);
fields[0] = aqString.ToUpper(fields[0]);
fields[1] = qa.system.trim(fields[1]);
}
FunctionResult.data[fields[0]] = fields[1];
FunctionResult.count++;
temp = qa.system.getTokenBySeparator(Donnees, FunctionResult.count);
};
Thank you!
Al2
If data are not to be kept secret, could you attach a jfile of FuntionResult ?
let DataInString = JSON.stringify(FunctionResult);
aqFile.WriteToTextFile("C:\\MyData.txt", DataInString, aqFile.ctANSI, true);
Or explain more (a sample) the structure of obtained FunctionResult ?