Hello everyone,

I'm considering the use of postgre, but I first wanted to hear from 
people who have successfully converted or who have tried and abandoned it.

It seems like there should be a lot more messages here if a lot of us 
are using it successfully.

Thanks,

Scott

If you mean Postgres as sql server - yes plenty , but not with PGDBE, but with ODBC and sqlexpress.



Scott Krise wrote in message news:3f2f6488$117fd3d5$a8aea@news.alaska-software.com...
>Hello everyone,
>
>I'm considering the use of postgre, but I first wanted to hear from 
>people who have successfully converted or who have tried and abandoned it.
>
>It seems like there should be a lot more messages here if a lot of us 
>are using it successfully.
>
>Thanks,
>
>Scott

On 14/11/19 0:56, Matej Jurac wrote:
> If you mean Postgres as sql server - yes plenty , but not with PGDBE, but with ODBC and sqlexpress.
> 
> 
> 
> Scott Krise wrote in message news:3f2f6488$117fd3d5$a8aea@news.alaska-software.com...
>> Hello everyone,
>>
>> I'm considering the use of postgre, but I first wanted to hear from
>> people who have successfully converted or who have tried and abandoned it.
>>
>> It seems like there should be a lot more messages here if a lot of us
>> are using it successfully.
>>
>> Thanks,
>>
>> Scott
+1

Yes, that is what I meant. So is there a reason you choose sqlexpress over 
PGDBE? Are you still able to use the ISAM style logic? Have you had any 
issues like Peter described in this thread with using sql statements in your 
programs?

Thanks!!

"Matej Jurac" wrote in message 
news:1b3ef94d$7e6399d8$ab598@news.alaska-software.com...

If you mean Postgres as sql server - yes plenty , but not with PGDBE, but 
with ODBC and sqlexpress.



Scott Krise wrote in message 
news:3f2f6488$117fd3d5$a8aea@news.alaska-software.com...
>Hello everyone,
>
>I'm considering the use of postgre, but I first wanted to hear from
>people who have successfully converted or who have tried and abandoned it.
>
>It seems like there should be a lot more messages here if a lot of us
>are using it successfully.
>
>Thanks,
>
>Scott

> So is there a reason you choose sqlexpress over PGDBE?

I started with xpp + SQL way before pgdbe driver was even planned (somewhere in 2003); for begin it was interfaced xbase to cavo.v2 then sqlexpress was announced and purchased for beeing more easily integrated.


> Are you still able to use the ISAM style logic?
Some of it is mostly same (except notation because you use object not alias) like reading fields, skipping, writing etc for sure, but for getting data it is something completly different, a bit harder but also way more powerful and fleksible.
In my case .DBF are currently (except for some legacy apps) delegated to local & temporary data storage for non sensitive data.


> Have you had any issues like Peter described in this thread with using sql statements in your programs?
ODBC is not fastest protocol out there, but it is at least standardized and a lot of people work with it (mind you do not need to know about inside works to use it for regular data manipulation), drivers are plentyful and apart from syntax quirks of different DBMS outside pure ANSI SQL (looking sterny at MySql) the same code goes for many platforms.

It is slow on high latency networks but that can be avoided either by terminal services or using REST (or more complicated SOAP) access to data.

Can't comment on PGDBE , I just do not use it outside minor usage for some consulting work.

See - customers demand features like encryption and security+control on multiple and different dbms platforms beyond what PGDBE can provide.

Currently problem with .dbf goes part with data security and GDPR compliance as they are accessible via file access; can be encoded (sure) but it is way harder to protect them from copying, viewing and so on compared to sql with proper security.




"Scott Krise" <scottkrise@verizon.net> wrote in message news:1ea24a5$470eaba4$b5670@news.alaska-software.com...
>Yes, that is what I meant. So is there a reason you choose sqlexpress over 
>PGDBE? Are you still able to use the ISAM style logic? Have you had any 
>issues like Peter described in this thread with using sql statements in your 
>programs?
>
>Thanks!!
>
>"Matej Jurac" wrote in message 
>news:1b3ef94d$7e6399d8$ab598@news.alaska-software.com...
>
>If you mean Postgres as sql server - yes plenty , but not with PGDBE, but 
>with ODBC and sqlexpress.
>
>
>
>Scott Krise wrote in message 
>news:3f2f6488$117fd3d5$a8aea@news.alaska-software.com...
>>Hello everyone,
>>
>>I'm considering the use of postgre, but I first wanted to hear from
>>people who have successfully converted or who have tried and abandoned it.
>>
>>It seems like there should be a lot more messages here if a lot of us
>>are using it successfully.
>>
>>Thanks,
>>
>>Scott

Scott,

> I'm considering the use of postgre, but I first wanted to hear from 
> people who have successfully converted or who have tried and abandoned it.
> 
> It seems like there should be a lot more messages here if a lot of us 
> are using it successfully.

As far as I can see it the ISAM way of using PostgreSQL is a viable option.
You might still experience quite a bit of trouble using the SQL statements
that became part of the Xbase++ language. I still have a few PDR's open on
basic functionality.
I think that is indeed the reason for lack of comment and questions here.

Peter

Thanks Peter. How was the performance after changing? I am using dbf/ntx 
tables now.

And are you using PGDBE or ODBC and sqlexpress?

Thanks!

"Peter Alderliesten" wrote in message 
news:hws7k1b5g897$.ikn7u5ajh5gz$.dlg@40tude.net...

Scott,

> I'm considering the use of postgre, but I first wanted to hear from
> people who have successfully converted or who have tried and abandoned it.
>
> It seems like there should be a lot more messages here if a lot of us
> are using it successfully.

As far as I can see it the ISAM way of using PostgreSQL is a viable option.
You might still experience quite a bit of trouble using the SQL statements
that became part of the Xbase++ language. I still have a few PDR's open on
basic functionality.
I think that is indeed the reason for lack of comment and questions here.

Peter

Scott,

> Thanks Peter. How was the performance after changing? I am using dbf/ntx 
> tables now.

Because the prominence of these issues I stopped developing in SQL for time
being. 

Peter

hi,

> How was the performance after changing? I am using dbf/ntx tables now.

it will be better when have many Data ( > 1,000,000)

> And are you using PGDBE or ODBC and sqlexpress?

