• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Powerbuilder 2017/R3 SQL-Server NVARCHAR(max) cut after 32766 characters

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

Resolved Powerbuilder 2017/R3 SQL-Server NVARCHAR(max) cut after 32766 characters

  1. Issue
0
Votes
Undo
  1. Andreas Steinhauser
    Andreas Steinhauser
  2. PowerBuilder
  3. Tuesday, 22 October 2019 08:10 AM UTC

Hi,

currently we are using Powerbuilder 2017/R3 Build 1858 in connection with SQL-Server (2005 - 2017)! We defined a field with NVARCHAR(MAX) in a table. We try to select this field.

SQLNCLI9/10/11:

Regarding parameter PBMaxTextSize: field NVARCHAR(MAX) will be selected  correclty if we set this parameter for value 1000000.  But this client has been DEPRECATED since SQL-Server 2012!

SQLOLEDB/MSOLEDBSQL (direct connection) and ADO.Net (Namespace: System.Data.OleDB)

Parameter PBMaxTextSize does not exist and therefore the field will be cut after 32766 figures (not only in DW but also in Powerbuilder in general).

We tested this function with a select to this field in Powerbuilder under DATABSAE / ISQL Session (Select Field From Table). In ADO.Net even with error message:

           

When we tested the same Connect with C# (in Visual Studio), the field was selected correctly.

DBPARM (ConnectString):

SQLCA.DBParm = "PROVIDER='MSOLEDBSQL',DATASOURCE='ServerName','CommitOnDisconnect='No',PROVIDERSTRING='Database=Datenbase;App=MyApplication;DataTypeCompatibility=80'"

 The parameter: DBTextLimit=xxx – will be ignored!

Does a parameter exist for Powerbuilder which will select the data fields correctly (maybe in Powerbuilder 2019)?

 

We also have tested in ADO.Net the Namespace: System.Data.Sqlclient. Here, the NVARCHAR(MAX) fields were selected correctly (like in SQLNCLI9/10/11), but we can not set the ApplicationName (the parameter will be irgnored --> App=/ApplicationName=/Application Name=/Application=/... with apostrophe / without apostrophe).

However, this parameter has to be set for our application.

Kindly support us in this matter.

Thanks,
Andreas

  1. ADO.NET
  2. OLEDB
  3. PowerBuilder (Appeon)
  4. SQL Server
How to fire "debug project" in PB 12 and 2019?
Bug 1785 - Differences between Native & GS PDF
Responses (2)
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 22 October 2019 10:37 AM UTC
  2. PowerBuilder
  3. # 1
  • PowerBuilder >>> PB 2017 R3, #1858
  • Database >>>  MS SQL 2012
  • Table Column >>> description NVARCHAR(max) NULL
  • SQLCA >>>
    • DBMS = "ADO"
    • DBParm = "
      Namespace= 'System.Data.SqlClient',
      DataSource='ServerName',
      Database='DatabaseName',
      PROVIDERSTRING='. . .;App=MyApp',
      . . ."
  • Embedded SQL >>>
    • SELECT Description, APP_NAME( )
      INTO :ls_description, :ls_app_name
      FROM TestTable
      WHERE Test_ID = . . .

I receive:

  • ls_description = ". . ."   <== Full text of 290'000 chars
  • ls_app_name = "MyApp"

 

Differences I notice:

  1. Driver = System.Data.SqlClient
  2. Database defined outside PROVIDERSTRING
  3. No DataTypeCompatibility
  4. Embedded SQL retrieves both large text value and APP_NAME successfully

HTH /Michael

Comment
There are no comments made yet.
Andreas Steinhauser
Andreas Steinhauser Accepted Answer Pending Moderation
  1. Tuesday, 22 October 2019 14:16 PM UTC
  2. PowerBuilder
  3. # 2

Hi Michael,

I have tested the connection parameter APP and it worked fine (only the blanks in the application name were cut=.

eg:

App=My Application xxx --> APP_NAME() = MyApplicationxxx

My issue is solved. Thanks a lot!

Andreas

Comment
Michael Kramer
  1. Michael Kramer
  2. Tuesday, 22 October 2019 14:35 PM UTC
Happy to help! /Michael
  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.