1. Gerry Whitmarsh
  2. PowerBuilder
  3. Wednesday, 4 December 2019 13:21 PM UTC

Hi, Gurus

I have this weird problem. I have a datawindow where I cannot open the data source. I have discovered the culprit but I don’t know why. In the SQL field list have a group_concat

SELECT …..,

group_concat(maillistdesc.descr separator ', ')  mailList

FROM   clients
                . . .

                LEFT OUTER JOIN maillist ON clients.client_id = maillist.client_id
                LEFT OUTER JOIN maillistdesc ON maillistdesc.list = maillist.list
WHERE clients.client_id = :id

At run-time, all works fine and I can manipulate the fields, but If I attempt to open the data source Powerbuilder just hangs and I have to quit the Powerbuilder process in the Task Manager. In order to modify the SQL I have to edit the dw source, and either modify the source there (simple change), or delete the group_concat, then I can open the data source in the regular way. Afterwards replace the group_concat.

Any ideas?

Windows 10, PB 2019 Build 2082

Thanks,
Gerry.

Gerry Whitmarsh Accepted Answer Pending Moderation
  1. Wednesday, 4 December 2019 14:23 PM UTC
  2. PowerBuilder
  3. # 1

Hi,Miguel

Thanks for the reply. I have tried what you suggest, but no joy. Luckily I have made the modifications I need, and I don't need to modify this report very often, so the edit source-delete function-edit normally-edit source-restore function-replace column approach will have to do.

 

Comment
  1. Miguel Leeuwe
  2. Wednesday, 4 December 2019 14:53 PM UTC
Sorry about that. So how were these datawindows created? Does pb do accept the syntax when creating the dw for the first time?
  1. Helpful
  1. Gerry Whitmarsh
  2. Wednesday, 4 December 2019 15:03 PM UTC
No need to be sorry, it's not your fault:-) I created a new dw with just a few columns, one of which being the group_concat and still a problem. I also had a problem when trying some C# stuff, and Appeon replied that MySQL was not supported and recommended turning to Postgresql. Duh. I guess it is just a MySQL problem and I shall have to wait until it is supported.

Thanks anyway for your input.

Gerry.
  1. Helpful
  1. Miguel Leeuwe
  2. Wednesday, 4 December 2019 19:36 PM UTC
Yes, I agree, we'll have to wait. One of powerbuilders strong points has always been that it supports many many databases, even though it might have to be using ODBC. Now ... it's being reduced to 3 databases with the C# stuff and powerserver. Oracle, MS SQLServer, PostgreSQL and Sql anywhere for odbc. We'll wait.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 4 December 2019 14:12 PM UTC
  2. PowerBuilder
  3. # 2

I guess you might be able to add the aggregate functions like group_concat() to the pbodb170.ini file by adding an entry like this:

 

[MySQL]
PBSyntax='MYSQL_SYNTAX'
PBDateTime='MYSQL_DATETIME'

;This following one is my "wild" guess, but haven't tried it
PBFunctions='MYSQL_FUNCTIONS'

[MYSQL_SYNTAX]
GetIdentity='Select @@identity'

[MYSQL_DATETIME]
PBDateFmt=' \'yyyy-mm-dd\''
PBTimeFmt=' \'hh:mm:ss\''
PBDateTimeFmt=' \'yyyy-mm-dd hh:mm:ss\''

 

 

There should be a way to indicate which aggregate functions, but they are specific for every database, so I think Appeon would have to help you out on this one.

If it works then you'd have to distribute your PBODB170.ini in the folder where your executable is, when deploying.

 

HIH

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.