i'm using PostgreSQL native without ISAM ... which is much quicker than 
PgDBE

p.s. Xbase++ v2.x use a lot of Things which are NOT xBase compatible !

On 11/18/2019 5:03 PM, Jim Lee wrote:
> hi,
> 
>> How was the performance after changing? I am using dbf/ntx tables now.
> 
> it will be better when have many Data ( > 1,000,000)
> 
>> And are you using PGDBE or ODBC and sqlexpress?
> 
> i'm using PostgreSQL native without ISAM ... which is much quicker than
> PgDBE
> 
> p.s. Xbase++ v2.x use a lot of Things which are NOT xBase compatible !
> 
> 
What do you mean? You are not using the same ISAM code as you had prior 
to PostgreSQL? You are using only SQL commands?

One other question...what is the preferred version of PostgreSQL? The 
xbase documentation mentions 8.3.x....but the PostgreSQL site now has 
version 12.1 available. I didn't see anything within the Xbase install 
to install the server so I assume everyone is getting the download from 
the PostgreSQL site?

hi.,

> What do you mean? You are not using the same ISAM code as you had prior to 
> PostgreSQL? You are using only SQL commands?

YES, only SL command.
it does not make sence to convert a ISAM Style ... simple to slow.

> One other question...what is the preferred version of PostgreSQL? The 
> xbase documentation mentions 8.3.x....but the PostgreSQL site now has 
> version 12.1 available. I didn't see anything within the Xbase install to 
> install the server so I assume everyone is getting the download from the 
> PostgreSQL site?

you still can use LibPQ.DLL Version v7.x with Server v12.x if you do not use 
SSL.
newer Server have new SQL Function like row_number() which can be used by 
Client.

but Xbase++ is still limited to 32 Bit Version of LibPQ.DLL so i'm not sure 
what happend if Resul-Set have a Adress > 4 GB

---

we just talk about PostgreSQL but what is which MySQL (mariaDB) or other SQL 
Server ?
did you think you will learn it when use PgDBE ?

IHMO you have to learn SQL to use it. else you never get the Performance.
i have test other xBase Dialect using RDD but they all work only good when < 
1,000,000 Row

>we just talk about PostgreSQL but what is which MySQL (mariaDB) or other 
>SQL Server ?
>did you think you will learn it when use PgDBE ?

Not sure I understand what you are saying here. Are you saying you don't 
have to use PostgreSQL as your SQL server? You can use MySQL instead? I am 
very familiar with MySQL...and the syntax etc. I assume that you need the 
PostgreSQL server within xbase to make this all work. Is that not true?


>IHMO you have to learn SQL to use it. else you never get the Performance.
>i have test other xBase Dialect using RDD but they all work only good when 
>< 1,000,000 Row

Im already very strong in sql...just have A LOT of software written for 
DBF/NTX tables and it’s a huge undertaking to rewrite everything. PostgreSQL 
seems to offer me a solution where I can use what I have invested so much 
time in, and as time allows, rewrite programs that could most benefit from 
the SQL performance advantages. But now I think im hearing that postgreSQL 
isn't really a finished product yet where that is possible due to bugs when 
you use some SQL commands.

i does not make sence to talk about it ... get Demo and try it yourself.

Alaska is working since 2012 on PgDBE. some in German Forum work with it and 
have show use how it work.
you can work with it if you accept that "SEEK" use 1.6 Sec (with SQL Index) 
when have 200,000 Row.

have a look for "PgDBE" in PDR ... there are a lot
have fun

Dear Mr. Lee,

>you can work with it if you accept that "SEEK" use 1.6 Sec (with SQL Index) 
>when have 200,000 Row.

Simple impossible, except you are talking about the early stages of
the ISAM emulation where we didn't have created indexes automatically.

If you have created the index manually, keep in mind you need two SQL
indexes with two different operator-implementations to support
full-key as well as partial-key searches.

Today, the ISAM emulation is with deleted records filtered out in a
hundreds of thousands of records scenario in the millisecond range on
average hardware.

regards
Steffen F. Pirsig 
Alaska Software


On Wed, 20 Nov 2019 22:31:59 +0100, Jim Lee wrote:

>i does not make sence to talk about it ... get Demo and try it yourself.
>
>Alaska is working since 2012 on PgDBE. some in German Forum work with it and 
>have show use how it work.
>you can work with it if you accept that "SEEK" use 1.6 Sec (with SQL Index) 
>when have 200,000 Row.
>
>have a look for "PgDBE" in PDR ... there are a lot
>have fun
>
>
>

hi,

>>you can work with it if you accept that "SEEK" use 1.6 Sec (with SQL
>>Index) when have 200,000 Row.
>
> Simple impossible, except you are talking about the early stages of
> the ISAM emulation where we didn't have created indexes automatically.

i got many People from that Session and a Video !
it was a fast PC and Werner have done a good job for Tom who was sick

if you come to German Forum Devcon 2020 you can have a look what User made
in last 7 Years where Alaska work on PgDBE.

we can make Performance Test User Solution against PgDBE.
i think that would be interessing for many User to come to German Forum 
Devcon 2020

Jimmy,

yes, Werner did a really good job in his session. But you withhold some 
parts:
- Werner started with older PGDBE versions, that have been really bad. 
And said than that Alaska made it much better with some more updates. He 
explicit pointed out some of the better items.
- Since than Alaska published a lot more updates, so it should be much 
faster and stable now.

Just because you fight for your own PostgreSQL class (and nobody else in 
the forum understands what you are doing) because you still have no 2.0 
subscription and still work with 1.9 without all the help the 2.0 
provides for SQL access this is not necessarily a good way.

And please - don't continue our discussion from the forum here in the 
NG. We both know that this is useless. I just wanted to point out that 
your statements on Werners session are not right (or complete).

Jan

Am 29.11.2019 um 08:24 schrieb Jim Lee:
> hi,
> 
>>> you can work with it if you accept that "SEEK" use 1.6 Sec (with SQL
>>> Index) when have 200,000 Row.
>>
>> Simple impossible, except you are talking about the early stages of
>> the ISAM emulation where we didn't have created indexes automatically.
> 
> i got many People from that Session and a Video !
> it was a fast PC and Werner have done a good job for Tom who was sick
> 
> if you come to German Forum Devcon 2020 you can have a look what User made
> in last 7 Years where Alaska work on PgDBE.
> 
> we can make Performance Test User Solution against PgDBE.
> i think that would be interessing for many User to come to German Forum
> Devcon 2020
> 
> 
> 
> 
> 
> 
> 
> 
> 
>

