Using Universal SQL with FOXCDX databases

Xbase++ 2.0 Build 554 or later
Post Reply
Message
Author
User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Using Universal SQL with FOXCDX databases

#1 Post by rdonnay »

Here is a sample program that utilizes Universal SQL to create arrays of data from an SQL query of a database.
The first array is a single-dimensional array of objects. The second array is a 2-dimensional array.

The eXpress++ DCBROWSE system can display data from work areas, arrays of objects or 2-dimensional arrays.

I suggest that you create a folder named \exp20\samples\SQL20 and unzip the attached file into that folder.
Copy the parts.* files to \exp20\data.

Code: Select all

#include "dac.ch"
#INCLUDE "dcdialog.CH"
#INCLUDE "appevent.CH"

#Pragma Library("dclipx.lib")

FUNCTION Main

LOCAL GetList[0], GetOptions, aParts, aInvoices, oBrowse1, oBrowse2, ;
      oBitmap, oStatic, nPointer := 1, i, lStatus, oTab1, oTab2, ;
      aObjects, aPartsStru, aInvoiceStru, aCustomerStru, oBrowse3, ;
      oBrowse4, oBrowse5, oTab3, oTab4, oTab5, bStru

DbeLoad( "FOXDBE" )
DbeLoad( "CDXDBE" )
DbeBuild( "FOXCDX", "FOXDBE", "CDXDBE" )

USE ..\..\data\parts INDEX ..\..\data\parts NEW VIA 'FOXCDX'

SELECT partno as Number, partname as Name, parttype as Type, ;
   bmpImage as Image, ;
   RecNo() as Record ;
   FROM parts ;
   INTO OBJECTS aParts

USE ..\..\data\invoice INDEX ..\..\data\invoice NEW VIA 'FOXCDX'
USE ..\..\data\customer INDEX ..\..\data\customer NEW VIA 'FOXCDX'

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                                        ;
       INTO ARRAY aInvoices

FOR i := 1 TO Len(aParts)
  aParts[i]:name := Pad(aParts[i]:name,20)
NEXT

bStru := {||PARTS->(dbStruct())}

SELECT Field1 AS FieldName, ;
       Field2 AS FieldType, ;
       Field3 AS FieldLen, ;
       Field4 AS FieldDec ;
       FROM (Eval(bStru)) INTO OBJECTS aPartsStru

bStru := {||INVOICE->(dbStruct())}

SELECT Field1 AS FieldName, ;
       Field2 AS FieldType, ;
       Field3 AS FieldLen, ;
       Field4 AS FieldDec ;
       FROM (Eval(bStru)) INTO OBJECTS aInvoiceStru

bStru := {||CUSTOMER->(dbStruct())}

SELECT Field1 AS FieldName, ;
       Field2 AS FieldType, ;
       Field3 AS FieldLen, ;
       Field4 AS FieldDec ;
       FROM (Eval(bStru)) INTO OBJECTS aCustomerStru

oBitmap := XbpBitmap():new():create()

@ 0,0 DCTABPAGE oTab1 SIZE 120, 25 CAPTION 'Parts'

@ 2,2 DCBROWSE oBrowse1 ;
      SIZE 72,22 ;
      DATA aParts ;
      HEADLINES 2 ;
      POINTER nPointer ;
      PARENT oTab1 ;
      USEVISUALSTYLE ;
      FONT '10.Lucida Console' ;
      EDIT xbeBRW_ItemSelected MODE DCGUI_BROWSE_EDITDOWN ;
      ITEMMARKED {||oBitmap:setBuffer(aParts[nPointer]:image), ;
                    oStatic:invalidateRect()}

DCBROWSECOL OBJECTVAR Number HEADER 'Part;Number' WIDTH 10 PARENT oBrowse1 PROTECT {||.t.}
DCBROWSECOL OBJECTVAR Name HEADER 'Part;Name' WIDTH 30 PARENT oBrowse1
DCBROWSECOL OBJECTVAR Type HEADER 'Part;Type' WIDTH 10 PARENT oBrowse1 PROTECT {||.t.}
DCBROWSECOL OBJECTVAR Record HEADER 'Record;Number' WIDTH 6 PICTURE '99999' PARENT oBrowse1 ;
   PROTECT {||.t.}

@ 2, 76 DCSTATIC TYPE XBPSTATIC_TYPE_BITMAP OBJECT oStatic ;
         CAPTION oBitmap SIZE 43,12 ;
         RESIZE DCGUI_RESIZE_REPOSONLY ;
         PARENT oTab1

