Debugging case studies

DataWindow related errors

DataWindow retrieve error

An error has occurred when executing the following SQL statement via DataWindow retrieve.

SQL syntax:

select  id typeid,
    "Fname"||' - '||"Lname" as FullName,
    diner + interval '1 day' dinneAdd1,
    costs* 0.85 *(case when id%2 = 0 then 1 else -1 end ) zk,
    birthday + interval '10 day' ,
    salary * 1.1 sales,
    to_char(cast(mobilephone as int),'###-####-###')
    from t_dwstyle_alltype a
    where /*DATEDIFF(d,birthday,'1000-02-01') between 8 and 10*/
    EXTRACT(day from birthday - to_timestamp('1000-02-01','YYYY-MM-DD')) > 8 and EXTRACT(day from birthday - to_timestamp('1000-02-01','YYYY-MM-DD')) < 10 
    or birthday is null
    order by typeid

When the DataWindow retrieves data using the SQL, an error message pops up: The property does not allow null value: Dinneadd1.

Debugging technique:

The error means that the model generated from the DataWindow has set non-nullable attribute to the column. However, the Dinneadd1 column is a computed column, so it shall be nullable. It is necessary to check the .cs file of the generated model, to find the column and set nullable attribute to it.

For example, the column defined in the model is:

        public TimeSpan Dinneadd1 { get; set; }

Add the nullable attribute to the column:

        public TimeSpan? Dinneadd1 { get; set; }

Note: If the SQL syntax contains left join, or union, it may possibly induce similar error.

SyntaxFromSQL execution error

The application crashes when executing the same SQL statement via SyntaxFromSQL.

Debugging technique:

We shall first check the web debugging proxy tool, such as Fiddler, to locate at what operation the error occurred (at the execution of SyntaxFromSQL), and also find out the error message captured. Check in the Inspector TextView for the possible causes:

  • Is there SqlErrorText?

  • Is any column name empty? --- Check the columnname;

  • Any improper date type? --- Check the datatype;

  • Issue with the column length or precision?

Then you will see there is a null column name. After setting an alias to the null column, SyntaxFromSQL can then be executed successfully:

Column in the SQL: birthday + interval '10 day'
Add an alias to the column: birthday + interval '10 day' rq
Different execution results in different databases

Supposing we are executing the following CREATE TABLE syntax in SQL Server and Oracle.

CREATE TABLE appeon_test (id integer NOT NULL,testname varchar(40) NOT NULL , testdate date  , testnumber decimal(12,3)  , PRIMARY KEY (id)) 

With Oracle, the table created is as below:

With SQL Server, the table created is as below:

In the PowerBuilder DataWindow SRD, the datatype is long, which can work well in both databases.

table(column=(type=long update=yes updatewhereclause=yes key=yes name=id dbname="" )

When converting the DataWindow to C# model, with SQL Server, the Id column is of int type:

[DwColumn("appeon_test", "id")]
        public int Id { get; set; }

Because the Id column of the table is Number in Oracle, when the same model tries to retrieve data from the Oracle database, an error occurs:

Therefore, if using the Oracle database, the model Id shall be changed to the decimal data type.

[DwColumn("appeon_test", "id")]
        public decimal Id { get; set; }

If you hope to run the same model against different databases, it is necessary to add ValueConverter too the model column in the .cs file by:

        [DwColumn("appeon_test", "id")]
        public int Id { get; set; }
Incompatible data type

With PostgreSQL, when retrieving data into a DataWindow that uses stored procedure as its data source, an error occurred:

Select Error: The Decimal type is not compatible with the data type of the mapped property 'Unit_Weight' on model 'Dw_Mat_Items_Inquiry_List'.

Debugging technique:

Search for the model ”Dw_Mat_Items_Inquiry_List” in SnapDevelop and then “Unit_Weight” in the model .cs file. In the stored procedure of the DataWindow, the data type of Unit_Weight is NUMBER (12,3). According to the Data type mapping tables, the Number data type is mapped to decimal. Therefore, the data type of Unit_Weight shall be changed to decimal in the .cs file:

Public decimal? Unit_Weight { get; set; }
PBSELECT retrieve error

Sample PBSELECT script:

retrieve="PBSELECT( VERSION(400) TABLE(NAME=~"dec_emp~" )

PBSELECT may easily cause errors. When the SQLPreview event (executed at the client side) converts the PBSELECT to the SELECT syntax, the event may cause the client crash or arrive at incorrect SQL syntax (the syntax relies on the DisableBind, DelimitIdentifier settings in dbparm).

If PBSELECT contains outer joins of multiple tables, there may be unknown error when it is converted to SELECT, and such error cannot be identified by Fiddler because there is no communication from the client to the server.

Debugging technique:

Check in Fiddler whether a connect has occurred, and whether the connection is successful.

If there is no connection and the client app has crashed, the issue may be caused by PBSELECT. To avoid the problem, better change PBSELECT to a SELECT statement in the application source code, and then deploy the application again.

Embedded SQL related errors

Possible error when executing an embedded SQL: ErrorMesage: 42883: function up_ods301_005(integer, timestamp with time zone) does not exit

Debugging technique:

When an embedded SQL reports error, the recommended way is to run the server Web APIs in debug mode, and check the SQL statement in the Output panel.

For example, the original embedded SQL is:

select * from up_ods301_005(20,'1981-01-01');

And the actual statement shown in the Output is:

select * from up_ods301_005(@P0,@P1)
@P0 = 20
@P1 = 1981-01-01 12:00:00.000

You can then notice that the data type of the second parameter does not match in the two statements (the first one is data, and the second one is timestamp with time zone) (for more accurate data type information, use the web debugging proxy tool).