Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved SQL Server errors after PB 2017 R2 migration

  1. Issue
0
Votes
Undo
  1. Don Olliver
    Don Olliver
  2. PowerBuilder
  3. 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

  1. ASE
  2. Data
Windows API
Rowscopy issue in runtime only
Responses (3)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 28 February 2018 19:25 PM UTC
  2. PowerBuilder
  3. # 1

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
  1. Don Olliver
  2. 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

  1. Helpful
There are no comments made yet.
Kim Berghall
Kim Berghall Accepted Answer Pending Moderation
  1. Wednesday, 28 February 2018 20:48 PM UTC
  2. PowerBuilder
  3. # 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
  1. Chris Pollach @Appeon
  2. 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

  1. Helpful
Don Olliver
  1. Don Olliver
  2. Friday, 23 March 2018 19:35 PM UTC
Thanks Chris. Yes, this is occurring at run time. We use PowerGen for our compiles.



Don

  1. Helpful
There are no comments made yet.
Govinda Lopez @Appeon
Govinda Lopez @Appeon Accepted Answer Pending Moderation
  1. Saturday, 3 March 2018 01:34 AM UTC
  2. PowerBuilder
  3. # 3
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.
 
 
Here are some suggestions for the errors you are getting:
 
'Invalid character value for cast specification'
'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'
'ep_buildschedule is not a recognized built-in function name'
'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
  1. Don Olliver
  2. Friday, 23 March 2018 19:40 PM UTC
Thank you Govinda. All of these issues were resolved by adding DISABLEBIND=1 to DBPARM.



Don

  1. Helpful
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.