ADir() and Directory() in PGDBE

[Piotr D]

Hi,
how can I simulate ADir() and Directory() functions on PostgreSQL tables?

Piotr

[Auge_Ohr]

how can I simulate ADir() and Directory() functions on PostgreSQL tables?

Code: Select all

cSql := "select * from information_schema.tables where table_catalog = 
'" + cDb + "' and table_schema = '" + cSchema + "' and table_name = '" + 
cTabel + "'"

Sample

Code: Select all

    select table_name from information_schema.tables
       where table_catalog = 'mdidemo'
       and   table_schema = 'public'

to check a single Table

Code: Select all

cSql := "SELECT Count(table_name) AS count FROM information_schema.tables WHERE table_name='%1'"

or just use Xbase++ v2.x TABLE() Function.

[Piotr D]

Jimmy,
when I use directly in my source:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like 'V_%' ORDER BY TABLE_NAME


I becam an error during compilation

[Hint] : usqlstmt(1) : lexer error 1 :
[Hint] : Unexpected character at offset 43, near 'A' :
[Hint] : ABLES WHERE TABLE_NA

What's wrong?
Piotr

[Auge_Ohr]

Code: Select all

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like 'V_%' ORDER BY TABLE_NAME

[Hint] : usqlstmt(1) : lexer error 1 :
[Hint] : Unexpected character at offset 43, near 'A' :
[Hint] : ABLES WHERE TABLE_NA

table_name is a sql_identifier which you can not use this Way.

read more about INFORMATION_SCHEMA in Postgre Documentation : 34.1. The Schema

i use PgAdmin.EXE to test my SQL Query.

[Piotr D]

Jimmy,
I now that these command work properly "inside" PgAdmin. I use these with ODBCDBE (with MSSQL) and in
this case I can "USE" table with these information. Alaska's Xbase 2.0 Guide is incomplete, and informations
about use PgDbe with SQL language are very poor...

Piotr

[Auge_Ohr]

Alaska's Xbase 2.0 Guide is incomplete, and informations about use PgDbe with SQL language are very poor...

when using PostgreSQL with pgDBE as Backend you must not use SELECT Query.
pgDBE will "translate" your ISAM Style Xbase Command into a SELECT Query.

when you want to learn "real" PostgreSQL Command you have to read PostgreSQL Documentation
http://www.postgresql.org/docs/manuals/

[Piotr D]

Here is simple code:

FUNCTION D_SQL_Adir(cTable,aDirTable)
***************************************************
LOCAL cSQL := "",cSel:= ALIAS(),cTemp:='cTMP',nRet:=0

cTable:=STRTRAN(STRTRAN(cTable,'?','_'),'*','%')

cSQL := "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE "+;
"TABLE_NAME like '"+cTable+"' ORDER BY TABLE_NAME;"

oSession:executeQuery(cSQL,cTemp,.T.)

select cTMP
DbGoTop()
IF aDirTable#NIL
aDirTable:={}
DO WHILE !EOF()
AAdd(aDirTable,TABLE_NAME)
skip
ENDDO
ENDIF
nRet:=Lastrec()
USE

IF !EMPTY(cSel)
SELECT &cSel
ENDIF
RETURN(nRet)


Piotr