Here's a good trick that I've used in the past with good success.
Suppose you want to do some complex joins, sorting and filtering on multiple
tables from various sources (i.e. GP, 3rd Party GP app and an outside
application).
GPTable has fields Field1 and Field2
3rdPartyTable has fields Field1 and Field3
OutsideTable has fields Field1 and Field4
Suppose you want join all 3 tables together where Field1="Bob".
1. Create a temp table (e.g. TempTableX) in your Dex application with fields
Field1, Field2, Field3, Field4. Create keys for that table for whatever
sorting you need.
2. Create a Function in your Dex application to fill the temp table.
Function: FillTempTable
function returns integer ErrorCode;
inout table TempTableX;
in string iField1Value;
local text sqltext;
local long lConn;
local string lTableName;
SQL_Connection(lConn);
{Clear content of the temp table and get it ready to receive new data}
range clear table TempTableX;
remove range TempTableX;
{Get the name of the temp table in SQL. Temp tables are created as needed
and are named differently in SQL every time}
lTableName = Table_GetOSName(table TempTableX);
{Build your complex SQL statement to populate the temp table. This statement
can be as complex and as simple as you want. This example simply joins the 3
tables together.}
sqltext =
"insert into " + lTableName +
"(Field1, Field2, Field3, Field4) +
"select T1.Field1, T1.Field2, T2.Field3, T3.Field4
from " + + " as T1
left join (select * from 3rdPartyTable) as T2
on T1.Field1=T2.Field1
left join (select * from OutsideTable) as T3
on T1.Field1=T3.Field1
where T1.Field1='" + iField1Value + """;
SQL_Execute(lConn, sqltext);
The table has been properly filled with data. There are several advantages
to this.
1. Only data that you want is in the temp table. No need to set any range or
filter.
2. All the leg work was done by the server because the filling of the table
was done by SQL. If you had to do this in Dexterity, you would need 3 nested
loops to gather all the data. This is extremely slow because every row of
data is moving thru the network twice (Reading cause data to be sent from SQL
to the local machine and writing the data to the temp table causes the data
to be sent back to thru the network to SQL). With more and more tables and
business logics involve, the iterative approach becomes exponentially more
complicated and slow.
3. More access to more tables. You are not restricted to tables within the
GP world only.
4. After you temp table is filled, you can further leverage conventional Dex
table ranging and filtering capabilities.
5. Makes for easy reporting. Report Writer is a nightmare to work with if
you are trying to build a complex report. Let SQL prep the data into the temp
table and then just dumb dump it onto a simple report.
To fill the table simply call:
FillTempTable(table TempTableX, "Bob");
get first table TempTableX;
while (err()=OKAY) do
{Do stuff}
get next table TempTableX;
end while;
Post by MisaHi,
I am trying to retrieve some data from SQL database for GP integration with
Dexterity.
Currently I am trying to use sunScript "table" and "range".
When I need to retrieve data from GP default tables, I do not want to change
the structure of the table (such as keys and sort). As the result, I found it
difficult to get specific data with complex sql queries.
Now I found that I can use ADO and pass-through SQL.
If I use ADO, I use recordset to read each record.
If I use pass-throuhg SQL, I need to fetch the each record.
I am wondering what is the common way to read records with complex sql in
Dexterity. If there are any suggestions or advice, I would appriciate.
Thank you in advance.
Misa