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.
- Use the Database Trace
Whenever you're having trouble with a pipeline during development or runtime, it's always a good idea to examine the trace of either the source or destination side to quickly find out what is being generated. SQLCA.DBMS=Trace MSS (using MS SQL Server as an example). It might be best to trace one connection at a time since PB writes to one pbtrace.log file.
- Use the DBA Notepad (DB Administrator)
If this is a development environment problem, cut the SQL that's failing out of the trace and place it in the DBA notepad. Work with it until you get it to return the correct results. This can give you some insight into how the pipeline is working and how to get it to work correctly.
- Look at the Pipeline source.
Export the pipeline object to source using Entry/Export in the library painter. View the source in the Powerbuilder built-in file editor (Shift-F6). You can often detect where an error is coming from quickly and since there is no SQL convert-to-syntax mode for pipelines, it's the only way to investigate what is saved in the pipeline object.
- Test the pipelines with different
users logged on.
Often, what user we log in as (DBA or regular user) affects the qualification inside both datawindows and pipelines. Sometimes these are just common table permissions issues. Make sure the SQL saved in the pipeline will run when you log in as a user who doesn't own the tables.