it is true that  Alaska work on PgDBE ... since 7 Years
it is NOT true that i want to provide my Class, i do provide native Style.

it is easy to BUY Software but as Developer i BUILD Software and i use more 
than only Xbase++
so let us make a Performance Test on Devocn 2020 and show is to User

it does not make a Difference to work with v1.9x and SQL as you do not need 
v2.x Stuff for SQL
so what can you tell use about YOUR work with PgDBE

uupps ... i forgot that you are not using SQL ...  so you are talking about 
it without knowing it.

Jimmy,

you are speaking nonsense.

1) 2.0 does support SQL much more than 1.9.

2) I know that also use competitive products to Xbase++. And try to 
write your code in a manner that it can also compiled with the other 
language(s). A good solution. But we normally do not - so in the NG we 
are mostly speaking about Xbase++. If you do other stuff please answer 
in this NG only that parts that depends on Xbase++

3) There might be a performance difference between the Xbase++ solution 
and a specialized solution you write. And you are right - the ISAM 
PostgreSQL solution in Xbase++ is much slower than the native access 
(but became much faster over the years). But nobody can change that, and 
the purpose is totally difference.

4) How do you know that I do not use SQL? Either you lie, or somebody 
else lied when he told you. I use SQL. When I develop in MS Access. And 
sometimes when accessing ADS in Xbase++. So be careful what you say. 
Surely I'm not a SQL professional. But I use it.

So please - can you stay with the topic and do not distract by insulting 
others?

Jan

Am 30.11.2019 um 02:16 schrieb Jim Lee:
> it is true that  Alaska work on PgDBE ... since 7 Years
> it is NOT true that i want to provide my Class, i do provide native Style.
> 
> it is easy to BUY Software but as Developer i BUILD Software and i use more
> than only Xbase++
> so let us make a Performance Test on Devocn 2020 and show is to User
> 
> it does not make a Difference to work with v1.9x and SQL as you do not need
> v2.x Stuff for SQL
> so what can you tell use about YOUR work with PgDBE
> 
> uupps ... i forgot that you are not using SQL ...  so you are talking about
> it without knowing it.
> 
> 
>

Scott,

>I assume that you need the PostgreSQL server within xbase to make this 
>all work. Is that not true?

No, the PostgreSQL Server (PGSQL) is completely separate from Xbase++, but 
you can access PGSQL via many different avenues using Xbase++, depending on 
what you like to accomplish and how you like to do that.

>But now I think im hearing that postgreSQL isn't really a finished product 
>yet where that is possible due to bugs when you use some SQL commands.

You are confusing two totally different things here. PostgreSQL (or PGSQL) 
is a fully fledged Open Source RDBMS, with some features that are missing 
from other SQL DBMs. It performs pretty much as well as MySQL, MS SQL/SQL 
Server, DB2, Oracle, etc. Actually, the reason Alaska choose PGSQL as their 
preferred SQL backend, is the fact that it supports some customization 
features that lent itself to implementing their ISAM emulation.

The PGDBE from Alaska on the other hand, is "a work in progress", and has 
still some issues, actually more than there are PDRs publicly available for 
it at this point. It is an attempt by Alaska to allow xBase-style ISAM code 
and coding patterns to run efficiently with a SQL DB Server backend, 
specifically PGSQL.

But as I wrote above, Alaska's Xbase++ -- not to mention some very good 
Add-On products for Xbase++ -- actually offers you many different ways to 
accomplish your task (leveraging your SQL knowledge).

First, it depends on your version of Xbase++. If you still hang on to 1.9 
(or even older versions), your only practical choice is SQLExpress or, if 
you use ADS, the ADSDBE. The ODBCDBE is deficient in so many ways, as to 
make it unsuitable for production work. This is still the case in Xbase++ 
2.0 in my opinion.

Because SQLExpress isn't a DBE, your code will look quite differently, as 
you will be using Classes/Objects, rather than work areas, etc. I would 
highly recommend SQLExpress for any SQL work in Xbase++. I've used it with 
Oracle, DB2, MS SQL, PGSQL, and some others, and it has always worked great.

But converting an existing ISAM DBE Xbase++ program to a SQL program using 
SQLExpress will require a lot of work and rewriting. If your business logic 
were pretty much separate from your data-access code, this might be slightly 
easier, but a total rewrite might be necessary in most cases. Based on your 
other posts, mentioning "Select 250 ; append blank"... etc., you seem to 
have a lot of old command-style Clipper-style code, which usually tightly 
integrates business logic and data access.

If you have the Advantage Database Server (ADS), you can use the ADSDBE 
and/or the ODBCDBE, as well as the ACE Wrappers (for native/direct DLL 
access), which come with SQLExpress -- as well as SQLExpress itself -- to 
work with it. With the ADSDBE you have full ISAM support and virtually no 
code changes are needed to switch from any DBF/FOX NTX/CDX DBE combinations. 
Some Xbase++ features available in those DBEs aren't supported by ADS, like 
OrdWildSeek(), etc., but there are workarounds for this. And of course, ADS 
supports many other things, including SQL.

If you install a (freely available) PostgreSQL Server, you can also use the 
ODBCDBE, SQLExpress, as well as direct/native (DLL call) access through the 
LibPQ.dll (as Jimmy mentioned). The ODBCDBE and SQLExpress work of course 
for any other SQL RDBMS with an ODBC-Driver, like MySQL, MS SQL, Oracle, 
DB2, etc.

If you have Xbase 2.0.554 (and preferable above), you have additional 
choices. Besides the PGDBE for ISAM emulation with a PostgreSQL database 
server backend, you have also (partially functional) Pass-Through SQL and 
Universal SQL (which uses an embedded SQLite server/DBE).

