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

Resolved How to turn off the database warning to allow automatically truncate data when column mismatch

  1. How-to
0
Votes
Undo
  1. Song Tom
    Song Tom
  2. PowerBuilder
  3. Friday, 13 December 2019 06:40 AM UTC

Dear Appeon:

 

    recently we encountered a issue of PowerBuilder when datawindow updating,  in some data column length mismatch case, when data length inserted is large than that of the database column, SQLSERVER always automatically truncate the string data without throw exception and terminate the SQL process, I remember the same case also get no issue in PB previous version like PB8, PB12, but for PB2017, this case would lead to issue, as these issue is hard to detected when there are lot of datawindows when upgrading, is there any database parameter can turn off these type of database waring (our database is sqlserver2017)

SAVEAS datawindow in %username%
Scroll to last row grid datawindow - query mode
Responses (4)
David Peace (Powersoft)
David Peace (Powersoft) Accepted Answer Pending Moderation
  1. Monday, 23 December 2019 11:00 AM UTC
  2. PowerBuilder
  3. # 1

SQL Server truncates long strings by default and there is no way you can turn this off. What you should do is set the correct sizes in your DW column specifications and on the edit screen to prevent too long a string being entered. That way the DW will error before sending the data to the database.

However, if you concatinate strings within inline SQL then you should always check the length before the insert/update.

Sorry it's not what you wanted to hear, but is an anoying feature of SQL Server.

Regards

David

Comment
Load more comments
David Peace (Powersoft)
  1. David Peace (Powersoft)
  2. Monday, 23 December 2019 17:07 PM UTC
I cannot comment on PB8 with SQL Server, never used it back then. I just know that it is a pain in the bum now :)
  1. Helpful
Michael Kramer
  1. Michael Kramer
  2. Tuesday, 24 December 2019 03:14 AM UTC
I have code like this firing as a "logon" script when app connects to a MSSQL database >>

EXECUTE IMMEDIATE 'SET ANSI_WARNINGS ON' USING this;

Actually I have an array of options that are fired immediately after CONNECT to define the "default connection properties". Alternatively you could set such options as default on your MSSQL database server.
  1. Helpful
Song Tom
  1. Song Tom
  2. Tuesday, 24 December 2019 13:18 PM UTC
Dear All:



Thanks a lot, and Merry Christmas and happy new year :),
  1. Helpful
There are no comments made yet.
Song Tom
Song Tom Accepted Answer Pending Moderation
  1. Monday, 23 December 2019 09:50 AM UTC
  2. PowerBuilder
  3. # 2
Sorry all:
 
Perhaps a bit misleading, when I create table Tbl_testabc with column code and name with column type char(10),
 
when I add two rows in related datawindow  and update, there is an error prompt
 
SQLSTATE = 22001
Microsoft SQL Server Native Client 11.0
String or binary data would be truncated.
No changes made to database.
INSERT INTO Tbl_testabc ( code, name ) VALUES ( '01', '12334324554655476576876987970eweqr' )
 
but on database side, there is just a warning, no error message, data can be successfually inserted, with column name be truncated
Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Monday, 23 December 2019 16:57 PM UTC
Hi Everyone;

FWIW: I would never put an App into production that allowed any DBMS to "truncate" the data regardless of its data type. I would always want the PB App to know about this as a DB Error and then inform the App user. You know, the actual business owner of the datum. and the one charged with its accuracy. That is why its super important to trap the DBError event on the DC/DS control and take action. ;-)

Just my $0.02

Regards ... Chris
  1. Helpful
Song Tom
  1. Song Tom
  2. Tuesday, 24 December 2019 02:15 AM UTC
Yes, developers may always understand that, but for dozens of users using old application, they may confuse why PowerBuilder divorce with MS on this behavior during migration after keep the same step on this for many years. worse of all, many legacy migrated old application will prompt error but we cannot predict it :), I think PowerBuilder should have a switch on that to turn on and off as customization rather than roughly turn it on, I think if I am user, I may regarded it as a bug
  1. Helpful
There are no comments made yet.
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Sunday, 15 December 2019 03:28 AM UTC
  2. PowerBuilder
  3. # 3

Do you mean the error message reading something like "String truncated ..."?

That message typically comes from DataWindow objects where DW buffer uses a different string size that the database table at runtime. Similar issue for other datatypes.

Now, you can have PowerBuilder check for database changes for every single DataWindow object to make sure each DataWindow matches the current runtime database doing this:

SQLCA.DBParm = "...,StaticBind=0"
CONNECT USING SQLCA;

Default is StaticBind=1 -- read: Trust that runtime database matches the database used by the developers at DataWindow design time.

When you disable static bind, the PB runtime will PREPARE + DESCRIBE the SELECT statement of every DataWindow before starting to use it at runtime. The PB runtime treats the SELECT of each DW object as DYNAMIC SQL Format 4. NOTE: This WILL make you app slower!

HTH /Michael

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 13 December 2019 17:21 PM UTC
  2. PowerBuilder
  3. # 4

Hi Song;

  Can you tell us ...

1) Is the data being truncated when the length is > 32K?

2) What DB Client are you using to connect to SS?

3) What version of SS are you using?

Regards ... Chris

 

Comment
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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.