SQL Server errors after PB 2017 R2 migration

Resolved SQL Server errors after PB 2017 R2 migration

Issue

Votes

Undo

Don Olliver
PowerBuilder
Wednesday, 28 February 2018 18:08 PM UTC

I am testing an evaluation copy of PB 2017 R2. The migration tool reported no errors when upgrading our PB 12.5.2 app. However, I am seeing random SQL Server errors during runtime that do not occur in PB 12.5.2. I compiled using PowerGen 9.5, and am using ODBC Driver 11 for SQL Server.

Some examples:

'Invalid character value for cast specification'
'The data types text and varchar are incompatible in the equal to operator'
'Datetime field overflow'
'ep_buildschedule is not a recognized built-in function name'
'The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Article_Line"'

All of these are associated with a datawindow or datastore. Additionally, we ported these changes from pbodb125.ini to pbodb170.ini:

[Microsoft SQL Server]
PBMaxBlobSize='1073741824'
PBMaxTextSize='1048576'
IdentifierCase=4
PBSupportBindSelect='NO'
PBSupportBindUpdate='NO'

[SQL Server]
PBSystemOwner='sys'
PBMaxBlobSize='1073741824'
PBMaxTextSize='1048576'
PBPrimaryKeyName='YES'
Block=1
IdentifierCase=4
PBDefaultValues='USER,SYSTEM_USER,USER_ID(),HOST_ID(),HOST_NAME(),DB_ID(),DB_NAME(),CURRENT_TIMESTAMP,NULL'
PBDefaultCreate='YES'
PBDefaultAlter='NO'
PBDefaultExpressions='YES'
PBSupportBindSelect='NO'
PBSupportBindUpdate='NO'

[MS_SQLSERVER_SYNTAX]
GetCatalogIdentity='SELECT c.name FROM sys.syscolumns c JOIN sys.identity_columns i on c.id = i.object_id JOIN sys.objects o on c.id = o.object_id JOIN sys.schemas sc on o.schema_id = sc.schema_id WHERE o.object_id = OBJECT_ID(''&TableOwner.&TableName'') and o.type != 'P' and i.increment_value > 0 and (c.status &128) = 128'

Before I start to research individual errors, are there any settings that must be changed or added to make PB 2017 datawindows compatible with SQL Server?

Thanks,

Don Olliver

ASE
Data

Responses (3)

Govinda Lopez @Appeon Accepted Answer Pending Moderation

Saturday, 3 March 2018 01:34 AM UTC
PowerBuilder
# 1

Hi Don,

PowerBuilder currently supports the following SQL Server versions: SQL Server 2016, 2014, 2012, and 2008 R2. So, if your database is in this list, then you can feel safer on it's usage with PowerBuilder.

You would also need to consider the currently supported datatypes: https://www.appeon.com/support/documents/appeon_online_help/pb2017r2/connecting_to_your_database/ch10s02.html

Here are some suggestions for the errors you are getting:

'Invalid character value for cast specification'

Add CallEscape='No' to your SQLCA.DBParm.
Make sure your SQLCA.DBParm has the correct syntax (https://www.appeon.com/support/documents/appeon_online_help/pb2017r2/connecting_to_your_database/ch14s03.html#d0e8273)

'The data types TEXT and VARCHAR are incompatible in the equal to operator'

Try changing the query by using 'like' instead of '=', Just don't add the '%' before or after the value (ie, SELECT column_a FROM tableA WHERE column_b = 'Test' to something like this: SELECT column_a FROM tableA WHERE column_b like 'Test')

'Datetime field overflow'

This could be cause by the datatime2 datatype in SQL Server. Basically you would need to trim off the fractional seconds. (https://www.appeon.com/support/documents/appeon_online_help/pb2017r2/connecting_to_your_database/ch10s07.html#XREF_42939_Support_for_new)
This error occurs when you try to write a date that is outside the valid range of dates for the Microsoft SQL Server datetime field. The datetime field can store dates from January 1, 1753 through December 31, 9999. To correct the error, make sure you are using a date within that range. Hence the trimminig off the fractional seconds. (http://support.sas.com/kb/37/066.html)

'ep_buildschedule is not a recognized built-in function name'

Try adding the [dbo] prefix to it (ie, SELECT dbo.ep_buildschedule('TEST'))
You can also add this to your PBODB170.ini
- PBSystemOwner='sys,dbo' (https://www.appeon.com/support/documents/appeon_online_help/pb2017r2/connecting_to_your_database/apas01.html#XREF_19024_APPENDIX_Adding)

'The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Article_Line"'

This could be caused by data integrity in your database. You can try executing: sp_help 'dbo.TableName' on your SQL Server Management Studio to see where the problem is.

You can find more info about your PBODB170.ini file and general database connections here:

I hope this helps.

Regards,

Comment

Don Olliver
Friday, 23 March 2018 19:40 PM UTC

Thank you Govinda. All of these issues were resolved by adding DISABLEBIND=1 to DBPARM.

Don

Helpful 0

There are no comments made yet.

Kim Berghall Accepted Answer Pending Moderation

Wednesday, 28 February 2018 20:48 PM UTC
PowerBuilder
# 2

I think it is the same we see from SQL Server, I reported it as a bug, but Appeon claims that it is normal.

All SQL is verified by sending it to the current

active DB Profile connection DBMS with a "NO EXECUTE" request added to it. The NO EXEC basically instructs the DBMS's DML Compiler to only parse the DML statement and then return any error code and error message from the parsing

operation to the PB IDE for display to the developer.

   So if you are not connecting to the correct DB Profile, DBMS, wrong database or even the wrong instance of a DB that does not reflect the current schema definition - you will get these errors. This is that way PB has operated since

v2.0.To suppress these messages if you are compiling against a non-current

schema, just set the "Disable DB Connection When Compiling" in the IDE's System Options dialogue's General tab.

Comment from Kim: I think it still is a bug, it definitely changed with 2017 R2. But not a high priority for us...

Comment

Chris Pollach @Appeon
Wednesday, 28 February 2018 22:40 PM UTC

Hi Kim;

This might be a different problem as Don seemed to indicate that this was happening at run time (I believe) and not at compile time.

Regards ... Chris

Helpful 0

Don Olliver
Friday, 23 March 2018 19:35 PM UTC

Thanks Chris. Yes, this is occurring at run time. We use PowerGen for our compiles.

Don

Helpful 0

There are no comments made yet.

Chris Pollach @Appeon Accepted Answer Pending Moderation

Wednesday, 28 February 2018 19:25 PM UTC
PowerBuilder
# 3

Hi Don;

Could you test and see if adding the TRACE option to your PB 12.5.x and PB 2017R2 PB App DB connection, exposes any SQL processing weirdness?

For example ...

SQLCA.DBMS = "ODB" - Change to -
SQLCA.DBMS = "TRACE ODB"

Then run both apps through an identical transaction against the same SS instance where you are seeing a problem in your PB 2017 App version and see how the SQL processing differs in each Apps SQL log file - created by using the TRACE option.

Regards ... Chris

Comment

Don Olliver
Friday, 23 March 2018 19:39 PM UTC

Chris,

Tracing the SQL allowed me to see how the SQL was bound. I resolved all of the issues by adding DISABLEBIND=1 to DBPARM.

Thanks,

Don

Helpful 0

There are no comments made yet.

Page :
1

There are no replies made for this question yet.
However, you are not allowed to reply to this question.

Please login to post a reply

You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »

Forgot Password?

Resolved SQL Server errors after PB 2017 R2 migration

Find Questions by Tag