Hello,

I successfully connected to a PostgreSQL dbms from Xbase 2.0. I would 
like to issue SQL commands like CREATE TABLE, DROP TABLE, INSERT INTO, 
UPDATE, DELETE, SELECT. The chapters under 'Universal SQL' and 
'Pass-Through SQL' in the docs are still empty. (I really don't know 
what improvements I can hope in this area...)

Could someone show me a few examples, please?

The following CREATE TABLE command did not work in my code:

-----------
...
    cConnStr := 
"DBE=pgdbe;server=192.168.0.1;db=kk2016;uid=tsuser;pwd=secret"
    oSession := DacSession():New(cConnStr)
    IF !oSession:isConnected()
       MsgBox("Error...")
       QUIT
    ENDIF

    CREATE TABLE TS20U (VKOD Character(2), UJEL Character(1), UKOD 
Character(5), UNEV Character(45))

oSession:disConnect()
...
-----------

Thank You!

Regards,
Otto

Hello,

I managed to use CREATE TABLE, the following code WORKED!

 >>>
PROCEDURE Main
    LOCAL cConnStr, oSession

    IF !Dbeload("PGDBE", .F.)
      MsgBox("Error loading PGDBE...", "Error")
      QUIT
    ENDIF
    DbeSetDefault("PGDBE")

    cConnStr := 
"DBE=pgdbe;server=192.168.0.1;db=kk2016;uid=tsuser;pwd=secret"
    oSession := DacSession():New(cConnStr)
    IF !oSession:isConnected()
       MsgBox("Error connecting to server...", "Error")
       QUIT
    ENDIF

    CREATE TABLE TS20U (;
         VKOD        Character(2),  ;
         UJEL        Character(1),  ;
         UKOD        Character(5),  ;
         UNEV        Character(45);
    )

    oSession:disConnect()
RETURN
 >>>

The "ISAM fields" were automatically created:

   __deleted boolean NOT NULL DEFAULT false,
   __record serial NOT NULL,
   __rowversion integer NOT NULL DEFAULT 0,
   __keyversion integer NOT NULL DEFAULT 0,
   __lock_owner integer NOT NULL DEFAULT 0,
   CONSTRAINT ts20u_pkey PRIMARY KEY (__record)

Regards,
Otto

Hello,

Issuing the following INSERT INTO command gave me a runtime error.

INSERT INTO TS20U (VKOD, UJEL, UKOD, UNEV) VALUES ('TR', 'V', '    5', 
'Yoyodyne Inc.') VIA (oSession)

(I put this line in my code where the CREATE TABLE part was previously.)

Did I something wrong or is the command incomplete?

Otto



------------------------------------------------------------------------------
ERROR LOG of "D:\FEJLESZT\SQL\TSQL.EXE" Date: 04/05/2016 13:46:39

Xbase++ version     : Xbase++ (R) Version 2.00.656
Operating system    : Windows 10 10.00 Build 10586
------------------------------------------------------------------------------
oError:args         :
           -> VALTYPE: U VALUE: NIL
oError:canDefault   : Y
oError:canRetry     : N
oError:canSubstitute: Y
oError:cargo        : NIL
oError:description  : No implementation of class(PgSqlStatement) found
oError:filename     :
oError:genCode      :       9999
oError:operation    : DACSQLSTATEMENT
oError:osCode       :          0
oError:severity     :          2
oError:subCode      :       9999
oError:subSystem    : USQL
oError:thread       :          1
oError:tries        :          0
------------------------------------------------------------------------------
CALLSTACK:
------------------------------------------------------------------------------

Hello,

As I haven't succeeded with INSERT INTO I tried the following code which 
worked. But it took 363.78 sec (6 minutes!!!) to insert 6975 rows.

    USE TS20U VIA "DBFNTX" NEW
    USE TS20U ALIAS TS20USQL VIA (oSession) NEW
    aStrukt:= TS20U->(DbStruct())
    aAdat:= Array(Len(aStrukt))
    TS20U->(DbGoTop())
    DO WHILE !TS20U->(Eof())
       AFill(aAdat, "")
       FOR nI:=1 TO Len(aAdat)
          aAdat[nI]:= TS20U->(FieldGet(nI))
       NEXT
       TS20USQL->(DbAppend())
       FOR nI:=1 TO Len(aAdat)
          TS20USQL->(FieldPut(nI, aAdat[nI]))
       NEXT
       TS20U->(DbSkip())
    ENDDO
    CLOSE TS20U
    CLOSE TS20USQL

