  |
GoFiler Legato Script Reference
Legato v 1.4j Application v 5.22b
|
| Table of Contents | < < Previous | Next >> |
Chapter Eight — Data Functions (continued)
Overview
The ODBCQuery function sends an SQL query to the database server and returns a handle to an ODBC Results Object containing the results of the request.
Syntax/Parameters
Syntax
handle = ODBCQuery ( handle hConnect, string query, [params...] );
Parameters
hConnect
A handle to an ODBC Connection Object.
query
A string that contains a complete query or a formatting string to build the query. If no parameters follow the query string, the string is passed verbatim.
params ...
Optional parameters to be applied to the query string. These parameters operate in same manner as any formatted string (see the FormatString function for details). The maximum size of the formatted string is 1 MB.
Return Value
Returns a handle to an ODBC Result Object or NULL_HANDLE on failure or if the returned dataset is empty. Use the GetLastError function to retrieve error information. This is the only method to determine if a NULL_HANDLE indicates an error or simply that no data was returned.
The GetLastErrorMessage function can return additional information about the query, including if the query produced no result. In the case of a successful query with no data, such as a ‘USE’ verb, the GetLastErrorMessage function will return the text “No Data”.
Remarks
Database queries are essential in interfacing with any database. They most commonly include selecting and returning data, inserting new data, updating existing data, and deleting data. The returned ODBC Results Object handle can be used to retrieve the data or characteristics from the query. When the handle is no longer needed, it must be closed the with CloseHandle function. If the handle declaration is a local variable, it will also be closed when the owning function is exited.
Handles to ODBC Results Objects should not be “leaked”. In other words, do not reuse the handle variable without first using the CloseHandle function. Each query result can hold a considerable amount of resources until closed and only 32 queries can be open for any connection at any time. “Leaking” the ODBC Results Object can result in sluggish performance due to improperly managed memory.
Note that not all types of queries and query structures are supported by all database connections.
Many ODBC connector drivers cache request and process them during down time. As a result if a series of queries are made without any I/O or other primary thread interrupts, an cache or buffer overflow error may result. If this occurs, consider sleeping for a small amount of time.
Examples
Set to ‘USE’ a specific table (assumed hCon is the already created connection object handle):
string data[100];
string keys[100];
handle hCon, hQuery;
int ix, size;
int rc;
// . . . already connected
ODBCQuery(hCon, "USE asteriskcdrdb");
rc = GetLastError();
if (IsError(rc)) {
MessageBox('X', "Query USE Failed %08X\r\r%s", rc, GetLastErrorMessage());
return ERROR_SOFT | 1;
}
Note that the ‘USE’ verb does not return any data so there theoretically is no handle returned. Then to get data:
hQuery = ODBCQuery(hCon, "SELECT * FROM `cdr` ORDER BY calldate DESC LIMIT 2000");
if (IsError(hQuery)) {
MessageBox('X', "Query SELECT Failed %08X\r\r%s", GetLastError(), GetLastErrorMessage());
return ERROR_SOFT | 2;
}
The query selects all data from the cdr table in descending order by ‘calldate’ with a limit of 2000 items.
In the following example, the key names are dumped out to the default log (shown in the IDE) as well as the first few columns of data:
data = ODBCFetchArray(hQuery, TRUE);
AddMessage("------------------------------ Keys ---------------------------------");
size = ArrayGetAxisDepth(data);
for (ix = 0; ix < size; ix++) {
keys[ix] = ArrayGetKeyName(data, ix);
AddMessage("%3d :%s:", ix, keys[ix]);
}
WriteLine(hCSV, CSVArrayToString(keys));
keys[0] = ArrayGetKeyName(data, 0);
keys[1] = ArrayGetKeyName(data, 1);
keys[2] = ArrayGetKeyName(data, 2);
keys[3] = ArrayGetKeyName(data, 3);
AddMessage("------------------------------ Data ---------------------------------");
AddMessage(" %-30s %-50s %-12s %-12s", keys[0], keys[1], keys[2], keys[3]);
ix = 0;
while (IsError() == FALSE) {
ix++;
WriteLine(hCSV, CSVArrayToString(data));
AddMessage("%3d %-30s %-50s %-12s %-12s : %s", ix, data[0], data[1], data[2], data[4], s2);
data = ODBCFetchArray(hQuery, TRUE);
}
Presumably, as the script programmer, one would know the key names and reference the required data.
Related Functions
Platform Support
Go13, Go16, GoFiler Complete, GoFiler Corporate, GoFiler, GoFiler Lite, GoXBRL
Legato IDE
| Table of Contents | < < Previous | Next >> |
© 2012-2024 Novaworks, LLC. All rights reserved worldwide. Unauthorized use, duplication or transmission prohibited by law. Portions of the software are protected by US Patents 10,095,672, 10,706,221 and 11,210,456. GoFiler™ and Legato™ are trademarks of Novaworks, LLC. EDGAR® is a federally registered trademark of the U.S. Securities and Exchange Commission. Novaworks is not affiliated with or approved by the U.S. Securities and Exchange Commission. All other trademarks are property of their respective owners. Use of the features specified in this language are subject to terms, conditions and limitations of the Software License Agreement.