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

Resolved Trailing spaces from MSSQL database

  1. Advice
0
Votes
Undo
  1. Craig Meinen
    Craig Meinen
  2. PowerBuilder
  3. Thursday, 4 May 2023 14:31 PM UTC

Hi All.

I'm using PB2022 to develop an application that connects to a SQL Server database via MSOLEDBSQL.  The syntax is shown below.

// Profile Database (MSSQL)
SQLCA.DBMS = "MSOLEDBSQL SQL Server"
SQLCA.LogPass = <***********>
SQLCA.ServerName = "NNN.NN.NN.NNN"
SQLCA.LogId = "sa"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Database='XXX',TrimSpaces=1"

 

The problem is that his doesn't seem to be working for me.  The character fields in my application still contain trailing blanks.  In places where I can, I need to add the Trim function to correct this issue, but I cannot use that in all cases, so I need to find out what I can do to correct this.

Any assistance would be much appreciated.

Thank you.

Attachments (1)
DB connection.png DB connection.png
Authentication REST
Upgrade workspace to PB 2022 from 2021
Responses (4)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 9 May 2023 18:04 PM UTC
  2. PowerBuilder
  3. # 1

Hi, Craig -

We take a more aggressive approach to this issue in our commercial app by preventing both leading and trailing spaces from being entered. The app is PFC-based, so in the base DataWindow control (u_dw) we placed code in the SQLPreview event to scan the syntax of every SQL Insert and Update statement and alter the syntax if needed to remove leading/trailing spaces before the values are placed in the database.

When we implemented this change, we did have a few instances of leading/trailing spaces in columns, so we wrote some one-time use stored procedures to look for and eliminate the occurrences in the affected database tables. A little painful at the time, but worry-free ever since.

Best regards, John

Comment
There are no comments made yet.
Craig Meinen
Craig Meinen Accepted Answer Pending Moderation
  1. Tuesday, 9 May 2023 16:28 PM UTC
  2. PowerBuilder
  3. # 2

Adding the parameter statement (SQLCA.DBParm="TrimSpaces=1") to the app_manager seems to work for us.  I'm not sure that this is the intended method, but it's the only one that we got to work for us so far.

Thanks for any and all ideas.

Craig

Comment
mike S
  1. mike S
  2. Tuesday, 9 May 2023 18:11 PM UTC
that is what trimspaces is for
  1. Helpful
There are no comments made yet.
Craig Meinen
Craig Meinen Accepted Answer Pending Moderation
  1. Monday, 8 May 2023 14:18 PM UTC
  2. PowerBuilder
  3. # 3

HI All.

To add to my initial post, I have been informed that our databases are using ANSI_NULLS at the table level.  ANSI_PADDING is disabled.  I don't know if this provides any clues beyond what has already been discussed.  So far, I can seem to do nothing to override these settings in the application.

 

Thank you.

Craig

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 4 May 2023 17:30 PM UTC
  2. PowerBuilder
  3. # 4

Hi,

Could this be of any help? https://community.appeon.com/index.php/qna/q-a/datawindow-edit-issue-pb-padding-string-fields-causing-user-insert-issue?limitstart=0#reply-31423

Comment
Craig Meinen
  1. Craig Meinen
  2. Thursday, 4 May 2023 17:41 PM UTC
Hi Miguel.



Thank you for your reply, I did review that thread before I submitted mine, and I tried switching my connection profile to ODBC so see if that made any difference, but it didn't seem to (unless there is something else that I am not doing that I should be). I should add that I didn't have the setting on in the IDE profile before yesterday, but I have been using it in the application INI file all along. It just doesn't seem to work with MSSQL for me.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 4 May 2023 17:55 PM UTC
hmmm, what about Ansi padding that can be set on the DB itself? https://www.appeon.com/standardsupport/search/view?id=1259

  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.