After working with the new SQL SELECT statement in Xbase++ 2.0, I became frustrated because it always failed with an Ads DacSession(). Apparently, Alaska has given this low priority over other SQL priorities. I also am disappointed with the performance of PostGreSql as compared to ADS. As an ISAM database it is unusable on large datasets.
After looking at the features of the Xbase++ 2.0 SQL SELECT command, I realized how easy it is to give this same functionality in Xbase++ 1.9 when using ADSDBE.
I have attached 4 files:
DCADS.CH - Contains the new SQL command
_DCADS.PRG - Contains the source for DC_AdsSqlStatement()
SQLTEST.PRG - A test program that demonstrates this new feature.
SQLTEST.XPJ
Copy DCADS.CH to your \exp19\include folder.
Copy _DCADS.PRG to your \exp19\source\dclipx folder and run BUILD19_SL1.BAT to rebuild DCLIPX.DLL.
Copy SQLTEST.PRG and SQLTEST.XPJ to your \exp19\samples\adssql folder.
Look at the source for how this new command can be used:
The single SQL command returns the result set in a workarea, an array (Scatter()) and an array of objects (DC_DbScatter()).
The 3 browses show how this data is used.
There is a DacSession() that connects to the Samples.Add (dictionary) in \exp19\samples\adssql\files\data folder.
Here is the syntax of the command:
Code: Select all
SQL <statement> ;
[INTO ARRAY <aData>] ;
[INTO OBJECTS <aObjects>] ;
[INTO CURSOR <cAlias>] ;
[VIA <oSession>] ;
[EVAL <bEval>] ;Code: Select all
#INCLUDE "adsdbe.CH"
#INCLUDE "dcads.CH"
#INCLUDE "dcdialog.CH"
#INCLUDE "dmlb.CH"
FUNCTION Main()
LOCAL GetList[0], oTab1, oTab2, oTab3, oBrowse1, oBrowse2, oBrowse3, ;
aInvoices[0], aObjects[0], cAlias := 'INVOICES', i, aStru, cSql
TEXT INTO cSql WRAP
SELECT
Invoice.Inv_nmbr as Invoice,
Invoice.Balance as Balance,
Customer.Bill_name as Customer,
Customer.Phone as Phone
FROM Invoice
LEFT OUTER JOIN Customer ON invoice.cust_nmbr = Customer.cust_nmbr
WHERE Invoice.Balance > 0
ENDTEXT
SQL cSql INTO ARRAY aInvoices INTO OBJECTS aObjects INTO CURSOR cAlias ;
EVAL {|a|ATail(aInvoices)[3] := Upper(a[3])}
aStru := INVOICES->(dbStruct())
@ 0,0 DCTABPAGE oTab1 SIZE 130,25 CAPTION 'Work Area'
@ 0,0 DCTABPAGE oTab2 RELATIVE oTab1 CAPTION 'Array'
@ 0,0 DCTABPAGE oTab3 RELATIVE oTab2 CAPTION 'Objects'
@ 2,2 DCBROWSE oBrowse1 PARENT oTab1 SIZE 126,22 ALIAS cAlias
FOR i := 1 TO Len(aStru)
DCBROWSECOL DATA DC_FieldWBlock(aStru[i,1],'INVOICES') PARENT oBrowse1 ;
HEADER aStru[i,1] WIDTH aStru[i,3]
NEXT
@ 2,2 DCBROWSE oBrowse2 PARENT oTab2 SIZE 126,22 DATA aInvoices
FOR i := 1 TO Len(aStru)
DCBROWSECOL ELEMENT i HEADER aStru[i,1] WIDTH aStru[i,3] PARENT oBrowse2
NEXT
@ 2,2 DCBROWSE oBrowse3 PARENT oTab3 SIZE 126,22 DATA aObjects
FOR i := 1 TO Len(aStru)
DCBROWSECOL OBJECTVAR (aStru[i,1]) HEADER aStru[i,1] WIDTH aStru[i,3] PARENT oBrowse3
NEXT
DCREAD GUI FIT TITLE 'Browsing a SQL cursor'
RETURN nil
* -----------
PROC appsys ; RETURN