The PGDBE allows you to use any PGSQL Server (Version 8.2 and above) in an 
emulated ISAM mode, without having to change much of your code, as the 
DBE-style syntax is pretty much unchanged. Needless to say, there are still 
many issues and I don't know of anyone using the PGDBE in a production 
environment. I tried for quite a while and developed workarounds for a lot 
of issues, but I'm not up to date with the latest version, so I don't know 
if it is up for prime time yet, but I have my doubts.

Pass-Through SQL is very similar to what SQLExpress offers, but on a much 
less sophisticated level. It can basically be used to send (text) SQL 
commands to any SQL backend, similar to the ODBCDBE and retrieve data back. 

But at this point, only the ADSDBE and the PGDBE support Pass-Through SQL, 
while the ODBCDBE curiously doesn't. With the PGDBE, you can actually mix 
ISAM access and Pass-Through SQL on the same connection and on the same 
tables, as long as you don't mess with the tables, columns, triggers, 
sequences, and indexes, etc. that the PGDBE creates on the PGSQL Server to 
emulate ISAM mode. For example, changing any value via SQL that is used by 
an ISAM-mode Index expression, will corrupt that emulated ISAM-Index.

Universal SQL is the ability to use SQL commands directly in your Xbase++ 
code, theoretically allowing you to use it with memory variables like 
DataObjects, Arrays, and any of the ISAM and Sequential File-based DBEs. In 
reality, though, only a tiny subset of those things actually work. It can be 
used for some demo code, but in my opinion nothing really practical(, yet).

If you look in the Xbase++ documentation, you will find the same exhaustive 
information for both topics, Pass-Through SQL as well as Universal SQL, 
consisting of just three letters: "TBD". Mind you that both were introduced 
over five years ago (official release 2.0.554 in September 2014, with test 
versions starting two years earlier, in October 2012, with Build 2.0.437).

There is a little bit of information available on Pass-Through SQL under the 
"SqlStatement" Class topic. But it is neither exhaustive nor precise and 
half of it isn't even working(, yet). This Class is implemented differently 
for each supporting DBE, which at this point only includes the ADSDBE and 
PGDBE, though. The basic DacSQLStatement() and the derived PGSqlStatement() 
and AdsSQlStatement() classes aren't documented at all(, yet).

There is also very little information available on Univeral SQL under the 
"USqlStatement" Functions and Command topic, even though "USqlStatement" is 
also a Class Function. This Class is implemented only for the embedded 
SQLite DBE, which means that it isn't fluent in basic xBase, while the 
XPP.exe compiler isn't fluent in SQL, making it (virtually) useless -- again 
in my opinion.

Anyway, you have many options, and depending on your situation, I would 
recommend you go with either ADSDBE, PGDBE, or SQLExpress. But even though 
SQLExpress is more sophisticated, much more stable, and usually much faster 
than either of the DBEs, it will cost extra money and time to re-write your 
ISAM application. For a new project, SQLExpress is definitively your best 
choice, especially as you already have extensive SQL knowledge.

All of the above are either facts or -- where mentioned -- my personal 
opinions, based on my (25+ years) experience with Xbase++ and other database 
technologies. As always, your mileage may vary! If anyone has different 
opinions or better information, please post it here.

Anyway, I hope the above will help with the decision making process,

Andreas

Andreas Gehrs-Pahl
Absolute Software, LLC

phone: (989) 723-9927
email: Andreas@AbsoluteSoftwareLLC.com
web:   http://www.AbsoluteSoftwareLLC.com
[F]:   https://www.facebook.com/AbsoluteSoftwareLLC

Andreas,

Thank you for the very comprehensive reply. And you were absolutely 
right...I was unclear on the distinctions you detailed below between 
PostgreSQL, PGDBE, etc. Your time and effort spent in clarifying that for me 
is very much appreciated.

I am currently running version 2.00.972 of xbase, and you are correct that 
much of my code is in the clipper style, so ideally I want to use that code 
pretty much as-is for the time being if possible.

I will take your and Jimmy's advice and give it a try...I just wanted to be 
more well informed on my choices before deciding which way to go.

Scott




"Andreas Gehrs-Pahl" wrote in message 
news:zb56c3cr1apk$.14zw3oi3ec737.dlg@40tude.net...

Scott,

>I assume that you need the PostgreSQL server within xbase to make this
>all work. Is that not true?

No, the PostgreSQL Server (PGSQL) is completely separate from Xbase++, but
you can access PGSQL via many different avenues using Xbase++, depending on
what you like to accomplish and how you like to do that.

>But now I think im hearing that postgreSQL isn't really a finished product
>yet where that is possible due to bugs when you use some SQL commands.

You are confusing two totally different things here. PostgreSQL (or PGSQL)
is a fully fledged Open Source RDBMS, with some features that are missing
from other SQL DBMs. It performs pretty much as well as MySQL, MS SQL/SQL
Server, DB2, Oracle, etc. Actually, the reason Alaska choose PGSQL as their
preferred SQL backend, is the fact that it supports some customization
features that lent itself to implementing their ISAM emulation.

The PGDBE from Alaska on the other hand, is "a work in progress", and has
still some issues, actually more than there are PDRs publicly available for
it at this point. It is an attempt by Alaska to allow xBase-style ISAM code
and coding patterns to run efficiently with a SQL DB Server backend,
specifically PGSQL.

But as I wrote above, Alaska's Xbase++ -- not to mention some very good
Add-On products for Xbase++ -- actually offers you many different ways to
accomplish your task (leveraging your SQL knowledge).

First, it depends on your version of Xbase++. If you still hang on to 1.9
(or even older versions), your only practical choice is SQLExpress or, if
you use ADS, the ADSDBE. The ODBCDBE is deficient in so many ways, as to
make it unsuitable for production work. This is still the case in Xbase++
2.0 in my opinion.

Because SQLExpress isn't a DBE, your code will look quite differently, as
you will be using Classes/Objects, rather than work areas, etc. I would
highly recommend SQLExpress for any SQL work in Xbase++. I've used it with
Oracle, DB2, MS SQL, PGSQL, and some others, and it has always worked great.

But converting an existing ISAM DBE Xbase++ program to a SQL program using
SQLExpress will require a lot of work and rewriting. If your business logic
were pretty much separate from your data-access code, this might be slightly
easier, but a total rewrite might be necessary in most cases. Based on your
other posts, mentioning "Select 250 ; append blank"... etc., you seem to
have a lot of old command-style Clipper-style code, which usually tightly
integrates business logic and data access.

