Page 1 of 1
ADir() and Directory() in PGDBE
Posted: Tue Jul 29, 2014 1:18 am
by Piotr D
Hi,
how can I simulate ADir() and Directory() functions on PostgreSQL tables?
Piotr
Re: ADir() and Directory() in PGDBE
Posted: Tue Jul 29, 2014 12:52 pm
by Auge_Ohr
Piotr D wrote: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.
Re: ADir() and Directory() in PGDBE
Posted: Tue Jul 29, 2014 11:04 pm
by 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
Re: ADir() and Directory() in PGDBE
Posted: Wed Jul 30, 2014 5:25 pm
by Auge_Ohr
Piotr D wrote: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.
Re: ADir() and Directory() in PGDBE
Posted: Wed Jul 30, 2014 11:59 pm
by 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
Re: ADir() and Directory() in PGDBE
Posted: Thu Jul 31, 2014 8:05 am
by Auge_Ohr
Piotr D wrote: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/
Re: ADir() and Directory() in PGDBE
Posted: Fri Aug 01, 2014 12:45 am
by 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