Working with a Text File

This example has shows all the important steps for opening a text file, reading its contents and closing it.

integer     li_rc = SUCCESS, li_file_nbr, li_indx, li_insert, li_fr_rc //File Read Return Code
long        ll_find
n_bo_dw_asg lnv_dw
string      ls_msg, ls_file, lsa_email[], ls_email, ls_line, ls_find

wf_RefreshAvailUsers()

// Open dis file
if li_rc = SUCCESS then
   ls_file = asa_location[1] + as_prov_cd + ".dis"  // path needed
   li_file_nbr = FileOpen(ls_file, LineMode!)
   if IsNull(li_file_nbr) or li_file_nbr < 1 then
      li_rc = NO_ACTION
   end if
end if

// Read dis file, store e-mails into array lsa_email[]
if li_rc = SUCCESS then
   li_indx = 0
   do while li_indx > -1 and li_rc = SUCCESS
      li_fr_rc = FileRead(li_file_nbr, ls_line)
      choose case li_fr_rc
	 case is > 0
	    li_indx ++
	    lsa_email[li_indx] = ls_line
	 case 0, -100
	    li_indx = -1
	 case -1
	    li_rc = FAILURE
	    ls_msg = "Error with FileRead"
      end choose
   loop
end if

//highlight all e-mails in dw_available
if li_rc = SUCCESS then
   li_indx = 0
   do while li_indx < UpperBound(lsa_email) and li_rc = SUCCESS
      li_indx ++
      ls_email = Lower(Trim(lsa_email[li_indx]))
      ls_find = 'Trim(Lower(email_nm)) = "' + ls_email + '" or ' + &
	        'Trim(Lower(c_email2)) = "' + ls_email + '" or ' + &
                'Trim(Lower(c_email3)) = "' + ls_email + '" or ' + &
	        'Trim(Lower(c_email4)) = "' + ls_email + '"'
      ll_find = lnv_dw.of_Find(idw_prov_available, ls_find, ls_msg)
      choose case ll_find
	 case is < 0
	    li_rc = FAILURE  // ls_msg set in function
	 case 0
	    li_insert = idw_prov_selected.InsertRow(0)
	    idw_prov_selected.SetItem(li_insert, "email_nm", Trim(lsa_email[li_indx] ))
	 case else
	    idw_prov_available.SelectRow(ll_find, True)
      end choose
   loop
end if

if li_rc = SUCCESS then
   icb_add.Event Clicked()
   idw_prov_selected.SetSort("c_employee_nm" )
   idw_prov_selected.Sort()
end if

//Clean up
if li_file_nbr > 0 then
   if FileClose(li_file_nbr) = FAILURE then
      li_rc = FAILURE
      ls_msg = "Error with closing .dis file"
   end if
end if

if li_rc = FAILURE then
   MessageBox("of_GetDisFile()", ls_msg)
end if

Return li_rc

Stored Procedures

Stored Procedures as External Function

Declare a Local External Function in n_tr_lm object.  (In this  example, SQLCA is  declared to be of type n_tr_lm, a descendant of n_tr)

FUNCTION long get_next_id(string TABLE, string COLUMN, long INCREMENT) &
   RPCFUNC ALIAS FOR “QUAD0076.dbo.sp_qg_get_next_id”

Powerbuilder script to call stored proc:

ll_next_job = SQLCA.get_next_id(“JOB_QUEUE”, “JOB_ID”, 1)

Calling a stored procedure using a local function which is not an external function

Example:

DECLARE redo_list PROCEDURE FOR
    @li_rc = QUAD0035.dbo.ml_create_cm_redo_list
    @CmEvent = :al_cm_id,
    @Output_List_id = :ll_output_id OUT
USING SQLCA;

EXECUTE redo_list;

IF SQLCA.SQLCode = -1 THEN
   ls_msg = SQLCA.SQLErrText
   MessageBox("of_redo_stored_Proc", ls_msg)
ELSE
   // Put the return value into the var and close the declaration.
   FETCH redo_list INTO :li_rc, :ll_output_id;
   CLOSE redo_list;
END IF

Calling a stored procedure with an output argument

In our Sybase environment, I found that the “dbo” was required in the stored procedure name

DECLARE redo_list PROCEDURE FOR
   @li_rc = QUAD0035.dbo.ml_create_cm_redo_list
   @CmEvent = :al_cm_id,
   @Output_List_id = :ll_output_id OUT
USING SQLCA;

EXECUTE redo_list;

IF SQLCA.SQLCode = -1 THEN
   ls_msg = SQLCA.SQLErrText
   MessageBox("of_redo_stored_Proc", ls_msg)
ELSE
   // Put the return value into the var and close the declaration.
   FETCH redo_list INTO :li_rc, :ll_output_id;
   CLOSE redo_list;
END IF

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;

Class() and TypeOf()

Class()

The class is the name of an object. You assign the name when you save the object in its painter.

ls_class = Lower(adw.ClassName())

In the above script, the class name will be n_ds or u_dw, or n_ds_add_rows. Here is another sample script:

choose case adw_wrkr_tm.ClassName()

case “dw_mach_op”

ls_type = “Direct Time”

case “dw_handwork_press”

ls_type = “Press Handwork”

case “dw_handwork_finishing”

ls_type = “Finishing Handwork”

case “dw_indirect_labor”

ls_type = “Indirect Time”

end choose

TypeOf()

TypeOf reports an object’s built-in object type. The types are values of the Object enumerated data type, such as Window! or CheckBox!. ClassName reports the class of the object in the ancestor-descendant hierarchy.

choose case TypeOf(adw)

case datastore!

lv_rc = TRUE

case else

lb_rc = FALSE

enc choose

If adw is a descendant of datastore!, like n_ds, it is still of TypeOf datastore!

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