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

Guaranteeing a Singleton


Because the datetime on the client machine may not be accurate, I wanted to write a function to get the current date and time from the server.  

For Sybase DBMS, this is simply achieved with the GetDate() function.  The problem is developing the SQL to return exactly one row everytime the function is called.  (It will still work if the SQL returns multiple rows, but you will have a lot of unecessary database traffic.)

Continue reading

September 19, 2008 Posted by | Database, Powerscript | , , , , | Leave a comment

Avoiding the Use of Cursors in a Stored Procedure


This example Stored Proc (Sybase DBMS) loops through a result set one row at a time without the use of a cursor. It also has a recursive call.

Continue reading

May 20, 2008 Posted by | Database | , , , , , , , | 1 Comment

Sybase Error: 2403 “WARNING! Some character(s) could not be converted…


I was working on a application that I am not too familiar with. While running the app in the PowerBuilder developer environment, I was getting the following database error:

Database error code: 2403

Select error: WARNING! Some character(s) could not be converted into client’s character set. Unconverted bytes were changed to question marks (“?”).

Continue reading

May 12, 2008 Posted by | Database | , , , | Leave a comment

Temp Tables


How do I destroy a temp table (Sybase)

Example:

Continue reading

May 6, 2008 Posted by | Database, Powerscript | , , , , , | 1 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