Schultz’s PowerBuilder Notes

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

Advertisements

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

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.

April 22, 2008 Posted by | Database | , , , , , , | Leave a comment

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

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

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

April 21, 2008 Posted by | Database, Powerscript | , , , , , , , , , , , , | 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

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!

April 21, 2008 Posted by | Powerscript | , , , , , | 1 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

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.

April 17, 2008 Posted by | 1. PowerBuilder General, PowerBuilder Versions | , , , , , , , , , , | Leave a comment

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)) )

April 16, 2008 Posted by | 2. Datawindows, PowerBuilder Versions | , , , , , , , , , , | Leave a comment