@ 0,0 DCTABPAGE oTab2 RELATIVE oTab1 CAPTION 'Customers'

@ 2,2 DCBROWSE oBrowse2 ;
      PARENT oTab2 ;
      SIZE 114,22 FIT ;
      DATA aInvoices ;
      HEADLINES 2 ;
      FONT '10.Lucida Console' ;
      USEVISUALSTYLE ;
      CURSORMODE XBPBRW_CURSOR_ROW

DCBROWSECOL ELEMENT 1 HEADER 'Invoice;Number' WIDTH 10 PARENT oBrowse2
DCBROWSECOL ELEMENT 2 HEADER 'Balance' WIDTH 10 PARENT oBrowse2 PICTURE '99999.99'
DCBROWSECOL ELEMENT 3 HEADER 'Customer;Name' WIDTH 40 PARENT oBrowse2
DCBROWSECOL ELEMENT 4 HEADER 'Customer;Phone' WIDTH 15 PARENT oBrowse2

@ 0,0 DCTABPAGE oTab3 RELATIVE oTab2 CAPTION 'Parts Structure'

@ 2,2 DCBROWSE oBrowse3 ;
      PARENT oTab3 ;
      SIZE 114,22 FIT ;
      DATA aPartsStru ;
      HEADLINES 2 ;
      FONT '10.Lucida Console' ;
      USEVISUALSTYLE ;
      CURSORMODE XBPBRW_CURSOR_ROW

DCBROWSECOL OBJECTVAR FieldName HEADER 'Field;Name' WIDTH 10 PARENT oBrowse3
DCBROWSECOL OBJECTVAR FieldType HEADER 'Field;Type' WIDTH 10 PARENT oBrowse3
DCBROWSECOL OBJECTVAR FieldLen HEADER 'Field;Length' WIDTH 10 PARENT oBrowse3 PICTURE '9999'
DCBROWSECOL OBJECTVAR FieldDec HEADER 'Field;Decimals' WIDTH 10 PARENT oBrowse3 PICTURE '9999'

@ 0,0 DCTABPAGE oTab4 RELATIVE oTab3 CAPTION 'Invoice Structure'

@ 2,2 DCBROWSE oBrowse4 ;
      PARENT oTab4 ;
      SIZE 114,22 FIT ;
      DATA aInvoiceStru ;
      HEADLINES 2 ;
      FONT '10.Lucida Console' ;
      USEVISUALSTYLE ;
      CURSORMODE XBPBRW_CURSOR_ROW

DCBROWSECOL OBJECTVAR FieldName HEADER 'Field;Name' WIDTH 10 PARENT oBrowse4
DCBROWSECOL OBJECTVAR FieldType HEADER 'Field;Type' WIDTH 10 PARENT oBrowse4
DCBROWSECOL OBJECTVAR FieldLen HEADER 'Field;Length' WIDTH 10 PARENT oBrowse4 PICTURE '9999'
DCBROWSECOL OBJECTVAR FieldDec HEADER 'Field;Decimals' WIDTH 10 PARENT oBrowse4 PICTURE '9999'

@ 0,0 DCTABPAGE oTab5 RELATIVE oTab4 CAPTION 'Customer Structure'

@ 2,2 DCBROWSE oBrowse5 ;
      PARENT oTab5 ;
      SIZE 114,22 FIT ;
      DATA aCustomerStru ;
      HEADLINES 2 ;
      FONT '10.Lucida Console' ;
      USEVISUALSTYLE ;
      CURSORMODE XBPBRW_CURSOR_ROW

DCBROWSECOL OBJECTVAR FieldName HEADER 'Field;Name' WIDTH 10 PARENT oBrowse5
DCBROWSECOL OBJECTVAR FieldType HEADER 'Field;Type' WIDTH 10 PARENT oBrowse5
DCBROWSECOL OBJECTVAR FieldLen HEADER 'Field;Length' WIDTH 10 PARENT oBrowse5 PICTURE '9999'
DCBROWSECOL OBJECTVAR FieldDec HEADER 'Field;Decimals' WIDTH 10 PARENT oBrowse5 PICTURE '9999'

DCGETOPTIONS RESIZE RESIZEDEFAULT DCGUI_RESIZE_RESIZEONLY

DCREAD GUI FIT TITLE 'Browsing a Fox Database SQL Query' ;
   OPTIONS GetOptions TO lStatus ADDBUTTONS

IF lStatus
  PARTS->(GatherData(aParts))
ENDIF

RETURN nil

* --------

PROC appsys ; RETURN

* --------

