Discrepancies

PowerBuilder and PowerServer have discrepancies in dealing with features such as the alias name, the DisableBind parameter etc.

Due to these discrepancies they might have different behaviors at runtime.

DB connection

In traditional client/server applications, one application just uses one database connection.

In applications deployed from PowerServer, each transaction uses a database connection, and when the transaction is completed, the database connection is ended.

Alias name

For dynamic DataWindow objects that are created by SyntaxFromSQL, if the alias name is the same as the column name, you will need to carefully check if the correct column name is used in the scripts.

Take the following as an example. PowerBuilder will use "t_dwstyle_grid_employ_empid" as the column name, while PowerServer will use "empid" as the column name.

select t_dwstyle_grid_employ.empid as empid, t_dwstyle_grid_employ.empname as empname, t_dwstyle_dept.deptname as deptname 
from t_dwstyle_grid_employ, t_dwstyle_dept
where t_dwstyle_grid_employ.deptid = t_dwstyle_dept.deptid and t_dwstyle_grid_employ.empid < 500

Thus, the following script will cause a runtime error in the installable cloud app.

getitem (row, "t_dwstyle_grid_employ_empid")

Data type mismatch

If data type is corrected while SRD is not re-generated to reflect the change (as shown below, data type is still mismatched), PowerBuilder will throw an error when trying to retrieve data, while PowerServer will retrieve data successfully.

column=(type=long update=yes updatewhereclause=yes name=starttime dbname="t_dwstyle_grid_employ.starttime" )

rowsupdated value

When the column is set to not updatable (as shown below), modifying the column data and then performing an update will cause the rowsupdated argument of the UpdateEnd event to:

  • Return 1 in PowerBuilder, but actually no update statement is generated at all.

  • Return 0 in PowerServer, indicating that no rows are updated.

dw_control.Object.columnname.Update = "No"

DisableBind parameter

Suppose the database column updatetime is defined as below.

updatetime  datetime  not null default  getdate()

If the application user inserts a new row, but does not enter a value for this column, then when DisableBind is set to 0,

  • PowerBuilder throws an error indicating that the updatetime column cannot be null.

  • PowerServer inserts the data row to the database successfully (the updatetime column takes the default value from database).

    In PowerServer, DisableBind always takes value 0 for the UPDATE statement and may ignore the null value according to the sqldefault attribute; while takes value 0 or 1 for the SELECT statement.

TableBlob retrieval

If the TableBlob control selects a text field, PowerBuilder retrieves data for all columns except for this blob; while PowerServer retrieves no data (thus DataWindow will have no data at all).


Dynamic DataWindow

When a computed field has no alias, PowerBuilder will use the computed expression as the column title, while PowerServer will automatically give a name (such as "Compute2") as the column title.

TransactionName

PowerServer will use the transaction object name to map with the database cache name. But when the transaction object is defined as an argument of a function, as shown below, PowerServer will get different transaction name from PowerBuilder.


transaction ltr_tmp
ltr_tmp = create transaction
gnv_manager.of_connect(ltr_tmp,"qa_datawindow")
  • PowerBuilder will get ltr_tmp as the transaction object name.

    For example,

    dw_1.settransobject(ltr_tmp)
    dw_1.retrieve()
    
  • PowerServer will get atr_transobject as the transaction object name.

Defining the transaction object as an instance variable or global variable can ensure PowerBuilder and PowerServer get the same transaction object name.

Data type in Dynamic SQL Format 4

In PowerBuilder, the Oracle database may return the numeric data as the decimal type.

Row=1, Column=1, type=Decimal, value=1
Row=2, Column=1, type=Decimal, value=2

While in PowerServer, the Oracle database may return the numeric data as the longlong type.

Row=1, Column=1, type=LongLong, value=1
Row=2, Column=1, type=LongLong, value=2

It is recommended that the developer use "choose case" to support the longlong-type numeric data.

CHOOSE CASE SQLDA.OutParmType[n]
case TypeLongLong!
   ls_DataType = 'LongLong'
   ls_Value = String(adda_parm.GetDynamicDecimal(li_Idx))

Decimal data type in static SQL or DataWindow

PowerServer will return numeric data with a fixed decimal point length according to the decimal precision of the column. If the decimal place is insufficient, zero will be automatically filled; while PowerBuilder will not fill zero after the decimal point of decimal data. For example, the money-type data may display as an integer (for example, 40) in PowerBuilder; while display as a floating point number with 4 decimal places (for example, 40.0000) in PowerServer.

Timing of transaction rollback

If the SELECT statement is executed after the UPDATE statement (like below) and if the execution of UPDATE is successful while the execution of SELECT is not, PowerServer will immediately roll back the transaction (and roll back UPDATE), while PowerBuilder will not. This may cause that data to be retrieved later will be different between PowerServer and PowerBuilder.

