Database Truncation Errors

1
0
-1

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. 
*************************************

Question Tags: 

Answers

Chris Pollach answered Database Truncation Errors

1
0
-1

Hi Ravi;

       René is correct ... the Native PDF feature tries to create a temporary dwPage.emf file in the application folder's directory. If you deploy your PB apps to the Program Files (X86) folder, the write permissions could certainly be the issue.

       The good news is that this oversight should be corrected in the PB2017R2 (revision #2) where the Native PDF feature will now create the dwPage.emf work file in the target folder where you are trying to build the PDF file. Obviously, the target folder should have write permissions already for the App user. The other enhancement will be the creation of a dwPageN.emf file where when the dwPage.emf already exits when threading multiple PDF requests. That means that the PBVM will create a dwPage1.emf, dwPage2.emf, etc work files as required on the fly.

      PB2017R2 is scheduled for the end of December, 2017. At the worst case, this revision release will be available in early January, 2018.

HTH

Regards ... Chris

 

bmettee@pchotshots.com's picture

Chris - wrong thread

jon_15171's picture

Thanks