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

Nulls


I find nulls to be the cause of so many bugs. Here are a few things that have helped me get my mind around this surprisingly confusing topic.

Let’s look at the following script, what would the result be?

Continue reading

August 14, 2008 Posted by | Powerscript | , , , | 2 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

Variables


Arrays

Initializing

String ls_array[]
Ls_array = {“value1”, “value2”,“value3”, “value4”}

Set to null

String ls_arrayofnulls
SetNull(ls_arrayofnulls)
// …
// … some code that loads values into ls_array[]
// ..
ls_array – ls_arrayofnulls

Decimal

decimal {4} a,b,d,e,f
decimal {3} c
a = 20.0/3                  // a contains  6.6667
b = 3 * a                   // b contains 20.0001
c = 3 * a                   // c contains 20.000
d = 3 * (20.0/3)            // d contains 20.0000
e = Truncate(20.0/3, 4)     // e contains  6.6666
f = Truncate(20.0/3, 5)     // f contains  6.6667

Default Values

Variable

Default Value

Blob

A blob of 0 length; an empty blob

Char (or character)

ASCII value 0

Boolean

FALSE

Date

1900-01-01 (January 1, 1900)

DateTime

1900-01-01 00:00:00

Numeric (integer, long, decimal, real, double, UnsignedInteger, and UnsignedLong)

0

String

Empty string (“”)

Time

00:00:00 (midnight)

Variable Declaration, setting to null

To set a variable to null in the declaration statement

integer si_dup_printer_adjust = SetNull(si_dup_printer_adjust)

Integer

integer i
i = 32767
i = i + 1     // i is now –32768

String

A standard data type that is characters enclosed in single (‘) or double (“) quotation marks, including a string of 0 length (the empty string “”). The maximum number of characters in a string is 60,000.

Null

integer a, b=100, c
SetNull(c)
a = b+c    // all
statements set a to NULL
a = b - c
a = b*c
a = b/c

Masks

! Upper Case
^ Lower Case
a Alphabetical
x Any character
# Number

Sample format

5

-5

.5

[General]

5

-5

0.5

0

5

-5

1

0.00

5.00

-5.00

0.05

#,##0

5

-5

1

#,##0.00

5.00

-5.00

0.50

$#,##0;($#,##0)

$5

($5)

$1

$#,##0;-$#,##0

$5

-$5

$1

$#,##0;[RED]($#,##0)

$5

($5)

$1

$#,##0.00;($#,##0.00)

$5.00

($5.00)

$0.50

$#,##0.00;[RED]($#,##0.00)

$5.00

($5.00)

$0.50

0%

500%

-500%

50%

0.00%

500.00%

-500.00%

50.00%

0.00E+00

5.00E+00

-5.00E+00

5.00E-01

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