If you have the Advantage Database Server (ADS), you can use the ADSDBE
and/or the ODBCDBE, as well as the ACE Wrappers (for native/direct DLL
access), which come with SQLExpress -- as well as SQLExpress itself -- to
work with it. With the ADSDBE you have full ISAM support and virtually no
code changes are needed to switch from any DBF/FOX NTX/CDX DBE combinations.
Some Xbase++ features available in those DBEs aren't supported by ADS, like
OrdWildSeek(), etc., but there are workarounds for this. And of course, ADS
supports many other things, including SQL.

If you install a (freely available) PostgreSQL Server, you can also use the
ODBCDBE, SQLExpress, as well as direct/native (DLL call) access through the
LibPQ.dll (as Jimmy mentioned). The ODBCDBE and SQLExpress work of course
for any other SQL RDBMS with an ODBC-Driver, like MySQL, MS SQL, Oracle,
DB2, etc.

If you have Xbase 2.0.554 (and preferable above), you have additional
choices. Besides the PGDBE for ISAM emulation with a PostgreSQL database
server backend, you have also (partially functional) Pass-Through SQL and
Universal SQL (which uses an embedded SQLite server/DBE).

The PGDBE allows you to use any PGSQL Server (Version 8.2 and above) in an
emulated ISAM mode, without having to change much of your code, as the
DBE-style syntax is pretty much unchanged. Needless to say, there are still
many issues and I don't know of anyone using the PGDBE in a production
environment. I tried for quite a while and developed workarounds for a lot
of issues, but I'm not up to date with the latest version, so I don't know
if it is up for prime time yet, but I have my doubts.

Pass-Through SQL is very similar to what SQLExpress offers, but on a much
less sophisticated level. It can basically be used to send (text) SQL
commands to any SQL backend, similar to the ODBCDBE and retrieve data back.

But at this point, only the ADSDBE and the PGDBE support Pass-Through SQL,
while the ODBCDBE curiously doesn't. With the PGDBE, you can actually mix
ISAM access and Pass-Through SQL on the same connection and on the same
tables, as long as you don't mess with the tables, columns, triggers,
sequences, and indexes, etc. that the PGDBE creates on the PGSQL Server to
emulate ISAM mode. For example, changing any value via SQL that is used by
an ISAM-mode Index expression, will corrupt that emulated ISAM-Index.

Universal SQL is the ability to use SQL commands directly in your Xbase++
code, theoretically allowing you to use it with memory variables like
DataObjects, Arrays, and any of the ISAM and Sequential File-based DBEs. In
reality, though, only a tiny subset of those things actually work. It can be
used for some demo code, but in my opinion nothing really practical(, yet).

If you look in the Xbase++ documentation, you will find the same exhaustive
information for both topics, Pass-Through SQL as well as Universal SQL,
consisting of just three letters: "TBD". Mind you that both were introduced
over five years ago (official release 2.0.554 in September 2014, with test
versions starting two years earlier, in October 2012, with Build 2.0.437).

There is a little bit of information available on Pass-Through SQL under the
"SqlStatement" Class topic. But it is neither exhaustive nor precise and
half of it isn't even working(, yet). This Class is implemented differently
for each supporting DBE, which at this point only includes the ADSDBE and
PGDBE, though. The basic DacSQLStatement() and the derived PGSqlStatement()
and AdsSQlStatement() classes aren't documented at all(, yet).

There is also very little information available on Univeral SQL under the
"USqlStatement" Functions and Command topic, even though "USqlStatement" is
also a Class Function. This Class is implemented only for the embedded
SQLite DBE, which means that it isn't fluent in basic xBase, while the
XPP.exe compiler isn't fluent in SQL, making it (virtually) useless -- again
in my opinion.

Anyway, you have many options, and depending on your situation, I would
recommend you go with either ADSDBE, PGDBE, or SQLExpress. But even though
SQLExpress is more sophisticated, much more stable, and usually much faster
than either of the DBEs, it will cost extra money and time to re-write your
ISAM application. For a new project, SQLExpress is definitively your best
choice, especially as you already have extensive SQL knowledge.

All of the above are either facts or -- where mentioned -- my personal
opinions, based on my (25+ years) experience with Xbase++ and other database
technologies. As always, your mileage may vary! If anyone has different
opinions or better information, please post it here.

Anyway, I hope the above will help with the decision making process,

Andreas

Andreas Gehrs-Pahl
Absolute Software, LLC

phone: (989) 723-9927
email: Andreas@AbsoluteSoftwareLLC.com
web:   http://www.AbsoluteSoftwareLLC.com
[F]:   https://www.facebook.com/AbsoluteSoftwareLLC

Hi Andreas,

>If you have Xbase 2.0.554 (and preferable above), you have additional 
>choices. Besides the PGDBE for ISAM emulation with a PostgreSQL database 
>server backend, you have also (partially functional) Pass-Through SQL and 
>Universal SQL (which uses an embedded SQLite server/DBE).
>

>The PGDBE allows you to use any PGSQL Server (Version 8.2 and above) in an 
>emulated ISAM mode, without having to change much of your code, as the 
>DBE-style syntax is pretty much unchanged. Needless to say, there are still 
>many issues and I don't know of anyone using the PGDBE in a production 
>environment. I tried for quite a while and developed workarounds for a lot 
>of issues, but I'm not up to date with the latest version, so I don't know 
>if it is up for prime time yet, but I have my doubts.
>
>Pass-Through SQL is very similar to what SQLExpress offers, but on a much 
>less sophisticated level. It can basically be used to send (text) SQL 
>commands to any SQL backend, similar to the ODBCDBE and retrieve data back. 

I have to disgree here. Pass-Through SQL via DacSqlStatement uses
parameterized statements and therefore allows you to manage binary
data such as image in a transparent way. If it is a SQL query then the
data is reflected in a workarea.

In fact, the PostgreSQL Pass-Through-SQL is easy to use and can not
compared to the ODBCDBE which is designed to access ODBC data in a
simple way but thats it.

Of course, if somebody wants to do ODBC with all the required
knowledge as well as specific coding but all freedom, then SqlExpresss
is definitive the best option.

