Questions? Feedback?powered byOlark live chat software
Bug 2465

PB IDE crushed (Modify SQL select syntax) 15 March, 2019

Alessandro Malaguti
09 March, 2019
Product: PowerBuilder Category: IDE
Version: 2019 Beta Publishing: Public
Status: Scheduling Priority: P3
Classification: Sybase (legacy) bug Resolution:
Ken Guo 15 March, 2019
Hi Alberto,

Thanks for reporting this problem! 
We reproduced it on our end and will do further research to figure it out. We will keep you posted about the progress.
Currently, I suggest you comment out the first bracket to workaround this issue or you can remove all annotations. 

BTW, Same issue in the PB 12.6

Regards,
Ken
Alessandro Malaguti 14 March, 2019
Hi Ken,

Ok, this is my connect info:

// Profile db_STERIGENICS270_TEST
SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
SQLCA.LogPass = <*************>
SQLCA.ServerName = "ALBERTOT"
SQLCA.LogId = "alberto"
SQLCA.Lock = "RU"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Provider='SQLNCLI11',Database='sterigenics270',ProviderString='DateTimeFormat='\''dd-mm-yyyy hh:mm:ss\'''',StripParmNames='Yes'',TrimSpaces=1',DateTimeFormat='dd-mm-yyyy hh:mm:ss'"

and instead the 'contratti_doc' table is the same of 'contratti_ufo', anyway this is the ddl (generate from MS-SQL sever Management Studio):

CREATE TABLE [dbo].[contratti_doc](
	[id_contratto_doc] [int] IDENTITY(5000,1) NOT NULL,
	[dati_contratto] [nvarchar](max) NULL,
	[x_datins] [datetime] NULL,
	[x_utente] [char](12) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I'd like to say you that dw I sent you last time produced the crash in mine IDE.

regards,
alberto trebbi
Ken Guo 14 March, 2019
Hi alberto,

I used the srd and Table Syntax you provided to try but still can’t reproduce this issue.
I’d like to know how do you connect to the database? Kindly please send the database connection info for me to test, for example: 
// Profile sql2017_client
SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
SQLCA.LogPass = <************>
SQLCA.ServerName = "172.16.0.19"
SQLCA.LogId = "sa"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Database='en_sql2017',Provider='SQLNCLI11'"

In addition, kindly please also send the table syntax of contratti_doc for me.


Regards,
Ken
Alessandro Malaguti 13 March, 2019
d_ufo.srd (366KB)

Hi Ken,
I attached the DW source with the issue and below find the 'create table' statement (sql-server):

CREATE TABLE [dbo].[contratti_ufo](
	[id_contratto_doc] [int] IDENTITY(5000,1) NOT NULL,
	[dati_contratto] [nvarchar](max) NULL,
	[x_datins] [datetime] NULL,
	[x_utente] [char](12) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

If you import the dw 'd_ufo.srd' in any target and open it then try to enter in modify source query and you should see IDE to crash out. 

My answers:
1. Does the issue still exist if you use ‘/* … */’ instead of ’--’?
yes, it does too
2. Can you reproduce this issue if you create a new sample DW includes ’--’?
see above and my attached

bye,
alberto trebbi
Ken Guo 13 March, 2019
Hi Alberto,

Thanks for locating to the detail code that caused this issue.
Even though I use ‘--’, I still can’t reproduce it on my side. Following are my questions:
1. Does the issue still exist if you use ‘/* … */’ instead of ’--’?
2. Can you reproduce this issue if you create a new sample DW includes ’--’?

Currently, I suggest you delete ‘--’ to workaround this issue. 


Regards,
Ken
Alessandro Malaguti 12 March, 2019
Hi Ken,
I rebuilt a new dw with same query (copy+paste from the old query) and now it's working fine because I removed a comment row (to begin with '--' character) 

the line was:
	--, (select isnull((clienti_memo.id_cliente_memo),0) from clienti_memo where  clienti_memo.id_cliente = JSON_VALUE(ctr.dati_contratto ,'$.id_cliente')  id_cliente_memo

But, the strange thing is that the same query whith fields referred of a 'view table' (always defined with JSON_VALUE) works fine also with the comment line included.

I answer your qustion:
1. Does this issue exist on other DWs? Does the issue still exist if you open PB via Run as Administrator? 
No, I have only that dw with this issue (now it's the only with JSON statment)  and Yes I always run as Adm
2. Does the issue exist if there is no JSON_VALUE function in this DW, or can you locate that it is the JSON_VALUE function that caused this issue?
No, I replaced the JASON_VALUE with the fields of the table-view and works fine but see you the above explanation.
3. Can you reproduce it if you use PB IDE to connect to ASA Demo DB first and then choose Design -> Data Source?
I found out when the issue happen (the 'comment line' seems the culprit) and maybe your request it is no longer necessary

thank you
alberto trebbi
Ken Guo 11 March, 2019
Hi Alessandro,

Thanks for reporting this issue but I can’t reproduce it on my side using neither PB 2017 R3 not PB 2019 Beta.
I also tried to use our SQL server 2016 database to create a new DataWindow includes JSON_VALUE function but still can’t reproduce it.
Following are my questions:
1. Does this issue exist on other DWs? Does the issue still exist if you open PB via Run as Administrator?
2. Does the issue exist if there is no JSON_VALUE function in this DW, or can you locate that it is the JSON_VALUE function that caused this issue?
3. Can you reproduce it if you use PB IDE to connect to ASA Demo DB first and then choose Design -> Data Source?
  a) If you can’t reproduce it, that means this issue is related to DB. In this case, can you provide a database includes only tables needed that can reproduce this issue for us?
  b) If you still can reproduce it, can you reproduce it on other machines?


Regards,
Ken
Alessandro Malaguti 09 March, 2019
d_contratti_doc.srd (475KB)

(PB 2019 and PB 2017R3)
From the inside 'EDIT datawindow' (the IDE environment, of course) if I choose 'Design' + 'Data Source...' (or directly 'Modify SQL select syntax' button from toolbar), IDE suddenly crushly without any message. 

The datawindow is built with MS-SQL  JSON statements like JSON_VALUE.

The retrive works fine.

here is a piece of the SQL source inside the datawindow:
......
retrieve="SELECT ctr.id_contratto_doc ,   
         JSON_VALUE(ctr.dati_contratto, '$.anno') ,  
         JSON_VALUE(ctr.dati_contratto,'$.magazzino'),   
         JSON_VALUE(ctr.dati_contratto ,'$.offerta_data'),   
         JSON_VALUE(ctr.dati_contratto ,'$.offerta_validita'),   
         JSON_VALUE(ctr.dati_contratto ,'$.oggetto'),   
         JSON_VALUE(ctr.dati_contratto ,'$.id_cliente'),   
         rtrim(clienti.rag_soc_10) as rag_soc_10,   
         clienti.loc_1,   
         clienti.prov_1,   
 .....
     FROM contratti_doc as ctr LEFT OUTER JOIN clie_settori ON JSON_VALUE(ctr.dati_contratto, '$.id_clie_settore') = clie_settori.id_clie_settore 
                   LEFT OUTER JOIN clienti ON JSON_VALUE(ctr.dati_contratto ,'$.id_cliente') = clienti.codice
                   LEFT OUTER JOIN gru ON JSON_VALUE(ctr.dati_contratto ,'$.gruppo') = gru.codice 
......

I attached the source of the dw

Alberto Trebbi
Remarks:
OS:
Windows 10 
Platform:
64-bit 
Database Type:
Microsoft SQL Server 
Database Version:
Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64) Jul 21 2018 07:47:45