Then I modified the code so that the target table was again a DBF file 
in a shared folder (actually on the same machine that is running the 
PostgreSql dbms) and now inserting the 6975 lines lasted 17.26 seconds.

Finally if the target file was a DBF again but on my local machine just 
as the source DBF then the whole operation took 1 second.

Probably the INSERT INTO command would be much faster than the ISAM way 
but it doesn't work (for me at least).

Any suggestions, explanations are welcome on how to insert many rows 
with PGDBE effectively!

Thanks!

With Regards,
Otto

Hello,

I played a little with UPDATE command too:

* updating 1 field worked:
UPDATE TS20U SET UNEV:="Mrs. Robinson" WHERE UKOD==" 4190"

* updating more fields did not work (gave runtime error #1):
UPDATE TS20U SET UNEV:="Mrs. Robinson", UHELY:="Budapest" WHERE UKOD==" 
4190"
   Why is there 2 'SET' keywords in the log?

* updating more fields from an array (which has elements) did not work 
(gave a runtime error):
UPDATE TS20U FROM aData WHERE UKOD == " 4190"



ERROR #1:
------------------------------------------------------------------------------
ERROR LOG of "D:\FEJLESZT\SQL\TSQL.EXE" Date: 04/06/2016 15:36:20

Xbase++ version     : Xbase++ (R) Version 2.00.656
Operating system    : Windows 10 10.00 Build 10586
------------------------------------------------------------------------------
oError:args         :
           -> VALTYPE: U VALUE: NIL
oError:canDefault   : Y
oError:canRetry     : N
oError:canSubstitute: Y
oError:cargo        : {NIL,         -1, syntax error at or near "uhely"
LINE 1: UPDATE ts20u SET unev = 'Mrs. Robinson', SET uhely = 'Buda...
                                                        ^}
oError:description  : syntax error at or near "uhely"
LINE 1: UPDATE ts20u SET unev = 'SĆóndor IstvĆónnĆc', SET uhely = 'BalĆó...
                                                        ^
oError:filename     :
oError:genCode      :       9001
oError:operation    : ABSTRACTSQLSTATEMENTBUILDER:EXECUTESTATEMENT
oError:osCode       :          0
oError:severity     :          2
oError:subCode      :         -1
oError:subSystem    : Remote SQL dacSession
oError:thread       :          1
oError:tries        :          0
------------------------------------------------------------------------------
CALLSTACK:
------------------------------------------------------------------------------
Called from MAIN(22)



ERROR #2:
------------------------------------------------------------------------------
ERROR LOG of "D:\FEJLESZT\SQL\TSQL.EXE" Date: 04/06/2016 16:11:40

Xbase++ version     : Xbase++ (R) Version 2.00.656
Operating system    : Windows 10 10.00 Build 10586
------------------------------------------------------------------------------
oError:args         :
           -> VALTYPE: C VALUE:
           -> VALTYPE: N VALUE: 1
oError:canDefault   : N
oError:canRetry     : N
oError:canSubstitute: Y
oError:cargo        : NIL
oError:description  : Error in array index
oError:filename     :
oError:genCode      :          4
oError:operation    : <C of 0>[<1>]
oError:osCode       :          0
oError:severity     :          2
oError:subCode      :       1012
oError:subSystem    : BASE
oError:thread       :          1
oError:tries        :          0
------------------------------------------------------------------------------
CALLSTACK:
------------------------------------------------------------------------------
Called from PGSQLSTATEMENT:REWRITETABLE(66)
Called from ABSTRACTSQLSTATEMENTBUILDER:FROM(283)
Called from MAIN(29)


Has anybody tried these with success?

Thanks.

Regards,
Otto

hi,

> * updating more fields did not work (gave runtime error #1):
> UPDATE TS20U SET UNEV:="Mrs. Robinson", UHELY:="Budapest" WHERE UKOD==" 
> 4190"
>   Why is there 2 'SET' keywords in the log?
>
> * updating more fields from an array (which has elements) did not work 
> (gave a runtime error):
> UPDATE TS20U FROM aData WHERE UKOD == " 4190"

try PgAdmin.EXE or PSql.EXE if your Syntax will work ... than it is a Bug in 
Alaska Software.

p.s. you can native access PostgreSQL Server using libpq.dll (32bit). have a 
look at www.xbwin.com

Hello Jim,

>
> try PgAdmin.EXE or PSql.EXE if your Syntax will work ... than it is a Bug in
> Alaska Software.

I use PgAdmin side by side with my sample program. In PgAdmin the first 
two UPDATEs were successful. But sometimes the syntax differs, eg. 
UPDATE FROM an array is (would be) only possible in Xbase.

> p.s. you can native access PostgreSQL Server using libpq.dll (32bit). have a
> look at www.xbwin.com

Where can I download the .dll from? Is there any documentation?

Thanks,

Otto

Hello,

I tried DELETE as well. The following line gave me a runtime error (see 
it below.)

DELETE FROM TS20U WHERE UKOD == " 4190"


If I do it the ISAM way, then it works and I can see in PgAdmin that the 
__deleted field of the row is changed to 'true'. For this I had to 
create an index of course (INDEX ON VKOD+UJEL+UKOD TO TS20UK) which took 
81 seconds for the 6975 records. (As a comparison it took 0.1 sec in 
case of a local DBF/NTX and 0.5 sec if the DBF is residing in the shared 
folder).

USE TS20U INDEX TS20UK NEW SHARED
TS20U->(DbSeek("TSV 4190"))
IF TS20U->(Found())
    TS20U->(Rlock())
    TS20U->(DbDelete())
    TS20U->(DbUnlock())
ENDIF


the error log:
------------------------------------------------------------------------------
ERROR LOG of "D:\FEJLESZT\SQL\TSQL.EXE" Date: 04/07/2016 10:19:14

Xbase++ version     : Xbase++ (R) Version 2.00.656
Operating system    : Windows 10 10.00 Build 10586
------------------------------------------------------------------------------
oError:args         :
           -> VALTYPE: A VALUE: {"TS20U"}
oError:canDefault   : N
oError:canRetry     : N
oError:canSubstitute: Y
oError:cargo        : NIL
oError:description  : Parameter has a wrong data type
oError:filename     :
oError:genCode      :          2
oError:operation    : allTrim
oError:osCode       :          0
oError:severity     :          2
oError:subCode      :          3
oError:subSystem    : BASE
oError:thread       :          1
oError:tries        :          0
------------------------------------------------------------------------------
CALLSTACK:
------------------------------------------------------------------------------
Called from PGSQLSTATEMENT:REWRITETABLE(60)
Called from ABSTRACTSQLSTATEMENTBUILDER:DELETE(274)
Called from MAIN(22)


Regards,
Otto

Hello,

- SELECT * FROM TS20U VIA (oSession) INTO TS20U

worked and it gave the result set of 6975 records in a work area (TS20U) 
within 0.5 second (!).

I could go through the workarea with the familiar code:
DO WHILE !TS20U->(Eof())
    ? TS20U->(UKOD+" "+UNEV)
    TS20U->(DbSkip())
ENDDO

- SELECT * FROM TS20U ORDER BY UKOD LIMIT 50 OFFSET 3400 VIA (oSession) 
INTO TS20U

gave the result set of 10 records in 0.07 sec (!)

- SELECT VKOD, UJEL, UKOD, UNEV FROM TS20U ORDER BY UNEV VIA (oSession) 
INTO TS20U

worked nice and even faster because of the fewer columns.

- SELECT VKOD, UJEL, UKOD, UNEV FROM TS20U ORDER BY UNEV VIA (oSession) 
INTO ARRAY aAdat

on the other hand did not work for me, aAdat remained intact (NIL in my 
case).


Regards,
Otto