1. 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

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 19 May 2020 16:39 PM UTC
  2. PowerBuilder
  3. # 1

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
  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 Accepted Answer Pending Moderation
  1. Tuesday, 19 May 2020 00:20 AM UTC
  2. PowerBuilder
  3. # 2

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 Accepted Answer Pending Moderation
  1. Monday, 18 May 2020 03:33 AM UTC
  2. PowerBuilder
  3. # 3

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
  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
  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 Accepted Answer Pending Moderation
  1. Saturday, 16 May 2020 15:03 PM UTC
  2. PowerBuilder
  3. # 4

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
  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 Accepted Answer Pending Moderation
  1. Friday, 15 May 2020 20:03 PM UTC
  2. PowerBuilder
  3. # 5

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
  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
  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.