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

Resolved PostGreSQL Pipe Problem

  1. Issue
0
Votes
Undo
  1. John Murphy
    John Murphy
  2. PowerBuilder
  3. Friday, 15 May 2020 18:53 PM UTC

I'm am testing PostGreSQL and I piped a bunch of tables with no problems but when I tried to update the data I got an error on any table that already existed. If I drop the table first it works fine.

Pipe Error -17 SQLCode = 1 SQLSTATE = 42P01

ERROR: relation "dbo.pbcattbl" does not exist;

Error while preparing parameters

  1. PostgreSQL
  2. PowerBuilder
PowerBuilder.RegisteredObject.TriggerEvent always ...
How to avoid Saveas dialog in print
Responses (6)
Victor Reinhart
Victor Reinhart Accepted Answer Pending Moderation
  1. Friday, 1 December 2023 00:00 AM UTC
  2. PowerBuilder
  3. # 1

I have this problem too.  I have not found a solution.  It happens on a table with just 2 columns, one is varchar(1000) the other is varchar(2000).

This is with PowerBuilder 2017 R3 Build 1951.

Source DB: SQL Anywhere.

Dest DB: Postgres 16.1.

Both connections use 32-bit ODBC.  I ran the PB Pipeline using the PowerBuilder development environment.

I tried checking and not checking the "Extended attributes" checkbox and it fails either way.

Please help.

Victor Reinhart
Gray Matter, LLC.

Comment
Kai Zhao @Appeon
  1. Kai Zhao @Appeon
  2. Friday, 1 December 2023 05:15 AM UTC
Hi Victor,

What's the detailed error you get? Your issue might be caused by the default size limit(255) of the varchar type in ODBC data source. Please go to ODBC Data Source Administaror > %your data source%, then click the Datasource button on the bottom left corner and change Max Varchar to 2000 or 4000 to see if it helps.

Please refer to the article below for more details about psqlODBC Configuration Options.

https://www.postgresql.org/message-id/attachment/15329/config.html

If there is still any issue, please report it via our standard support ticketing system to ensure it is being properly received and tracked by our tech support:

https://www.appeon.com/standardsupport/. Many thanks in advance.

BTW, to make PB work well with PostGreSQL, it is planned to support .NET 6 data providers for PostgreSQL database connection in PB 2025.

Regards,Kai

  1. Helpful 2
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 19 May 2020 16:39 PM UTC
  2. PowerBuilder
  3. # 2

Hi John;

   As promised over the weekend, I tried to recreate your PL issue on my PostGreSQL test DBMS. As I suspected, I had no issues with the PBCAT tables. I tried the PL with the Drop/Add, Delete/Insert, Update, Update/Insert options and the PL execution worked 100% for me each time.

  BTW: I am using "PG 12.1 build 1914 - 64bit".   FYI ....

 

    My guess is that your problem might be related to DB security. When I set-up my PG database, I created a DBA role and then associated my login ID with this role, as follows:

HTH

Regards ... Chris

 

Comment
John Murphy
  1. John Murphy
  2. Tuesday, 19 May 2020 18:55 PM UTC
I have great hope since it is working for you that it will work out ok.

I set myself up as a super user.

CREATE ROLE "john.murphy" WITH

LOGIN

SUPERUSER

INHERIT

CREATEDB

CREATEROLE

REPLICATION

ENCRYPTED PASSWORD 'md5bba379d57241c624de7ae9fe2791c15e';



GRANT postgres TO "john.murphy" WITH ADMIN OPTION;



I then ran the book_town.sql in psql logged in as postgre

I piped the tables to SQL Server

I tried to pipe back to PG and got the error

I have only downloaded the software in the last two weeks so everything should be relatively current.

If i delete the pbcat tables before i start the job it works

I am using the unicode driver and also using v12
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 19 May 2020 00:20 AM UTC
  2. PowerBuilder
  3. # 3

