SELECT with an ALIAS

[rdonnay]

The below code doesn't work because I can't find any way to assign an alias to the workarea after a SELECT statment. The alias always comes back as QUERY.

The docs claim that I should be able to use the AS <cAliasName> but it will not work no matter how I try. Has anyone done this?

See the below code:

Code: Select all

#include "pgdbe.ch"
#include "dcdialog.CH"

#Pragma Library("dclipx.lib")
#Pragma Library("dclip1.lib")

FUNCTION Main

LOCAL oSession, cConnStr, GetList[0], oBrowse, cFieldName, i, aStru

DbeLoad("pgdbe")
DbeSetDefault("pgdbe")

cConnStr := "DBE=pgdbe;server=localhost;"
cConnStr += "db=Topline;uid=postgres;pwd=postgres"

oSession := DacSession():New(cConnStr)
IF(!oSession:IsConnected())
  MsgBox("Connection failed ("+Var2Char(oSession:GetLastMessage())+")")
  QUIT
ENDIF

/* We can not use File() to verify if a table exists. Use Table()
 * instead. Table() works with dbf tables, sql tables, local or
 * remote.
*/

BEGIN SEQUENCE

MsgBox("Connected to PostgreSQL server")
IF !Table("Parts")
  MsgBox("No PARTS table")
  BREAK
ENDIF

SELECT * FROM PARTS

aStru := PARTS->(dbStruct())

@ 0,0 DCBROWSE oBrowse ALIAS 'PARTS' SIZE 100,20

FOR i := 1 TO Len(aStru)

  cFieldName := aStru[i,1]

  DCBROWSECOL DATA DC_FieldWBlock(cFieldName,'PARTS') ;
     HEADER cFieldName WIDTH 10 PARENT oBrowse

NEXT

DCREAD GUI FIT TITLE 'Browsing PARTS using PostGreSql'

END SEQUENCE

oSession:Disconnect()

RETURN nil

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

PROC appsys ; RETURN

[skiman]

Hi Roger,

In your code there is no USE PARTS ALIAS PARTS.

Maybe it is not needed for your SQL queries as your select, but if you want to use the ALIAS namen, I suppose you have to use the USE command.

[rdonnay]

USE PARTS ALIAS PARTS works fine.

I'm trying to do a SQL SELECT statement instead.

I tried the following

SELECT * FROM PARTS ALIAS PARTS
SELECT * FROM PARTS AS PARTS
SELECT * FROM PARTS INTO CURSOR PARTS

None of these work.

[Piotr D]

Roger,
try executeQuery() method, it will work fine.

oSession:executeQuery("SELECT * FROM PARTS","PARTS",.T.)
SELECT PARTS
DbGoTop()

Piotr

[pauld]

We also used executequery() to place the results in a workarea.

I know this is a couple of years old but I just wanted to clarify that Alias in postgres doesn't work the same way as it does with dbf.

Here's an example of Alias in postgres:

SELECT * FROM clients AS c, houses AS h WHERE c.recordid = h.clientrid

It allows you to use shorthand so you don't have to fully type out client.recordid when specifying a table, but it doesn't become the alias for Client throughout your code. So you wouldn't be able to use it any where else other than that query statement.

[rdonnay]

I understand what Aliasing in a SELECT statement does.

That's not what I was talking about.

I found out that Alaska still does not support this feature, even though it is in their documentation.

I have decided to stay with Advantage Server for my SQL needs.
It works with an Xbase++ work area.