Converting from MSSQL Server 2017 to Postgresql 10.1

[a_sivababu]

Hi All,
I was initially using Sqlbase and then converted to Oracle Express Edition and then converted to SQL Server Express Edition. Now, I feel Express Edition is not right fit for me since it supports only 1 GB RAM, 1 Core Processor and 10 GB DB Storage only. So, I thought to evaluate Postgresql and MySql and I decided to go with Postgresql since I have more stored procedures and triggers.

my question is that, Will there be anyone who uses postgresql with Team Developer 7.x ( there is a postgres fix in TD 7.0.3)? if so, just wanted to know at what extent TD supports. I just started Data migration now through TD and so far so good.

If I am getting posgresql error in TD application then error dialog window appears with the error message "Not found in Error.sql". So, I capture the proper error message through SqlGetError() to Turns off backend error mapping and reports real backend errors. OR, I enable ODBCTrace in Sql.ini to find out actual error message. but just wanted to know whether TD has any settings to show the postgresql error message in default error dialog window.

Thanks
Siva A

[mogus]

Hi, Siva,
I have been using Postgresql with TD 6 and TD 7 (64bit) for quite some time now without any problems.
I got used to the behavior in case of incorrect SQL statements in TD ("Not found..."). I help myself by transferring the incorrect statement to an SQL tool (e. g. pgAdmin) and testing it there. That works fine for me.

Markus

[a_sivababu]

That's good Markus.
It is bit of difficult in initial stage but I am sure this OSDBMS will start giving more features and benefits in future.


Thanks!
Siva A

[a_sivababu]

Hi Markus,
I have raised ticket with Gupta. Have you faced this challenge before?


If I use constant text, concatenation operator, apply any postgresql functions then it returns as text data type (This is postgres design I hope). So, this is not displaying with normal String data type/variable in TD. It requires Long String since the function return type is text data type.

For eg,
1. Create a table with one columns as varchar(15)
create table test123
(col1 varchar(15))

2. Insert a string value to col1.

insert into test123 values ('i am upper case')

3. Run the TD application AS-IS

col1 - shows the value in the TD as the col1 is VARCHAR data type
col2 - does not show the value in the TD as posgresql upper function is returning text data type
col3 - shows the value in the TD due to work around
col4 - does not show as this is constant text as it returns data type is text

However, For col3, cost of converting for all SQLs is huge and work around design is not a good approach.


4: Change col2 & col4 in Grid to Long String and strCol1 and strcol4 variable to Long String.
col1 - shows the value in the TD
col2 - shows the value in the TD
col3 - shows the value in the TD
col4 - shows the value in the TD

However this work around is also requires more cost and design is not right.

my clarification is that, the application should work with string if the size is less than or equal to 254 and long string for more than 254.

I hope this is major issues and hope if TD supports for posgresql ( I hope it is yes since there is a bug fix even in TD 7.0.3) then this issue would have been raised before.

Can you please let me know how to proceed on this before I explore MySQL/MariaDB as an alternative option.

I see the connection parameters in gupta.ini.

ODBCConnectString=DSN=PGDB;DATABASE=PGDB;SERVER=192.111.1.1;PORT=5432;UID=postgres;CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c305008b;A1=7.4

and tried to check with TextAsLongVarchar=No in the above DSN though this is not rigght way but no effect.

Sample file is attached.


Thanks
Siva A

[mogus]

Hi Siva,
I tried out your sample and it works fine with TD 7.0.3 (64bit), without any changes, all colums are type of String!
Tis is amazing! I also struggled with the same problems before and solved it the the way you discribed it as workaround. For my opinion, it was not a TD bug, because PG returned "unknown" as data type for string constants (or even NULL as constant) and "text" for aggregate functions. So now I´m glad to see that it works in a much more "natural" way!

I will now carry on testing my programs, for to see, if it is now possible to get rid of all of them cast functions.

Markus

[a_sivababu]