Hi John;

   I've been pipelining regular tables & pbcat tables a few weeks ago from SQL Anywhere to PostgreSQL without issues. Both Create, Refresh & Update with no issues.

   We're off on the US/Canada holiday today but when I'm back in the office tomorrow, I'll try this again & let you know. BTW: I think I'm on PG 12 but will confirm more tomorrow.

Regards ... Chris 

Comment
There are no comments made yet.
Kai Zhao @Appeon
Kai Zhao @Appeon Accepted Answer Pending Moderation
  1. Monday, 18 May 2020 03:33 AM UTC
  2. PowerBuilder
  3. # 4

Hi John,

For the "ERROR: relation "dbo.pbcattbl" does not exist;" issue:
I only reproduced the issue when the "Extended Attributes" checkbox is checked and the PB extended attribute system tables doesn’t exist in the database.

Please make sure you didn’t checked the "Extended Attributes" checkbox of pipeline.
https://docs.appeon.com/appeon_online_help/pb2019r2/pbug/ch17s01.html#d0e30672

You can solve the issue by creating the PB extended attribute system tables. Please connect to your database in PB and modify or create any tables to create them. please refer to the article below for more details about the PB extended attribute system tables.
https://docs.appeon.com/appeon_online_help/pb2019r2/pbug/ch16s05.html#d0e28846


For the "Parameters exists but IPD isn't set. Please call SQLDescribeParam()" issue:
What's the detailed ODBC driver version you are using? It might be a ODBC driver bug. Please try using a new version, and please refer to the article below for details.
https://www.postgresql.org/message-id/9bdc5ee1-caea-a728-b368-a3339d99aa13%40dream.email.ne.jp

If there is still the issue, please provide us a simple PB case and detailed steps to reproduce the issue for more study, thanks.

Regards,
ZhaoKai

Comment
John Murphy
  1. John Murphy
  2. Monday, 18 May 2020 21:39 PM UTC
Just to give everyone a warm and fuzzy:

PIPELINE(source_connect=wh_data_mss,destination_connect=pg_test ansi,type=replace,commit=100,errors=100,keyname="acd_x")

SOURCE(name="acd",COLUMN(type=varchar,name="corp",dbtype="varchar(2)",key=yes,nulls_allowed=no)

no Extended Attributes!

I have not had time to build a test case but I did notice if I delete the pbcat tables and run the pipes before I open the PB development environment - it works. I I run PB and then rerun the pipes it fails.

  1. Helpful
John Murphy
  1. John Murphy
  2. Tuesday, 19 May 2020 18:57 PM UTC
no luck on the test drivers. The download link on that page is broken and the link to a Microsoft help page just takes you to the generic help page.
  1. Helpful
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Saturday, 16 May 2020 15:03 PM UTC
  2. PowerBuilder
  3. # 5

John -

   This sounds like a bug, no matter which version of PowerBuilderyou are using. I'd report it as a bug and attach your code to the ticket.


Olan

Comment
John Murphy
  1. John Murphy
  2. Monday, 18 May 2020 03:23 AM UTC
Thanks! Will do.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 15 May 2020 20:03 PM UTC
  2. PowerBuilder
  3. # 6

Hi John;

  On the 2nd round of the Pipeline ... did you change its functionality from CREATE to either Update / REPLACE or REFRESH?

Note: You only need the "Extended Attributes" checkbox checked if your copying datum to another DBMS that will be used for development OR if your planning on using the "Dynamic DW" feature (SyntaxFromSQL).

For example ...

Regards ... Chris

Comment
John Murphy
  1. John Murphy
  2. Friday, 15 May 2020 22:50 PM UTC
Yes it was replace. It was running a dynamically created pipe. I just tried setting up a new pipe and got the error:

Database error 32.

SQLSTATE - 07002

Parameters exists but IPD isn't set. Please call

SQLDescribeParam()
  1. Helpful
John Murphy
  1. John Murphy
  2. Friday, 15 May 2020 22:51 PM UTC
Extend Attributes was not used
  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.