update dbparm_fortest set name_char = :ls_tmp1,name_varch=:ls_tmp1 where id = :li_id using tran01; 
select "name_char",name_varch into :ls_char,:ls_varchar from dbparm_fortest where "id"= :li_id using tran01;
ls_return += "ls_char=" + ls_char+" "+string(len(ls_char)) + is_newline
ls_return += "ls_varchar=" + ls_varchar+" "+string(len(ls_varchar)) + is_newline
select count(1) into :ll_count from dbparm_fortest where name_char = :ls_name using tran01;

Oracle AutoCommit

The Oracle AutoCommit property takes effect in PowerServer, while takes no effect in PowerBuilder.

Stored procedure parameter

If the output parameter of stored procedure has default values, even if PowerBuilder did not pass the output parameter, the server can still use the default value of the stored procedure to successfully get data. However, in PowerServer, if PowerBuilder did not pass the output parameter, PowerServer will use null as the default value. This will cause the result set different between PowerBuilder and PowerServer.

If the parameter name or number does not match, PowerBuilder will display an error indicating that the parameter does not exist; while PowerServer will check the schema of stored procedure or function and automatically match the corresponding parameter type and position, therefore PowerServer may be able to execute the stored procedure without errors.

Cursor syntax

If the Cursor definition syntax has redundant statements, for example, into, as shown in the example below, PowerServer will consider it (ls_result in the following example) as a parameter and will display the error: The number of parameters does not match.

string ls_sql, ls_result
ls_vid = '100002'
ls_vname = ''
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
ls_sql = 'SELECT v_name into :ls_result  FROM employee WHERE v_id = ?'
PREPARE SQLSA FROM :ls_sql ;
OPEN DYNAMIC my_cursor using :ls_vid ;
FETCH my_cursor INTO :ls_vname ;
CLOSE my_cursor ;

Transaction commit

If the commit statement is executed after Transaction.autocommit = true, PowerServer will display the error: Database connection or transaction is not opened, commit is invalid. To avoid this error, do not execute commit/rollback after Transaction.autocommit = true.

If the commit statement is executed after the select statement, PowerServer will display an error indicating that the database connection failed due to the unknown logic error. To avoid the error, remove the commit statement after the select statement.

Syntax check after union

PowerBuilder will not check the syntax after the UNION keyword. In the following example, the syntax after UNION is invalid (a space is missing between "SELECT" and ":as_test"). This will cause DataWindow to fail to be converted to model.

SELECT :as_test test1, Dept.ID, Dept.Name  FROM Dept   
union
  SELECT:as_test test1,Dept.ID,   Dept.Name    FROM Dept  

Use Describe in Dynamic SQL Format 4

You can no longer use Describe in Dynamic SQL Format 4 to check the SQL syntax. For example,

describe sqlsa into sqlda;
if not this.of_checktrans(atr ) then
        return string(atr.sqlcode)+":"+atr.sqlerrtext
end if

However, this discrepancy can be ignored as the SQL syntax will be checked when opened or executed on the server.

Bit data field

When the SNC SQL Native Client database interface is used, the Bit data field returns -1 when the data value is 1.

It is recommended to use IsPowerServerApp to determine the scripts to execute for the application deployed via PowerServer.

Column order in data source and Column Specification

If the columns listed in the data source and in the Column Specification are not in the same order, the data retrieval will fail.

Suppose the data source is

SELECT "employee"."emp_id","employee"."emp_fname","employee"."start_date" FROM "employee"

If the column specification is changed from

table(column=(type=long update=yes updatewhereclause=yes key=yes name=emp_id dbname="employee.emp_id" )
column=(type=char(20) update=yes updatewhereclause=yes name=emp_fname dbname="employee.emp_fname" )
column=(type=date update=yes updatewhereclause=yes name=start_date dbname="employee.start_date" )

to

table(column=(type=long update=yes updatewhereclause=yes key=yes name=emp_id dbname="employee.emp_id" )
column=(type=date update=yes updatewhereclause=yes name=start_date dbname="employee.start_date" )
column=(type=char(20) update=yes updatewhereclause=yes name=emp_fname dbname="employee.emp_fname" )

then the DataWindow will be converted incorrectly to the model

[Key]
[DwColumn("employee", "emp_id")]
public int? Emp_Id { get; set; }
[ConcurrencyCheck]
[DwColumn("employee", "emp_fname", TypeName = "char")]
public string Start_Date { get; set; }
[ConcurrencyCheck]
[DwColumn("employee", "start_date")]
public DateTime? Emp_Fname { get; set; }

And the following error will occur when retrieving data in the application:

sqlerrtext=Invalid object name 'employee'.