FUNCTION GatherData( aParts )

LOCAL i

FOR i := 1 TO Len(aParts)
  DbGoTo(aParts[i]:record)
  IF dbRLock()
    REPLACE partname WITH aParts[i]:name
    dbRUnlock()
  ENDIF
NEXT

RETURN nil
FoxSQL1.jpg
FoxSQL1.jpg (89.47 KiB) Viewed 18468 times
FoxSQL2.jpg
FoxSQL2.jpg (125.92 KiB) Viewed 18468 times
FoxSQL3.jpg
FoxSQL3.jpg (74.7 KiB) Viewed 18468 times
Attachments
sql20.zip
(2.44 MiB) Downloaded 839 times
The eXpress train is coming - and it has more cars.

Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: Using Universal SQL with FOXCDX databases

#2 Post by Victorio »

Hi Roger,

Tell me, please what I need to examine this ? I need Xbase++ Proffesional ? Or also SQL Express ?

Victorio

User avatar
PedroAlex
Posts: 229
Joined: Tue Feb 09, 2010 3:06 am

Re: Using Universal SQL with FOXCDX databases

#3 Post by PedroAlex »

Very interesting.

I had tried compile this sample and I have this error :

XB V2.0.703

Best regards
Pedro
Attachments
SQL_Error.PNG
SQL_Error.PNG (5.95 KiB) Viewed 18444 times
Pedro Alexandre

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Using Universal SQL with FOXCDX databases

#4 Post by rdonnay »

I believe that Universal SQL is part of the base foundation product.
The above sample requires eXpress++ to display the results.

No other SQL system is required.
You DO NOT need SQL Express, ADSDBE or ODBCDBE.

This is now built into the Xbase++ language.

You need the latest build of Xbase++ 2.0. (build 742 or later)

Alaska fixed a bug in the WHERE clause.

For now, You can comment out WHERE Invoice->balance > 0 .
The eXpress train is coming - and it has more cars.

Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: Using Universal SQL with FOXCDX databases

#5 Post by Victorio »

Hi,
I want examine this, but error when compiling :

But I have XBase 2.0 build 554, is this problem ?

[Hint] : Pbuild.exe project.xpj /a
[Hint] : File C:\exp19\Samples\SQL20\FoxSql.prg successfully compiled.
[Hint] : #Syntax Error in Line(1) Pos(16) : org.antlr.runtime.MissingTokenException#Syntax Error in Line(1) Pos(16) : org.antlr.runtime.MissingTokenException#Syntax Error in Line(1) Pos(28) : org.antlr.runtime.MissingTokenException#Syntax Error in Line(1) Pos(28) : org.antlr.runtime.MissingTokenException#Syntax Error in Line(1) Pos(16) : org.antlr.runtime.MissingTokenException#Syntax Error in Line(1) Pos(16) : org.antlr.runtime.MissingTokenException#Syntax Error in Line(1) Pos(16) : org.antlr.runtime.MissingTokenException#Syntax Error in Line(1) Pos(16) : org.antlr.runtime.MissingTokenException#Syntax Error in Line(1) Pos(16) : org.antlr.runtime.MissingTokenException#Syntax Error in Line(1) Pos(16) : org.antlr.runtime.MissingTokenException
[Hint] : FOXSQL.Exe created successfully.
[Hint] : File C:\exp19\Samples\SQL20\ArraySql.prg successfully compiled.
[Hint] : #Syntax Error in Line(1) Pos(16) : org.antlr.runtime.MissingTokenException#Syntax Error in Line(1) Pos(16) : org.antlr.runtime.MissingTokenException
[Hint] : ArraySQL.Exe created successfully.
[Hint] : Processing time: 0.54 secs.
[Hint] : DONE

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Using Universal SQL with FOXCDX databases

#6 Post by rdonnay »

But I have XBase 2.0 build 554, is this problem ?
I don't have build 554 anymore.
There were many problems with Universal SQL in that older build.
I suggest that you get the latest version.
The eXpress train is coming - and it has more cars.

Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: Using Universal SQL with FOXCDX databases

#7 Post by Victorio »

ok, thank you.

User avatar
pedroah
Posts: 28
Joined: Wed Nov 05, 2014 7:15 pm
Location: Dominican Republic
Contact:

Re: Using Universal SQL with FOXCDX databases

#8 Post by pedroah »

Thanks Roger, This is a Excellent Sample

One Question

May we use a variable in where condition, like this :

Local dDate := ctod('02/11/2017')

Select * from Invoice where ship_date = dDate

Thanks in advanced

Post Reply