Schultz’s PowerBuilder Notes

Sybase and Nulls


If you have not read about how PowerBuilder treats nulls, you may want to read  nulls first.

Continue reading

Advertisements

January 7, 2009 Posted by | Database | , , , , | 1 Comment

PFC Security Q and A


How do I enable the PFC security service?

A common place to do this the pfc_postopen event of w_frame

gnv_app.of_SetSecurity(True) //Enable security service

How do I force security to run on a particular object

Continue reading

July 21, 2008 Posted by | 3. PFC, Utility Services | , , , , | Leave a comment

Dynamic DataWindows


How do I build a DataWindow dynamically?

  1. Build the SQL statement for the DataWindow object as a string and describe he form and presentation style of the DataWindow object
  2. Associate the new DataWindow object with a DataWindow control

Continue reading

May 26, 2008 Posted by | 2. Datawindows | , , , , , , , , , , , | 5 Comments

SQL Painter


How can I use Retrieval Arguments to make my where clause more flexible?

Here is a solution I came up with to avoid the need to dynamically modify the Where Clause, or creating multiple datawindows with slightly different SQL. The JOB_QUEUE_RULE table has IDs which must be a positive number ( a good place for a check constraint in the table’s DDL). If you want to see all the rules, set the retrieval argument to 0, otherwise set the ret arg to the ID you want to see.

SELECT R.ID,
       R.DESCRPTION
  FROM dbo.JOB_QUEUE_RULE   R
 WHERE ((R.ID = :ra_id) OR (:ra_id = 0 ))

How do I limit the number of tables to those with a certain prefix?

There exists a parameter to limit the tables you view in the database painter. Here’s the language you need.

  • Click the DB profile tool bar button
  • Select the profile you want and click EDIT (this action displays the DATABASE PROFILE SETUP dialogue)
  • Select the SYSTEM tab
  • Go to the TABLE CRITERIA section and type ,W11559,”TABLE” (or replace W11559 with whichever account you want)
  • Click OK (this action returns you to the DATABASE PROFILES dialogue)
  • Click CONNECT
  • Help can be found under TABLECRITERIA (one word)

How can I use Dual (Oracle) to populate my initial search screen?

SELECT '' as secretary_code,
'' as LOG_SEQ_NO,
'' as governor_log_no,
'' as SUBJECT_DESC,
' as BUREAU_REGION_CODE,
to_date('' ) as FR_received_on_date,
to_date('' ) as TO_received_on_date,
to_date('' ) as FR_DUE_DATE,
to_date('' ) as TO_DUE_DATE,
to_date('' ) as FR_letter_out_date,
to_date('' ) as TO_letter_out_date,
to_date(to_date(to_char(add_months(sysdate,-12),'mm/dd/yyyy' ), 'mm/dd/yyyy' )) as FR_date,
to_date(to_char(sysdate,'mm/dd/yyyy' ),'mm/dd/yyyy' ) as TO_date,
'' as leg_cust_last_name,
'' as leg_cust_first_name,
to_date('' ) as fr_letter_date,
to_date('' ) as to_letter_date
FROMDUAL

Magic where clause for datawindow receiving a date retrieval argument

WHERE “WM_HUNTERS_CHOICE_APPLICANT”.”DOB” = to_date(:as_dob, ‘mm/dd/yyyy’ )

When I try to switch from Syntax mode to Graphic mode, PowerBuilder gives me a message saying that it can’t.

Tables associated with your SQL can not be accessed from your current database profile. Switch to a database profile with the correct server and system login ID and password.

May 9, 2008 Posted by | 2. Datawindows, Datawindow Painter | , , , , , , , | Leave a comment

Temp Tables


How do I destroy a temp table (Sybase)

Example:

Continue reading

May 6, 2008 Posted by | Database, Powerscript | , , , , , | 1 Comment

Transaction Object


Properties for database communication

Property

Type

Description

DBMS

String

Name of the DBMS

Database

String

Name of the database

UserID

String

User ID for database logon

DBPass

String

Password

Lock

String

Isolation level

LogID

String

User ID for server logon

LogPass

String

Password

ServerName

String

Name of the server

AutoCommit

Boolean

Turns transaction processing on or off, where supported

DBParm

String

DBMS specific settings

“Live” Properties

When you assign a value to most properties, the value is stored and no immediate action is taken. However, AutoCommit and DBParm are “live” properties. If you are connected to the database, and you assign a value to either of these properties, a message is sent to the database.

Properties that return status information

Property

i) Type

Description

SQLCode

long

Status code for the last SQL operation 0 Ok 100, No result set -1 error

SQLNRows

long

Number of rows affected by most recent SQL operation.

SQLDBCode

long

DBMS specific error code

SQLErrText

string

DBMS specific message

SQLReturnData

string

DBMS specific

April 22, 2008 Posted by | Database | , , , , , | 1 Comment

Embedded SQL


If you are using embedded SQL, check the SQLCode, SQLDBCode, and SQLErrText properties of the transaction object.

0

Ok

-1

Error

100

No result set pending (no da

The values for SQLDBCode and SQLErrText properties are database specific

Do not check transaction object properties when you are using DataWindow functions.

Example

if li_rc = SUCCESS then
   select MPU_ID
     into :ll_mpu_id
     from QUAD0070..MPU
    where MPU_ID = :al_mpu_id
    using itr_mdat_assign;

   choose case itr_mdat_assign.SQLCode
      case 0
     lb_rc = TRUE
      case 100
     lb_rc = FALSE
      case else
     li_rc = FAILURE
     ls_msg = "Unknown SQLCode: " + String(itr_mdat_assign.SQLCode)
   end choose
end if

Execute transaction object refers to a different database

Sometimes the transaction object needs to refer to a different database. Before executing, you need to

//Transaction object should refer to Q62, Q70 is what it normally refers to
EXECUTE IMMEDIATE "USE QUAD0062" USING itr_mdat_assign;

ll_next_id = f_get_next_id("COMPONENT","CPT_ID", "NEXT_IDENTIFIER", 1, itr_mdat_assign)

EXECUTE IMMEDIATE "USE QUAD0070" USING itr_mdat_assign;

April 21, 2008 Posted by | Powerscript | , , , , , , , , | Leave a comment