1. Jon Stoller
  2. PowerBuilder
  3. Wednesday, 6 December 2017 23:18 PM

I am having a problem where I am getting errors when PowerBuilder tries to update the database and there is a "truncation" issue with one of the columns in the table it is updating.   

I am in the process of upgrading from PowerBuilder v7 to version 2017.  I am also updating the database.  I use the Microsoft SQL Server database.  I was using version 2008R2 with PowerBuilder v7.  I am using MS SQL Server 2016 with PowerBuilder 2017.  I connect using OLE Microsoft OLE DB

Database The following is an example of the problem: 

************************************
Database information
  employee table:
  columns: 
    employee_id    char(1)
    employee_name  char(25)

PowerBuilder script: 
  string  ls_emp_id, ls_emp_name
  long   ll_return_code

  ls_emp_id = "WJL"
  ls_emp_name = "Washington Jefferson Lincoln"

  dw_employee.object.employee_id[1] = ls_emp_id
  dw_employee.object.employee_name[1] = ls_emp_name

  ll_return_code = dw_employee.Update()

Note - the employee name column is char(25) but the ls_emp_name is 30 characters ("Washington Jefferson Lincoln")
The update fails in PowerBuilder 2017 with MS SQL Server database 2016
  The ll_return_code is "-1"
  I get a "DataWindow Error" popup with a message that says:
    SQLSTATE = 22001
    Microsoft OLE DB Provider for SQL Server
    String or binary data would be truncated.
    No changes made to database.
 
**************************************

The same PowerBuilder script works in PowerBuiler v7 with the DB v2008R2.  The update returns "1"
The data gets truncated and the database gets updated without an error. 
The employee name is updated with 25 characters - as "Washington Jefferson Linc"

I realize I can "code" my way around this but then I have to find and deal with every possible occurrence of the problem. 

Does anybody know of something I can do so the data automatically gets truncated and PBv2017 performs the update as it did in version 7.  

For example, is there something I can put in the Connection String when I connect to the database that will eliminate the problem. 

Note:  Previously I was having a problem because char and varchar data columns were being padded on the right with spaces. 
I added the following parameter to my connection string:      PBTrimCharColumns='Yes'
Now it is not padding the columns with spaces - which is good. 

Is there something similar that I can do so I don't get the truncation errors. 
*************************************

René Ullrich Accepted Answer Pending Moderation
  1. Thursday, 7 December 2017 06:23 AM
  2. PowerBuilder
  3. # 1
Comment
I'm also facing the same problem.. where i need to set the ansi_warnings ?? i have tried in database level.. it is not working..



Regards,

Backya
  1. backya arun
  2. Saturday, 21 November 2020 11:56 AM
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.