Does anyone use Universal SQL ?

[rdonnay]

I can't believe that this stuff still doesn't work.

The last time I tried this, I gave up because I couldn't get even the simplest SELECT statements to compile.

So, I thought that I would give this another try, now that it is 3 years later and many more new builds.

This will not compile:

Code: Select all

STATIC FUNCTION ReSort( cFieldName, oSession, oBrowse )

SELECT * FROM customers ORDER BY (cFieldName) VIA (oSession) INTO Customers

oBrowse:refreshAll()

RETURN nil

error XBTS600: Missing CMD_SELECT in SELECT projection/fieldlist of SELECT command

This seems to be another huge waste of my time.
The only way I can ever get SQL to work is via ODBCDBE or ADSDBE.

[Tom]

Hi, Roger.

From the docs:
Local SQL supports any kind of expression in the ORDER BY clause. With remote SQL only field names are allowed.

I started working with Universal SQL some weeks ago, but I'm still at the beginning. It safes lots of code, and the ... ehem .. new DataObjects make it very easy to work with the result like this:

Code: Select all

USE MyTable NEW
SELECT * FROM MyTable INTO OBJECTS aMyTable

But this code takes twice the time of a code reading all lines from the table into a simple two-dim array. Anyway, the array of DataObjects is very elegant.

[Tom]

Besides. Creating a workarea from a sql statement is "not implemented yet". So, this:

Code: Select all

SELECT * FROM customers INTO cust

does not (yet) work anyway. You must create an array or an array of data objects. And if you try to order by a field which has no index, executing the statement is very, very slow.

[skiman]

Hi,

Currently we are testing the following.
https://github.com/postgraphql/postgraphql

The idea is to use an API, which is the same for our Xbase application, as our webbased application that we are developing.
The GraphQL API which you can create easily with postgraphQL provides all we need.

[rdonnay]

Local SQL supports any kind of expression in the ORDER BY clause. With remote SQL only field names are allowed.

I missed that in the docs.
This makes it very difficult to emulate just a simple browse with a SORT on the header.

Up to now, I have been using an array of data objects to do this, but there are big limitations because (as you said), the entire workarea needs to be traversed to load the array.

I had a customer here last week who is trying to migrate to PostGreSQL and that was my recommendation to him, except that I suggested that he use the ODBCDBE and SQL statements in a TEXT INTO rather than trying to create a SQL statement using Universal SQL and PGDBE.

I am still trying to figure out how to use my own set of functions with PGDBE.

I just want to do this simple thing:

Code: Select all

FUNCTION LoadCursor( cOrder, cAlias, oSession )

LOCAL cSQL, cAlias, lStatus

TEXT INTO cSQL
SELECT * FROM Customers ORDER BY ?
ENDTEXT

cSQL := DC_ApplySQLParams( cSQL, { cOrder )}

lStatus := SqlStmtExec(cSql,oSession,,,.F.)

IF lStatus
  dbUseArea( .T., (oSession), (cSql), "CUSTOMERS")
ENDIF

RETURN lStatus

I do this all the time with the ODBCDBE and Microsoft SQLServer.
I'm sure that I can also do this with the ODBCDBE and PostGreSQL.

However, I'm being told (by Steffen) that PGDBE and Universal SQL is the way to migrate and I don't get it.
It appears that nobody is using this.

We appear to have another communication problem with Alaska.

[rdonnay]

I found a solution that required compiling with a /P to see the pre-processed code.

For example this:

Code: Select all

SELECT * FROM customers ORDER BY cFieldName VIA (oSession) INTO customers

Pre-processes to this:

Code: Select all

DacSqlStatement( oSession ):Select( {{ 1 }} ):From( {{"customers"}} ):OrderBy( {{"cFieldName", 1}} ):Build():Query( 1, "Customers" )

If I replace the command with the pre-processed code and then remove the quotes around "cFieldName", then everything works as desired. I'm hoping that I can solve this problem by creating my own SQL command that handles the pre-processing and overloads the SQL command embedded in the compiler.

[rdonnay]

Here is a small eXpress++ program that browses the Northwind customer table.
It assumes that you have already installed the Northwind database into PostGreSQL server by following the instructions in the Alaska documentation. It also assumes that you have an environment variable named POSTGRESQL_PASSWORD which contains the password.

It will load all fields into a browse and then allow any column to be sorted by right-clicking the column header.

Code: Select all

#INCLUDE "dcdialog.ch"
// PostgresSQL DBE header file is required
#include "pgdbe.ch"

FUNCTION Main()

LOCAL cConnect, oSession, i, oBrowse, aStru, GetList[0], GetOptions

// Load the PostgreSQL DatabaseEngine
IF(!DbeLoad("pgdbe"))
  Alert( "Unable to load the PostgreSQL DatabaseEngine", {"Quit"} )
  QUIT
ENDIF

// Establish the connection
cConnect := "DBE=pgdbe;SERVER=localhost;DB=northwind;UID=postgres;PWD=" + GetEnv('POSTGRESQL_PASSWORD')

oSession := DacSession():New( cConnect )

// Check for connection success
IF .NOT. oSession:isConnected()
  DCMSGBOX oSession:GetLastMessage(), ;
           "Unable to establish connection to server"
  QUIT
ENDIF

SELECT * FROM customers VIA (oSession) INTO Customers

@ 0,0 DCBROWSE oBrowse ALIAS 'Customers' SIZE 120,25 ;
      RESIZE DCGUI_RESIZE_RESIZEONLY

aStru := Customers->(dbStruct())

FOR i := 1 TO Len(aStru)
  DCBROWSECOL DATA FieldWBlock(aStru[i,1],'Customers') ;
    HEADER DC_CapFirst(aStru[i,1]) WIDTH Min(aStru[i,3],10) PARENT oBrowse ;
    SORT SortBlock(aStru[i,1],oSession,@oBrowse) 
NEXT

DCGETOPTIONS RESIZE
DCREAD GUI FIT TITLE 'PostGreSQL test' OPTIONS GetOptions

// Disconnect from server
oSession:disconnect()

RETURN nil

* -----------

PROCEDURE Appsys ; RETURN

* -----------

STATIC FUNCTION SortBlock( cFieldName, oSession, oBrowse )

RETURN {||Resort(cFieldName,oSession,oBrowse)}

* -----------

STATIC FUNCTION ReSort( cFieldName, oSession, oBrowse )

LOCAL oStatement

CUSTOMERS->(dbCloseArea())

oStatement := DacSqlStatement(oSession)
oStatement:Select( {{ 1 }} )
oStatement:From( {{"customers"}} )
oStatement:OrderBy( {{cFieldName, 1}} )
oStatement:Build()
oStatement:Query( 1, "Customers" )

oBrowse:refreshAll()

RETURN nil

[unixkd]

Hi Roger

Everything comes performance.

What happen when browsing a table with hundred of thousands of records ?

Browsing millions of records with ADS has no performance penalties.

Thanks

Joe