>If you look in the Xbase++ documentation, you will find the same exhaustive 
>information for both topics, Pass-Through SQL as well as Universal SQL, 
>consisting of just three letters: "TBD". Mind you that both were introduced 
>over five years ago (official release 2.0.554 in September 2014, with test 
>versions starting two years earlier, in October 2012, with Build 2.0.437)
>
>There is a little bit of information available on Pass-Through SQL under the 
>"SqlStatement" Class topic. But it is neither exhaustive nor precise and 
>half of it isn't even working(, yet). This Class is implemented differently 
>for each supporting DBE, which at this point only includes the ADSDBE and 
>PGDBE, though. The basic DacSQLStatement() and the derived PGSqlStatement() 
>and AdsSQlStatement() classes aren't documented at all(, yet).
>
>There is also very little information available on Univeral SQL under the 
>"USqlStatement" Functions and Command topic, even though "USqlStatement" is 
>also a Class Function. This Class is implemented only for the embedded 
>SQLite DBE, which means that it isn't fluent in basic xBase, while the 
>XPP.exe compiler isn't fluent in SQL, making it (virtually) useless -- again 
>in my opinion.

Maybe you are right with the documentation amount but as a matter of
fact what should Alaska Software document beyond how to add parameters
to a SQL statement and how to perform the query or execute the
statement. And of course how to escape a type cast. That all is
documented as well as the methods and parameters.

The fact that PgSqlStatement()/AdsSqlStatement() .. are not
documentated is simple because they are the conrete providers and
implement internal interfaces used by USQL as well as the interface
documentet and used at SqlStatement(). IAW they are not documented by
design because they do not expose any interface of interest.


>All of the above are either facts or -- where mentioned -- my personal 
>opinions, based on my (25+ years) experience with Xbase++ and other database 
>technologies. As always, your mileage may vary! .
>
May I ask on which Xbase++ version your "facts" are based?


>  If anyone has different 
>opinions or better information, please post it here.
>
Did so 

regards
Steffen F. Pirsig
Alaska Software

Scott,

just to clarify. The PostgreSQL DatabaseEngine provides two different
data access strategies which can be used interoperable.

1. ISAM Emulation: this means you need to use the DbfUpsizer to
migrate your existing dbf/ntx/cdx data model and data to PostgreSQL
Server. Then you can use the ISAM emulation, ideally you do not need
to change anything of your existing ISAM code.

The upsize process installs triggers and creates stored procedures
which are required to perform ISAM operations and used by the triggers
to update meta data after data has been changed via pure SQL form
another tool or language

2. SQL parameterized statements via DacSqlStatement(), basically your
are doing the following

 oStmt := DacSqlStatement( oSession ):fromChar( "SELECT * FROM
customer WHERE lastname = ::lastname AND countrycode=::ccode")

 the ::parameter name in SQL stmt is reflected as a member of the
stmt instance
oStmt:lastname := "name to search"
oStmt:ccode      := "US"

oStmt:build():query()
 IF(!Empty(oSession:getLastError()))
     error
  ENDIF

 resultset is now in the current read only workarea. you can use
skip, eof/field-> to access the data

 get rid of it
DbCloseArea()

FWIW: ISAM Emulating tables have a column __record which is record# in
ISAM terms. This way you can correlate a SQL query result with a ISAM
record for example.

The SQL performance is as good as your SQL statements  and the ISAM
performance is as of today better than any dbf/ntx/cdx use case in a
multi user scenario.

regards
Steffen F. Pirsig
Alaska Software

On Wed, 20 Nov 2019 08:07:06 -0500, "Scott Krise"
<scottkrise@verizon.net> wrote:

>>we just talk about PostgreSQL but what is which MySQL (mariaDB) or other 
>>SQL Server ?
>>did you think you will learn it when use PgDBE ?
>
>Not sure I understand what you are saying here. Are you saying you don't 
>have to use PostgreSQL as your SQL server? You can use MySQL instead? I am 
>very familiar with MySQL...and the syntax etc. I assume that you need the 
>PostgreSQL server within xbase to make this all work. Is that not true?
>
>
>>IHMO you have to learn SQL to use it. else you never get the Performance.
>>i have test other xBase Dialect using RDD but they all work only good when 
>>< 1,000,000 Row
>
>Im already very strong in sql...just have A LOT of software written for 
>DBF/NTX tables and it’s a huge undertaking to rewrite everything. PostgreSQL 
>seems to offer me a solution where I can use what I have invested so much 
>time in, and as time allows, rewrite programs that could most benefit from 
>the SQL performance advantages. But now I think im hearing that postgreSQL 
>isn't really a finished product yet where that is possible due to bugs when 
>you use some SQL commands.

Scott,

keep in mind that I am from Alaska Software so my answer can be biased


RE: performance
Our current measurement shows that the PostgreSQL ISAM emulation out
performs any network based data access using dbf/ntx or dbf/cdx when
using more than one workstation over the network. The reason for that
is clearly related to the client/server approach and the fact that
there is no concurrency between your clients regarding file resources.

The performance of the PostgreSQL ISAM emulation is generally now
compareable with the ADS Database Server. It is a bit slower if it
comes to relative navigational behavior with skip and filter. We are
not sure if we can make it better than the ADS in that area. However
if it comes to query processing - using SQL - the ADS server is much
slower and provides lesser options to optimize performance.

So much about performance in detail. In general i would say that the
PostgreSQL ISAM emulation is good enough to substitue the dbf/ntx/cdx
file storage engines as well as the ads server. Why, because it finaly
is more reliable, better scaleable and provides you as a developer
more tools to attack performance issues if it comes to data-analysis.

RE: Open PDRs and ISAM emulation
We already announced to other customers which have migrated their
existing dbf/cdx/ntx solution to the PostgreSQL ISAM emuulation that
in early 2020 we will do the final fixing run. The truth is that it
took a part of the dev team almost the entire year 2019 to adjust
existing algorithms or redesign exsiting approaches to reach the
current performance level of the ISAM emulation. 

Btw, we have done our performance work based on real world Xbase++
applications installed by some of our customers at EC2 instances - so
we had real world scenarios to move that uniqe technology forward.

Bottom line here is that the PostgreSQL ISAM emulation is not
production ready as of now in terms of rolling out to your customers
but will be with one the next monthly updates. However, it is by far
good enough to start working on that subject. 

Keep in mind, you need to develop new backup strategies, your support
people may need to aquire new knowledge. So it is not just code which
needs to be changed to do that transition from dbf/cdx/ntx or ads to
the PostgreSQL server.

regards
Steffen F. Pirsig
Alaska Software 


On Mon, 18 Nov 2019 10:36:52 -0500, "Scott Krise"
<scottkrise@verizon.net> wrote:

>Thanks Peter. How was the performance after changing? I am using dbf/ntx 
>tables now.
>
>And are you using PGDBE or ODBC and sqlexpress?
>
>Thanks!
>
>"Peter Alderliesten" wrote in message 
>news:hws7k1b5g897$.ikn7u5ajh5gz$.dlg@40tude.net...
>
>Scott,
>
>> I'm considering the use of postgre, but I first wanted to hear from
>> people who have successfully converted or who have tried and abandoned it.
>>
>> It seems like there should be a lot more messages here if a lot of us
>> are using it successfully.
>
>As far as I can see it the ISAM way of using PostgreSQL is a viable option.
>You might still experience quite a bit of trouble using the SQL statements
>that became part of the Xbase++ language. I still have a few PDR's open on
>basic functionality.
>I think that is indeed the reason for lack of comment and questions here.
>
>Peter

Hi Scott,

Just for clarification. As a matter of fact, the amount of
newsgroup/nntp or usenet users is declining since a few decades on
planet earth.  With the advent of powerful search engines and blogs as
well as social media - people can find answer to questions generally
much faster - in fact almost instantly. Compared to waiting that
others respond to a question.

Said that, I still believe that there is a place for some product/
topic specific community driven Q&A platform but it seems that nntp
groups or forums are continously declining in terms of professional
use.

If we at Alaska Software do the measure then we see that around 5% of
our active customers are showing up at the news.alaska-software.com as
well as the other two donnay-software.com and xbaseforum.de platforms 
where Xbase++ developers meet. 

The total amount of registered users at these community platforms
compares to 15% of the current active subscription customers.

I know this was not your primary question, but I wanted to address
your assumption about the amount of messages related to the subject.

regards
Steffen F. Pirsig
Alaska Software

>Hello everyone,
>
>I'm considering the use of postgre, but I first wanted to hear from 
>people who have successfully converted or who have tried and abandoned it.
>
>It seems like there should be a lot more messages here if a lot of us 
>are using it successfully.
>
>Thanks,
>
>Scott

Scott,

let me go back to your original question. Xbase++ provides various
ways to access/manage data natively or via add-ons such as SqlExpress.

IMO you need to ask yourself what you want. And then you need to
decide. Let me outline the general options. But first, let us define
scenarios. Of course, the scenarios below assume that you are
developing a mission-critical solution, if we are talking about
real-time data processing from sensors or production control, then the
situation is different.

Scenario 1.)  You want to protect existing code by 100% for an
existing dbf/ntx/cdx based application that has its historical debt in
terms of mixed data access, business logic, and user interface code.

