Powerbuilder Datawindow + Datetime2 Error

[Former Member]

I am in the process of Converting a large application to work against SQL Server 2014. It is currently running against Sybase ASE 15.

The Problem is when I try to retrieve a value from a column that is defined as Datetime2, I get "Error: Invalid DataWindow row/column specified at line". This line in defined in the Sybase table as Datetime and retrieves correct.

Also, there are no milliseconds data value for this column.

Any ideas of what is causing it or how to correct it?

Thanks in advance.

Shai

[Former Member · Accepted solution]

Hi Shai;

  The DateTime2 data type is a Microsoft convention and not an ANSI standard. If you use just the regular DateTime data type in your SS column definitions, PB will have no issues with that as it meets ANSI standards.

Note: PB only uses ANSI standards for its result set processing against any DBMS.

HTH

Regards ... Chris

[Former Member · Accepted solution]

Hi Chris,

Thanks for your Answer. What is SS column definitions? Since this application needs to be able to run against both Sql Server and Sybase (not at the same time), The PB definition stays datetime(also, there is no datetime2 but I don't think you meant that). Are you suggesting keeping the table structure in SQL as datetime?

Shai

[Former Member · Accepted solution]

Your post stated that your error was with DateTime2. That is a valid SS data type - but, just not ANSI standard.

[Former Member · Accepted solution]

I am getting this Error trying to retrieve datetime value from a column defined as datetime2.  Working against this data type has different results. I am successfully Updating a datawindow in SS using a column that is defined as datetime2. But trying to do a getitemdatetime Fails.

[Former Member · Accepted solution]

If your issue is because your using an old ASE 15 based DataWindow, then ....

1) Try building a new DW object against your DT2 based table.

2) If the new DW works on the DT2 column - then you'll probably have to rebuild all your DW objects from the ASE 15 days that use Datetime columns in order for them to realign their internal buffer handling of the SS DT2 column data.

[Former Member · Accepted solution]

Rebuilding the DW doesn't work.

[Former Member · Accepted solution]

Did a new DW work OK?

[Former Member · Accepted solution]

No. I built a complete new DW and I get the same Error as above.

[Former Member · Accepted solution]

That is what I thought as DT2 is not ANSI.

You will need to modify your SS database schema and change all the columns that are defined as DateTime2 to be DateTime. That will then make SS behave like ASE when it comes to properly handling the Date/Time data type. That should then allow the DataWindows to process the SS result set in standard ANSI fashion (like it was for ASE).

HTH

[ricardojasso · Accepted solution]

Chris,

I don't think Shai needs to do such a drastic change. I performed a quick test adding a datetime2 column to an existing SS database and had no problem adding it to an existing datawindow and performing retrieves and updates on it. Of course, I used the corresponding database interface for SQL Server. I didn't test for the full range of values of the datetime2 datatype and maybe there will be some issues, as you mention that PB only handles standard ANSI datatypes, but I think the current problem is a driver problem not a ANSI standards problem.

Regards,

Ricardo

[Former Member · Accepted solution]

In ASE and SQL Server, the milliseconds portion of datetime has three decimal places which makes sense since milliseconds is thousands of a second.

In SQL Server, the Datetime2 datatype defaults to 7 decimal places and allows you to specify the number of places you want.

If you are converting an application from ASE to SQL Server, you should choose compatible datatypes.

[Former Member · Accepted solution]

We are using SNC SQL Native Client to connect to SQL. We have a connection object that would Alter the database interface according to the location of the DB.

It actually works now after changing the data type in the SQL table to datetime.

[ricardojasso · Accepted solution]

Good choice. PB doesn't handle datetime2 columns well as Chris pointed out.

Still, when doing a test (PB 12.5) I didn't got the error you mention in your original post "Error: Invalid DataWindow row/column specified at line..." after issuing a GetItemDateTime. I just got erratic behavior from the datawindow when placing the cursor on the field but not the error you mention. Maybe it is a bug in PB 12.6.

What puzzles me is the use of the datetime2 datatype in SS when the original datatype in Sybase was datetime...

[Former Member · Accepted solution]

That is Excellent news Shai!  

[Former Member · Accepted solution]

Hi Ricardo;

  The problem (limitation) exists in all the v12.x PB versions. It could manifest itself a little different in each PB application but typically, the issue will crop up in one of DW methods - quite often after your Retrieve "seems" OK.

  My best recommendation for any PB developer is to stay within standard ANSI conventions in order to be portable. Of course, each DBMS vendor wants you to use their specialized features so that they can "lock you" into their software or make it extremely difficult to migrate away from.

Regards ... Chris

[Former Member · Accepted solution]

The SQL tables were pre-created by the SQL DBA with (as I am told) the recommended datetime datatype in SQL which is datetime2. This still works in some applications we converted.

[Former Member · Accepted solution]

Chris, Thanks again for your help.

[Former Member]

What version of PowerBuilder and what database interface are you using?

[Former Member]

Hi Roland,

PB 12.6.3506. Not sure what is database interface. This is a client Server application and I am testing through the Powerbuilder IDE.

Shai

[ricardojasso]

It's important to know what database interface you are using. If you try to connect to a SQL Server database using a Sybase database interface (i.e. SYC Sybase ASE) you will probably get connection errors. You should connect to a SQL Server database using the proper interface (i.e. SNC SQL Native Client).

Since you intend the application to connect to either of both databases then the application must provide a way to change the database driver in the logon window.