Hi Markus,
Thanks. That's right. I have tested in TD 6.3.2 and got this issue. I got response from Gupta (JM) too ,saying that, it works in TD 7.0.3. so, I tested in TD 7.0.3 using win32 ODBC/.NET ODBC (ANSI & Unicode) and both are working fine.

I am bit relaxed and conversion is in progress now

Thanks
Siva A

[ptomasini]

Hi, I'm very interested in TD/PGSQL compatibility too...
These are my questions:
- which version of ODBC driver do you use? Those from [https://www.postgresql.org/ftp/odbc]?
- Have you tested with 32-bit TD?
- Any issue with dates and blob fields?
- unicode support is ok?
- performance issues?
- SQL error trapping and reporting?

I'm actually using oracle as my preferred DBMS (with TD 6.2), but i'm evaluating to adopt PGSQL (+TD 7.0.3) for new projects...
Thanks
Paolo

[a_sivababu]

Hi Paolo,
Migration from SQL Server is still in progress. I had more procedures and triggers so it takes time to complete and then I need to change TD SQL statements in few places. Planning to complete this in next 3 months.

Will list out here if there is any issues.

Thanks
Siva A

[Harald]

Hi Siva,

if you are looking for a free database edition, you could also consider DB2 Express-C. It has a limitation of 2 Cores, 16 GB RAM and 15 TB Data.

kind regars
Harald

[a_sivababu]

Hi Harald,
Ok. That's nice. I have used Oracle Express and SQL Server express edition earlier but thinking to move to database where there is no limitation . And PostgreSQL ORDBMS is having lot of features and it is placed in better ranking - https://db-engines.com/en/ranking

Thanks
Siva A

[a_sivababu]

Hi Paolo,
I have successfully migrated tables, views, triggers, stored procedures to Postgresql without any major issues.

if someone is using express edition from Oracle or SQL Server or IBM then Postgresql is better option.

Migrating from Oracle to Postgres is much easier than MS SQL Server as Postgres and Oracle are having similar syntax. I used online free tool for converting tables, triggers and T-SQL procedures.


Please find my answer below for your queries

- which version of ODBC driver do you use? Those from [https://www.postgresql.org/ftp/odbc]?
Siva > yes. you can use the latest one from above. if there is an issues then you can take the previous one. as of now, Postgresql 11 beta 2 is released, you can start using this as it has some more features and performance improvement.
- Have you tested with 32-bit TD?
Siva >> TD 7.0 / 7.1 tested in win32, win64 and works fine. TD .NET WPF/Web services is currently in progress and we did not find any issues so far.
- Any issue with dates and blob fields?
Siva> there is no issues with dates or blob field. however you can consider timestamp without timezone for date and Bytea for blob.
- unicode support is ok?
Siva> I have tested with unicode database though I am not currently using multilingual database. I support Latin1 characters and created database with Latin1.
- performance issues?
Siva > Performance is much better than SQL Server as I was using MSS SQL Server Express edition which is having more limitation.
- SQL error trapping and reporting?
Siva > TD is not capturing the correct error with existing SAL function so I used different SAL function to capture the native error and it works.

but one important thing is that, if you are supporting for multiple RDBMS then consider the below point

a) create your own function for natively supported function if you are not identifying ANSI standard functions. I replaced ISNULL, NVL to COALESCE which is ANSI standard. if it is not supported like SUBSTRING then you can create your own function and use it.
b) if you there is a complex native SQL statement and you want to go with this then try to create as a view and call it in TD instead of using if..else...checking for RDBMS in TD
c) you can also store the SQL statement in your own table( SQLID, SQLText,DBName,RDBMS_Vendor,RDBMS_Version etc) and use it from this.

There are some minor issues but added some work around. Will raise the ticket for the same.

1. viewing uploaded document is sometimes creating problem if I use ANSI ODBC but works fine by recreating with Unicode ODBC.
2. SalListpopulate() is not working so I just added to the list/combo with while sqlfetchnext.

Happy to help you if you are planning to give a try and post your issues here.

Thanks
Siva A