Scenario 1.1) You want to ensure that this application can deal with
extensive data, provides better data security and regulations
conformance, becomes more reliable. But you do not want to
re-implement existing code nor do you intend to add massive new logic
or data analysis to the application

Scenario 1.2) Same as 1.1 except that you intend to add new features
as well as intend to extend its data analysis capabilities.

Scenario 2.) You want to protect as much as possible of the existing
code and business logic but want to redesign some modules as well
as extend the data analysis capabilities or your solution.

Scenario 3.) You want to create a new application from the Scratch

Scenario 4.) You need to access additional external data for an
existing application. Data manage by different DBMS systems such as
Oracle, MySQL, PostgreSQL...


Based on the previous outlined scenarios here are the proposed
technologies:

RE: 1.1) Use ADS DatabaseServer or PostgreSQL as a DBMS, and use the
ADSDBE or PGDBE since they are the only way to protect existing code.
Stay in both cases(ADS/PG)  with the ISAM feature set - that's it.

ISAM feature set means command and functions such as
USE/SKIP/SEEK/FILTER/RLOCK/APPEND...

RE: 1.2) Use  PostgreSQL as a DBMS, and use the  PGDBE. That is the
only way to protect the existing code. In the case of new features,
you are free to use ISAM style or SQL style coding. Typically you stay
in terms of transactional code with ISAM but start using SQL coding
(DacSqlStatement()) via parameterized SQL statements for the Query
processing and data analysis part. Query results get reflected in a
read-only work area, so you use your existing code and framework to
access that data and process it.

RE:2) Basically, like 1.2 except that you should concentrate on using
parameterized SQL statements as well as to start re-thinking your data
model. That is very often the most substantial work since there is a
lot of existing code tweaking datatypes and doing tricks. But with a
SQL DBMS, you have more specialized data types, which in turn can
increase the overall performance if used. But still, if required, use
ISAM coding.

RE:3) Use PGDBE with parameterized statements, use ISAM to navigate in
resultsets, use DataObjects as data-containers between your
application layers. Use SCATTER / GATHER to easy data exchange between
your containers and work areas.Bottom line here is, write all your
data access code using parameterized SQL commands.

As an alternative, you can use an ADS Server and the ADSDBE.

If you want to write a pure ODBC based application where you can
control anything and can access any ODBC data source - then SQL
Express is the best way to go. Even so, this may require a lot of DBMS
specific knowledge and means that the entire concept of work
areas/fields is no more available to you.

Another approach is to use ADO ActiveRecords via the Xbase++ ActiveX
layer, it is not the fastest approach, but it works.

RE:4) Use ODBC DBE or Sql Express or ADO.

Some final note regarding the ADS Server. It is our understanding that
the current product owner of the ADS server may announce product EOL -
end of life - in a short to mid term timeframe. 

Bottom line is, only the ADSDBE and the PGDBE provide you backward
compatibility in means of your existing code. All other approaches
require re-implementation and re-testing of your existing data-access
and business logic.

regards
Steffen F. Pirsig
Alaska Software

On Wed, 13 Nov 2019 15:05:43 -0500, Scott Krise wrote:

