• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • How to connect PowerBuilder 2019 to a PostgreSQL 10 Database

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved How to connect PowerBuilder 2019 to a PostgreSQL 10 Database

  1. How-to
0
Votes
Undo
  1. James Jarratt
    James Jarratt
  2. PowerBuilder
  3. Thursday, 9 July 2020 06:21 AM UTC

Hello

I have carefully followed Bruce Armstrong' tutorial for connecting PowerBuilder 2017 R2 to PostgreSQL, but I have a trial version of Powerbuilder 2019 R2 which I am trying to connect to a PostgreSQL 10 database. So far, I have not been successful.

Is there similarly definitive information for making the connection in Powerbuilder 2019? and if so where?

I have a successful ODBC DSN connection and an ODBC datasource in PB where I can see the tables, but I can't access the data in the tables.

Any advice would be appreciated.

Thanks in advance. :)

James Jarratt

 

 

  1. PostgreSQL
  2. PowerBuilder (Appeon)
PB object taking long time while regenerating/ com...
SaveAs PDF Failing
Accepted Answer
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 9 July 2020 17:37 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi James;

   If you can connect and see the tables, then the ODBC connection sounds OK. If you cannot access the datum, then it could be because your DB User ID does not have "Select" privileges. I would suggest checking this in your  DB Schema.

Regards ... Chris

Comment
There are no comments made yet.
Responses (3)
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Thursday, 9 July 2020 07:53 AM UTC
  2. PowerBuilder
  3. # 1

We have been connecting to Postgresql since PB 2017 (R1) through PB 2017 (R3), successfully, using the following DSN less connection, without any issues.

@ RunTime

SQLCA.DBMS=ODBC
SQLCA.DBParm="ConnectString='Driver=PostgreSQL ODBC Driver(UNICODE);Database=<Database>;Server=<ServerName>;Port=5432;UID=<UserID>;PWD=<Password>;',DisableBind=1"
SQLCA.Lock="RC"
SQLCA.AutoCommit = False

* Substitute <Database>, <ServerName>, <UserID>, <Password> with correct values.

*****************

Inside the IDE < DB Profile - ODB ODBC >  Need a DSN to connect

// Profile BaseTwoWheeler
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='DSN=<DSN>;UID=<userid>;PWD=<******>'"

********************

If you face any other difficulties, do explain with more details.

HTH

 

 

Comment
Load more comments
Olan Knight
  1. Olan Knight
  2. Monday, 24 August 2020 13:59 PM UTC
>>> * Substitute <Database>, <ServerName>, <UserID>, <Password> with correct values.

What value goes into <Servername>?

A) cabdev022thisisthe.server.prod ~or~

B) 10.10.10.256



Thank you!
  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Monday, 14 September 2020 14:00 PM UTC
Olan,

I use IP address to connect PostgreSQL db.

Sample from my ini file



[Profile Test_db1]

DBServer=Postgres

DBMS=ODBC

ServerName=192.168.1.25

LogId=<userid>

LogPassword=<pass>

Database=test_db1

DBParm="ConnectString='Driver=PostgreSQL ODBC Driver(UNICODE);Database=test_db1;Server=192.168.1.25;Port=5432;UID=<userid>;PWD=<pass>;',DisableBind=1"

Driver=PostgreSQL ODBC Driver(UNICODE)

Port=5432

Lock="RC"



// Note : DBServer is to connect different database < Postgres / SQL Server >

  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Monday, 14 September 2020 14:05 PM UTC
1) Do you get the "Select Data Source" popup window when you connect in the IDE?



I don't remember anything like that pop out..
  1. Helpful
There are no comments made yet.
James Jarratt
James Jarratt Accepted Answer Pending Moderation
  1. Friday, 28 August 2020 04:35 AM UTC
  2. PowerBuilder
  3. # 2

Hi All

And thank you.

I have resolved the problem by

  1. eliminating upper and lower case letters in table and column names, 
  2. reducing VARCHAR  lengths to 255
  3. ensuring that user has all priviledges in the db.

(1 & 2) give access to the data

(3) I think, is letting me edit it.

(1 & 2) may be a bug, and I think it is because PostgreSQL is case-sensitive about lables and the PowerBuilder system files convert labels to lower case.

NB: PostgreSQL comes with a number of free tools that make modification pretty easy, but you have to create a new connection in PowerBuilder for them to be recognized. 

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 1 September 2020 18:41 PM UTC
Hi James ... that is awesome news ... thanks for the feedback!
  1. Helpful 1
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Monday, 14 September 2020 14:09 PM UTC
From the beginning, I started naming the table field names in lower case letters.

I do have few (varchar) columns with length more than 255, all working without issues so far.

  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.