1. P Laceholder
  2. PowerBuilder
  3. Friday, 4 March 2022 10:24 AM UTC

Greetings community,

 

In the past few months, our company migrated multiple applications from Powerbuilder 9 to Powerbuilder 2019.

We did encounter something interesting: for varchar(max) and text column types, on our MSSQL database, all datawindow fields of those types are blank.

 

Powerbuilder 9 treats text/varchar(max) columns as char(4099) inside of the datawindow syntax.

While doing a series of tests we noticed that altering the datawindow column to char(32000) actually displays the content.

 

My two questions are the following:

  1. Is the fact that our text field is blank a bug? Shouldn't it display the content anyway?
  2. We have hundreds of datawindows that would need changes, how could we achieve this efficiently?

 

I will also add that we tried this with both "SNC SQL Native Client" and "MSOLEDBSQL" connection driver.

 

I thank you in advance.

Regards.

mike S Accepted Answer Pending Moderation
  1. Monday, 7 March 2022 14:20 PM UTC
  2. PowerBuilder
  3. # 1

what happens when you put text into the column that is larger than 32K?  does the same problem occur?

If you do change the pbmaxtextsize value to 1GB, then the char size in the datawindow displays as char(999999). 

Perhaps the real problem is that you have entered more than 4099 in the column?  did it display when smaller text was entered?

Setting the field size limit to 4099 or 32K and sticking to it may be the real fix here.  If you need larger than that, use select/update blob syntax.

 

I suggest reporting these results to appeon.  At the least this should be in the documentation whether its a driver issue or a datawindow issue.   This may simply be how the driver works and developers have to work around it.  Appeon has been good at adding these tidbits to the documentation.

 

 

 

Comment
  1. P Laceholder
  2. Monday, 7 March 2022 14:41 PM UTC
>what happens when you put text into the column that is larger than 32K?

Texts won't be larger than that for sure



>Perhaps the real problem is that you have entered more than 4099 in the column? did it display when smaller text was entered?

The text did not display, even if 10 characters long and char(4099); it did get saved properly on the database tho.
  1. Helpful
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Sunday, 6 March 2022 17:45 PM UTC
  2. PowerBuilder
  3. # 2

Have you tried setting staticbind in your database connection?

https://docs.appeon.com/pb2019/connection_reference/ch01s01.html#StaticBind

 

It will slow down the dw retreives, but it will allow you to use existing datawindows without changing the column size.  I have no idea whether that will fix your 4099 vs 32k issue for varchar(max).   That is just assuming that the problem really is the column size.  I never have problems with retrieving too large a value, but i do when sending updates/inserts/deletes.

 

also, if your varchar(max) is still larger than 32K then you may have to retrieve it down differently.  PBMaxTextSizedatabase parameter may come into play here depending on your database driver.  The default is 32K, but you can extend to 1GB.  only available for certain database drivers (MSOLEDBSQL & SNC)

Database parameters and supported database interfaces - - Connection Reference (appeon.com)

 

Comment
  1. P Laceholder
  2. Monday, 7 March 2022 07:38 AM UTC
This is very interesting, I will look into it and do some tests, thank you very much!
  1. Helpful
  1. mike S
  2. Monday, 7 March 2022 14:04 PM UTC
also, i vaguely remember an issue with sql server native client and text/varchar(max). this was in PB 11 or 9, when we changed from the old mss driver to SNC. the problem did not occur with the OLEDB sql driver (the now old one that ships with windows). since then, all of our datawindows have been changed for various reasons, and as a result the size updated to 32k - so we don't have that problem anymore with SNC or the new MSSQLOLEDB.



I think it may have been the same problem you are having now. if oledb works, then it may be the best option until you have time to resize the columns.
  1. Helpful 1
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 4 March 2022 13:12 PM UTC
  2. PowerBuilder
  3. # 3

Hi;

  This could be related to a long time bug (glitch) that was identified in the PowerSoft days, re-identified in the Sybase days, etc but never fixed. This glitch is that the PB IDE never updates the DWO source during a migration. Thus when your running the migrated App in your case, the DWO's still think they are running as PB 9 not 2019.

   To see if this is affecting your App. Try editing the "source" of one of your problem DWO's. You'll pry see that the 1st line states "release 9;". If migration did its job properly, the 1st line should be "release 19;". Since you have the DWO source open, change the "9" to a "19", save the DWO & then run your App to see that fixes the issue. It should because newer DWO code ignores the String(NN) column length and always dynamically expands its primary buffer to 32K per column if needed. HTH

 PS: note that after changing the "release" number & saving the DWO, reopen the DWO source right afterwards. Notice how much more robust the source code is.  ;-)

Regards ... Chris

Comment
  1. Chris Pollach @Appeon
  2. Friday, 4 March 2022 18:00 PM UTC
I did too Miguel to PowerSoft & Sybase - numerous times each. So far like my kids, they just ignored me - LOL!
  1. Helpful 3
  1. Miguel Leeuwe
  2. Friday, 4 March 2022 22:22 PM UTC
:)
  1. Helpful
  1. mike S
  2. Sunday, 6 March 2022 17:33 PM UTC
i'm not at all convinced that changing the release number does anything at all except prevent older versions of PB from opening the datawindow. I don't like the idea of the IDE changing that number during migration
  1. Helpful
There are no comments made yet.
  1. Friday, 4 March 2022 12:09 PM UTC
  2. PowerBuilder
  3. # 4

I would export all the DW SRD files and then using VI to find and replace the CHAR(4099) to CHAR(32000).

If you don't have acces to VI it would be quite easy to write a PB program to read the SRDs and change the syntax.

Comment
  1. Chris Pollach @Appeon
  2. Friday, 4 March 2022 15:31 PM UTC
FYI: You can use the LibraryDirectory, LibraryExport and LibraryImport commands to form a little PB App utility to correct all your DWO's source for that column length's current restriction.
  1. Helpful 2
  1. Miguel Leeuwe
  2. Friday, 4 March 2022 17:37 PM UTC
Another thing you could also do, is to export all of the datawindows and even use Notepad++ "find (and replace) in Files" to do the job. After that, import them back into their corresponding libraries.

Another alternative is to use PBLPeeper if I'm not mistaken. There should be a link somewhere on this forum if you search on pblpeeper.

If not let me know, I have it somewhere.

regards.
  1. Helpful 2
  1. Matt Balent
  2. Monday, 7 March 2022 20:45 PM UTC
Take a look at Roland Smith's PBSearch tool on Topwiz Programming. It has a search / replace functionality which could help you here.



  1. Helpful 1
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.