>Hello everyone,
>
>I'm considering the use of postgre, but I first wanted to hear from 
>people who have successfully converted or who have tried and abandoned it.
>
>It seems like there should be a lot more messages here if a lot of us 
>are using it successfully.
>
>Thanks,
>
>Scott

Hello Steffen,

I will respond just once for the many, very insightful comments in this 
thread. Per your question below, I will primarily fall in scenarios 1.1 or 
1.2 at this point, but may need to start the arduous task of going with 
Scenario 3. In either case, Ill surely consider your recommendations on how 
to proceed.

I had not heard that ADS will be reaching EOL in the near future. That is 
somewhat upsetting, but I appreciate the information.

Thank you very much.

Scott

"Steffen F. Pirsig" wrote in message 
news:ngittehknm0kilfgrcm2jbeei8mr0m4fp5@4ax.com...

Scott,

let me go back to your original question. Xbase++ provides various
ways to access/manage data natively or via add-ons such as SqlExpress.

IMO you need to ask yourself what you want. And then you need to
decide. Let me outline the general options. But first, let us define
scenarios. Of course, the scenarios below assume that you are
developing a mission-critical solution, if we are talking about
real-time data processing from sensors or production control, then the
situation is different.

Scenario 1.)  You want to protect existing code by 100% for an
existing dbf/ntx/cdx based application that has its historical debt in
terms of mixed data access, business logic, and user interface code.

Scenario 1.1) You want to ensure that this application can deal with
extensive data, provides better data security and regulations
conformance, becomes more reliable. But you do not want to
re-implement existing code nor do you intend to add massive new logic
or data analysis to the application

Scenario 1.2) Same as 1.1 except that you intend to add new features
as well as intend to extend its data analysis capabilities.

Scenario 2.) You want to protect as much as possible of the existing
code and business logic but want to redesign some modules as well
as extend the data analysis capabilities or your solution.

Scenario 3.) You want to create a new application from the Scratch

Scenario 4.) You need to access additional external data for an
existing application. Data manage by different DBMS systems such as
Oracle, MySQL, PostgreSQL...


Based on the previous outlined scenarios here are the proposed
technologies:

RE: 1.1) Use ADS DatabaseServer or PostgreSQL as a DBMS, and use the
ADSDBE or PGDBE since they are the only way to protect existing code.
Stay in both cases(ADS/PG)  with the ISAM feature set - that's it.

ISAM feature set means command and functions such as
USE/SKIP/SEEK/FILTER/RLOCK/APPEND...

RE: 1.2) Use  PostgreSQL as a DBMS, and use the  PGDBE. That is the
only way to protect the existing code. In the case of new features,
you are free to use ISAM style or SQL style coding. Typically you stay
in terms of transactional code with ISAM but start using SQL coding
(DacSqlStatement()) via parameterized SQL statements for the Query
processing and data analysis part. Query results get reflected in a
read-only work area, so you use your existing code and framework to
access that data and process it.

RE:2) Basically, like 1.2 except that you should concentrate on using
parameterized SQL statements as well as to start re-thinking your data
model. That is very often the most substantial work since there is a
lot of existing code tweaking datatypes and doing tricks. But with a
SQL DBMS, you have more specialized data types, which in turn can
increase the overall performance if used. But still, if required, use
ISAM coding.

RE:3) Use PGDBE with parameterized statements, use ISAM to navigate in
resultsets, use DataObjects as data-containers between your
application layers. Use SCATTER / GATHER to easy data exchange between
your containers and work areas.Bottom line here is, write all your
data access code using parameterized SQL commands.

As an alternative, you can use an ADS Server and the ADSDBE.

If you want to write a pure ODBC based application where you can
control anything and can access any ODBC data source - then SQL
Express is the best way to go. Even so, this may require a lot of DBMS
specific knowledge and means that the entire concept of work
areas/fields is no more available to you.

Another approach is to use ADO ActiveRecords via the Xbase++ ActiveX
layer, it is not the fastest approach, but it works.

RE:4) Use ODBC DBE or Sql Express or ADO.

Some final note regarding the ADS Server. It is our understanding that
the current product owner of the ADS server may announce product EOL -
end of life - in a short to mid term timeframe.

Bottom line is, only the ADSDBE and the PGDBE provide you backward
compatibility in means of your existing code. All other approaches
require re-implementation and re-testing of your existing data-access
and business logic.

regards
Steffen F. Pirsig
Alaska Software

On Wed, 13 Nov 2019 15:05:43 -0500, Scott Krise wrote:

>Hello everyone,
>
>I'm considering the use of postgre, but I first wanted to hear from
>people who have successfully converted or who have tried and abandoned it.
>
>It seems like there should be a lot more messages here if a lot of us
>are using it successfully.
>
>Thanks,
>
>Scott

On 11/27/2019 1:22 PM, Steffen F. Pirsig wrote:


> Some final note regarding the ADS Server. It is our understanding that
> the current product owner of the ADS server may announce product EOL -
> end of life - in a short to mid term timeframe.
> 
> Bottom line is, only the ADSDBE and the PGDBE provide you backward
> compatibility in means of your existing code. All other approaches
> require re-implementation and re-testing of your existing data-access
> and business logic.
> 
> regards
> Steffen F. Pirsig
> Alaska Software
> 

I was poking around this morning and came across a "statement" from 
Michael Loop (https://people.sap.com/michael.loop#about) of SAP...

https://answers.sap.com/questions/12676073/ads-future.html which states...

SAP Advantage Database Server (ADS) is an active product with the 
current in-market version being v12. Our current published End of 
Mainstream Maintenance date is 12/31/2020. The End of Mainstream 
Maintenance date should not be viewed as being an End of Life Date. With 
an EOL, the product is no longer sold or supported. This contrasts with 
End of Mainstream Maintenance which is commonly extended when there is 
no successor version and where the product is still available with 
technical support although new fixes will not be issued for that version 
but would be issued for the successor version.

While SAP policy prevents me commenting on future major releases and 
dates, I can say that we remain committed to supporting the in-market 
versions of ADS (currently v12) through the delivery of PLs/SPs. Any 
enhancements would focus on additional client support and certification 
on new operating systems.

Cheers... and Merry Christmas with wishes to all for a Great 2020!!!

jonathan.leeming@familycentre.org
Edmonton, Alberta, Canada