Working with PowerBuilder Pipeline Objects
Native Driver behavior and USERID:
Pipelines work differently in the development environment vs. runtime when using any native drivers for Oracle, Sybase, Informix and Microsoft (e.g. SYB, SYC, IN5, OR7, O73, MSS, etc). This is similar to the behavior of syntaxfromsql with SQLCA.USERID required for table prefixing and extended attributes lookup.Since PowerBuilder 1.0, the development environment always adjusts the USERID so users see their extended attributes. However, in the runtime environment using native drivers, it is the programmer's responsibility to set the USERID in the transaction object since Powerbuilder supports runtime overrides making this a requirement.
The USERID string is used to determine column qualification rules, index lookup, and extended catalog lookup. For the native driver side of the pipeline to behave identical to the development environment, you need to set the USERID in the transaction object to the same value PowerBuilder uses in the development environment. This would be the default catalog or table owner for the particular DBMS.
We don't adjust the USERID at runtime because the runtime engine doesn't know whether the programmer has purposely set USERID to a different value to trigger the addition or stripping of qualifiers. If the Powerbuilder runtime blindly set USERID=LOGID, for example, it could break some applications using Embedded SQL and the same transaction object as the pipeline.
SQLCA.USERID isn't used for native drivers for anything else. It's documented as "Not Required" for most native drivers in "Connecting to Your Database" and isn't required for connecting. SQLCA.DBPASS is not required at all.
Many runtime errors with native drivers and pipelines can be tracked down to table qualification errors either when the native driver is on the source or destination side. Typical problems are Adaptive Server Anywhere (ASA) to Adaptive Server Enterprise (ASE), ASA to DB2, ASA to Net Gateways, etc. ie any native driver on the destination side. You can encounter problems on the source side as well.
Problem: My native driver is on the destination side of the pipeline. In development, it works fine but when deployed as an executable, I get errors during the CREATE phase of my destination tables. What can cause this?
Resolution: In these cases, the correct destination SQL for native driver table name qualification requires you to use USERID (e.g. "dbo" for Sybase, "sysibm"for DB2). You need to populate the USERID with the name you want to use to qualify the DESTINATION table.
Question: My native driver is on the source side of the pipeline. I create dynamic datawindows using the destination side connection but I'm missing my extended attributes - even though I have "Extended Atributes" checked in the pipeline object I'm using. It works fine in development but not in the executable. What could be causing this?
Resolution: The native driver on the source side requires SQLCA.USERID to be populated with the owner of the tables in order to qualify the SQL correctly for looking up the pbcat tables in order to pipe them over. If the destination is also a native driver, you may also have to qualify the USERID there as well so the pbcat* tables get prefixed and updated correctly.