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

Database Glossary

Concurrency

Several clients sharing a resource

Contention

Multiple users try to access a resource at the same time resulting in waits or event deadlocks.

Deadlocks

A deadlock occurs when two or more transactions are both in a wait state, with each one waiting for the other to release a lock before it proceeds.

Deadlocks are automatically detected and resolved by the DBMS. The DBMS rolls back one of the transaction usually the one with the least amount of CPU time.

Exclusive lock

A DBMS always places exclusive locks whenever a transaction performs an update operation (insert, update, delete)

A transaction cannot obtain an exclusive lock on a page or row while other transaction has any lock (shared or exclusive)

A DBMS holds an exclusive lock for the duration of a transition (until a COMMIT or ROLLBACK)

Lock Granularity

Identifies the lowest level at which a DBMS provides locking. Some DBMS offer locking at the row level while others do so at the physical database page level

Optimistic concurrency

The updated rows are checked to determine if another user has changed them since they were originally read. If the data has been updated by someone else, the transaction is rolled back and a message informs the user accordingly.

This technique assumes that the likelihood that two users will attempt to select and update the same information at the same time is low. In such cases, it also accepts that one user may lose his or her updates.

Pessimistic concurrency

Is implemented using a database management system’s locking facility to acquire and leave locks on the rows being read. A user is guaranteed data consistency because no other user can update the data while it is locked.

Pessimistic concurrency is used when the likelihood that other users will update the same data is high and absolute isolation between users is required. This type of control comes at the expense of concurrency. The can be significant with DBMS using page locking.

Shared Lock

Some DBMS place shared locks on pages while they are being read after a transaction issues a SELECT statement, and then releases them after reading each page or row.

A transaction cannot obtain a shared lock on a page while another transaction has an exclusive lock on it. However, multiple transactions can obtain shared locks on the same page or row.

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&#91;&#93;
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&#91;li_indx&#93;))
      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&#91;li_indx&#93; ))
	 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

Migrating PB9 Objects to PB7

You may find yourself working in PB 7 and wanting to use a DataWindow developed in PB 9.  Some objects are easier than others.

 DataWindows

Datawindows are addressed specifically in the previous post.  Take a look there

Windows and User Objects

Try opeingi up the  PB 9 version in PB 7.   If this works, Just save it and you are good to go.   If blows up,  here are several  possible causes:

• An invalid global variable is being referenced
• A non-standard variable is of a datatype which was not included found in the search path
• A non-standard variable refers to an object which has not yet been migrated to PB 7

You can clean up the code by editing the .srw export file with a text editor and commenting out the offending references.  When you import the file, make sure the PB 9 version is not in your library path, otherwise the import will fail.  You will not get a helpful error message or PowerBuilder will crash.

Migrating a DataWindow from PB 9 Back to PB 7

You may find yourself working in PB 7 and wanting to use a DataWindow developed in PB 9. This can be done if you export the PB 9 code (export may be done in PB 7), update the srd file, and then import it to your PB7 PBL.

PB 9 SRD file

$PBExportHeader$d_cycl_validation.srd
release 9;
datawindow(units=0 timer_interval=0 color=67108864 processing=0 HTMLDW=no print.printername="" print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overrideprintjob=no print.collate=yes hidegrayline=no )
summary(height=0 color="536870912" )
footer(height=0 color="536870912" )
detail(height=68 color="536870912" )
table(column=(type=char(10) updatewhereclause=yes name=cycl_rtg_id dbname="CYCL_RTG_ID" )
 column=(type=char(32) updatewhereclause=yes name=descr dbname="DESCR" )
 retrieve="SELECT  CRM.CYCL_RTG_ID,
        CRM.DESCR
FROM    QUAD0062..CYCL_RTG_MSTR CRM
WHERE   CRM.CYCL_RTG_ID = :CYCLE_ID
AND     CRM.PDCT_CD = 'M'
AND     CRM.RTG_CLA <> 'D'"
column(band=detail id=2 alignment="0" tabsequence=32766 border="5" color="0" x="347" y="4" height="56" width="1024" format="[general]" html.valueishtml="0"  name=descr visible="1" edit.limit=0 edit.case=any edit.autoselect=yes edit.imemode=0  font.face="Arial" font.height="-8" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" )
text(band=detail alignment="0" text="Description:" border="0" color="0" x="9" y="4" height="56" width="311" html.valueishtml="0"  name=t_1 visible="1"  font.face="Arial" font.height="-8" font.weight="700"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" )
htmltable(border="1" )
htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" encodeselflinkargs="1" netscapelayers="0" )
export.xml(headgroups="1" includewhitespace="0" metadatatype=0 savemetadata=0 )
import.xml()
export.pdf(method=0 distill.custompostscript="0" xslfop.print="0" )

Changes Needed for Converting Sample SRD File to PB 7

Note, lines 18 -21 refer to the last 4 lines of your SRD file.

• Line 2
  • Change “Release 9” to “Release 7“
• Line 3
  • Delete “print.printername=””“
  • Delete “print.canusedefaultprinter=yes“
  • Delete “print.cliptext=no print.overrideprintjob=no print.collate=yes hidegrayline=no” (end of the line)
• Line 18
  • At the end of the line, delete “encodeselflinkargs=”1″ netscapelayers=”0″ “
• Line 19-21
  • Delete these lines

PB 7 SRD file

The final SRD file is now ready to be imported into PB 7. It looks like the following:

 $PBExportHeader$d_cycl_validation.srd
 release 7;
 datawindow(units=0 timer_interval=0 color=67108864 processing=0 HTMLDW=no print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.prompt=no print.buttons=no print.preview.buttons=no )
 summary(height=0 color="536870912" )
 footer(height=0 color="536870912" )
 detail(height=68 color="536870912" )
 table(column=(type=char(10) updatewhereclause=yes name=cycl_rtg_id dbname="CYCL_RTG_ID" )
  column=(type=char(32) updatewhereclause=yes name=descr dbname="DESCR" )
  retrieve="SELECT  CRM.CYCL_RTG_ID,
         CRM.DESCR
 FROM    QUAD0062..CYCL_RTG_MSTR CRM
 WHERE   CRM.CYCL_RTG_ID = :CYCLE_ID
 AND     CRM.PDCT_CD = 'M'
 AND     CRM.RTG_CLA <> 'D' " arguments=(("CYCLE_ID", string)) )