Schultz’s PowerBuilder Notes

Datawindow Retreive With Large In Clause


Our DBMS limits the number of Items in an “IN Clause” to 255. If you attempt to retrieve with an array of a larger size, the retrieve fails. One method of dealing with this constraint is to create a temp table, populate this with your retrieval arguments, and then have your datastore join to the temp table during the retrieve. Another way is to use a function similar to this: Continue reading

Advertisements

August 10, 2009 Posted by | 2. Datawindows, Uncategorized | , , , , , , | 1 Comment

ItemChanged Event


Return codes

Return Code

Action

0

Accept the data value

1

Reject the data value

2

Reject the data value but let the focus change

Continue reading

May 29, 2008 Posted by | DataWindow Control | , , , , , , , , | Leave a comment

Error Event


When does the Error event fire?

The Error Event occurs at execution time as the result of syntax errors in DataWindow property expressions and DataWindow data expressions. If you refer to a nonexistent object, misspell a property name or reference nonexistent data the Error event fires.

Dw_1.Object.emp_id.Visible=’0’

Continue reading

May 16, 2008 Posted by | DataWindow Control | , , , , , , , , , , | Leave a comment

Prompt for Criteria


When a retrieve is performed, a dialog box appears and prompts the user to supply Retrieval Criteria.

Continue reading

May 16, 2008 Posted by | DataWindow Control | , , , , , , | Leave a comment

How come my dddw pops up a response window asking for retrieval arguments when I try to insert a row?


If the datawindow tries to insert a new row, and the dddw is not yet populated, the response window asking for Retrieval Arguments will be displayed.  Here are 3 ways to solve this problem:

  1. Get the handle of the DDDW using GetChild, SetTransObject() and Retrieve() before the parent is filled. Then filter the invalid rows based on the values of the columns entered. This works fine if there are only a modest number of rows in the DDDW universe and this data is static.
  2. Pre-store data in the DDDW in the DataWindow painter. Then filter the invalid rows based on the values of the columns entered. This requires the data to be static. If it is not, the datawindow has to be updated, and a new build will need to be deployed.
  3. Get the handle of the DDDW using Get Child and InsertRow() to give it a result set. The DDDW will have a blank row, but at least the annoying response window will not be in the user’s face. The row with the DDDW is protected until all the columns from which retrieval arguments are derived are entered. Then the DDDW is populated with a retrieve (or copied from a cache).

May 10, 2008 Posted by | Drop Down Data Window (DDDW) | , , , , , , | 1 Comment

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

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