RESOLVED getDataSetByQuery runs in 7.4.8 not in 2020.6

Questions and answers regarding general SQL and backend databases

RESOLVED getDataSetByQuery runs in 7.4.8 not in 2020.6

Postby joe26 » Thu Sep 03, 2020 7:31 am

Hi, having an issue with moving to 2020.6 from 7.4, while using the newest jdbc driver for postgres.

The postgresql version is 9.5.

Driver version is postgresql-42.2.9.jar.

The following query does not return a dataset in 2020.6, but does in 7.4.

Yeah. Getting to the new version, but I've dreaded the transition.

SELECT sum(pcmk_qty),sum(item_weight*pcmk_qty) FROM pms
WHERE sheet_id IN ('FFE9F52D-3BBA-4EEC-8451-F575A603EB20','006FAE1E-19F4-461A-A8D3-612034243026','01155041-EE42-4F98-845F-AB8D90096FA2','02B1C3FF-8953-41E3-B1F5-BDF38719FD3D','0353CF42-21B6-403F-8315-9BAEE8EBBBCE','0362AA86-195C-4DFC-8E9C-864CDDFA8B4B','06DEBDED-FED2-4AC0-BC2B-5D32DE46D716','078A4FFD-BA7C-4271-815E-80D9ABCA224F','09F38544-E5CD-4ECC-A6CE-C90938F63B1F','0A065A43-A624-47D3-AC52-FA3736D48024','10C21E97-8F67-4FF8-92CD-7163031D0426','15E5D1AE-0B8B-4270-AB09-54BDE3F7310D','179E3707-3617-43FA-93CB-4BA6D2CB4EEF','17D7E910-2A03-4F1E-9944-A3FB5E1195A3','1EE964CF-3D11-4AE4-8488-B72833293079','1FAF7EB4-741B-4F15-BCDE-F0888C2D5115','1FCFE925-C5E9-43B2-991B-8FFBB0AC79CD','1FE39F82-5310-4339-87F8-468F3BC5D600','207D1A9A-8F30-4B4C-8D9E-7D89F450BE94','22D71014-F0BF-40E5-8440-6D4B8B034FCD','25BA8BFA-8D06-4B21-A084-6348F8938EBB','261C0501-06A9-4BCF-9DF3-02ED9FCFFE4F','26228D33-6678-4763-926A-575AD64E18BF','2A4CDA2D-8ED3-4E28-95C2-C6425799DCCE','2C991776-BA83-4D2E-88EC-1A63908CAD1E','3C3ACA16-6CF8-40BA-BA7B-9CDCB6A4146A','2E17B672-81A8-482E-A79D-19838F42A31E','2F901AF2-1057-4C1B-9548-F519A1C91EB5','3024BD48-AA79-4A07-9118-B301F0ACA222','30ECA61B-D5B5-4C1C-8164-EBEA6C64083E','32C518A9-92B1-43A4-B789-4ADD7CFB5F26','33719925-0D9B-42B0-803E-E2F7D9BA2890','36F90C30-8A39-4023-8093-7018ED1C42DF','3DEF212A-2105-4AC7-8389-A6ECC945F5CB','4054105A-0673-4FAD-B058-7C39B28BA759','40488C2D-34BB-42E9-908E-6716E366C82A','46EF6AFB-2D74-44C4-9B96-C1C47654BA66','480EA2BF-B5B6-4879-8605-10894767BE8D','48D290BE-DBDC-40C5-BBE3-53260C407101','497FFC49-34CE-4E0B-89E5-D2111FF52B80','4C15E8E4-0D40-48B7-94FF-11375FC05238','4D756826-52FA-4A58-825F-FE76BD904C0C','50B54334-39AE-4DF2-9E8B-62245B77E628','523C9730-ADEB-4705-A7F8-8BBCCA0D14B3','52F1EB45-63BE-41CA-B942-01DF106FF00C','53059183-8A1A-47F1-846C-FD7F56362C2D','56897FC7-5EBB-48A1-96EE-5AA7C1D1A0D1','571DF495-D74E-4AFE-9387-1FAFC08B70C1','58891797-DED4-41CE-A6E2-8C81EED384C6','58B329D1-F48C-4BC1-9D3B-7001061E56D0','5C432B68-A108-4906-9D75-25AFDA18BF04','5D87B045-95DB-46E4-B3A3-79311366ECC3','6110FB71-E1C2-4CFD-B429-7BEE752C2AC7','6365FCBF-393E-4041-A099-A617C5097FEE','657BC84D-89BA-40EE-8DD5-DA96D6822045','6A287918-AE4A-4536-8791-B39AC1AE2CAA','6AF2C1F5-B474-4A85-94BC-72BAE0EBCEE0','6AFA7DAB-2F02-4022-B62F-7A18E1F76979','6C13A3C2-64CF-41D1-AA14-E17C22BF28D9','6C3A11CB-DE51-4CC4-B7A4-2A98E4E791DB','6E9DEC8B-24FB-4EE4-B8E3-E49313C436DF','718D7C17-02EB-4700-8265-F94EDF7CE8A2','7395376E-871B-4646-B517-2ECC230AC07C','74DD27C4-6FC6-4CEF-B260-E6EF82239708','77325258-AF98-43A0-9F8B-5EA0962FB2D8','776F74B5-B818-4EE6-B17E-67A547036955','77DC7DBA-87EF-4D33-B86B-9D51C3EDD07C','7810975D-859A-45C9-B64B-B4F53649601D','7ECBE610-73B5-4DF3-B0CC-C44FF45F01B1','80C2258C-0A8C-474D-BBDD-93B042F632BB','820D19FE-1392-428A-9776-526EB9C53AB1','82605679-7007-4229-8D40-D87C8D78AEFB','8285D9BD-7110-46E2-82BA-F93271863847','8292F5B1-0B77-4245-9145-09F9B1B3BBF8','84748FCC-ECD5-4E0F-B9AA-CEEED2F40BE7','85522562-7356-4D1C-85A4-8C14F8035507','85D99C2C-E495-435C-BB25-9DF7E6D44C59','894CD912-3739-4AA3-A93B-D97499973F22','89847331-2927-4689-A94F-615D58A18D7C','8C19438E-620D-44C9-A05A-C4A6AA5ACA9A','8ED17599-3B74-4CB5-A2AE-DCCB7F16D4A7','8F867862-DC88-43E9-B695-AF05D47967FB','91045069-E191-43F6-B2FF-375EABF39B2B','92A2B2EE-C1C5-402B-B610-F9621061B07F','95770673-1D81-4892-8E2E-9F8747162CED','95876FE5-8402-47C3-B515-49C11EBD78BF','96E6787D-E6F5-46AE-833D-0D9546C9C595','985884A4-6EF4-45AD-85FB-C64C82406D55','9AFF959B-954A-4F66-AC96-52AE0EBEA22C','9FF01E29-0916-46DC-A66A-D3907A6D848A','A02DF0F8-BD86-4307-8EA6-2CFF8699F25C','A1178459-D885-4A82-88B6-AC63D59E04A0','A70B36A6-43A9-4739-BA34-EB5BEF4F56D6','A80AD16D-0D91-4DF9-8331-C9D3C02E904C','A8152845-A5C0-4C08-8594-5E07C0C1DC2C','A827262D-F4B2-42C9-B302-3397F585F934','A8BF774E-48E3-420D-98D6-4FFB7AF5C7AB','A9C44E35-4AF2-41E9-91F3-7D6D5DE57A48','AA38699D-2D46-4314-87F3-EEB377233DB5','ACB302E6-C8E6-4822-B88B-35E7DE85C349','AE7080A8-6AAA-4D02-965F-80CA03AAA56A','B1CA66DF-F3E9-4895-B8C8-909966E7A1CA','B4027532-6A68-42D1-8937-C262D3B60956','B8D76410-B52B-4E38-8F09-CB17B5B1A929','B90569A7-8178-48EE-B3E9-86B750350029','BBE8B73D-4E01-4717-AC77-60F36B2404A5','BE01EBE8-D7E9-474B-AB52-B8A10F87D414','BE957762-D852-4306-A2D1-4709095BB9A6','BEE9DCD7-42B5-45BB-A1D2-DE27AF50D489','BFA749E3-C2E9-4DB0-A1EB-675A336B9817','C08CC03A-992C-48B6-A2C5-A1C0B5F06FF3','C1049FCE-117C-4F12-9C64-B31090A621E3','C1840682-83EF-4026-863A-0632C6132AE8','C20A6A85-D35F-43AE-A269-9DF6584C3E90','C747464A-8658-418E-B999-471E2C805760','C74B41F6-1A5F-47CD-ACED-2CE1E5D17DB9','CB071619-181C-4243-BAA4-ED68B3F67E2B','CCC25176-2E10-4C74-ABC8-53F2E3B686BE','CD92C8CA-3904-4E12-A066-50127B365B87','D1784B7E-1DF8-4FF1-AF4B-0F54E93E86D8','D1CE115F-C1AE-4F58-8C6E-984E7784967D','D31F7531-09F8-449D-B9F1-812FB1756629','D7D955E6-C87A-4878-97B1-841CF359DBAF','DEC28519-CAF2-4D6C-A1B7-BFA6C28323E0','E0FCD947-BFF3-45F8-886B-3EB2799A9F1C','E206A005-9C55-427D-AA53-E8B706BE2668','E55D4119-FBCE-4A7A-8F66-CFA562E6A065','E7CB0197-9D1A-4B12-9B51-3A2C9F1567C1','E8527481-904A-4454-9D72-BEE87EB05574','EA5C658F-9858-4120-93F8-ED763EA0B4FB','EC50686B-6C1C-4330-A6FD-26464BC40EBF','ED59BBE9-E856-4F19-B594-C481B9541659','EDFB11FC-1798-4C95-97F8-1A171A2AFAC5','EE7EF0E0-302D-43E1-85AC-0296CEF56604','F03BEC3C-461E-48E0-B427-7E235E009438','F0EDB28D-36BF-46A8-86F2-17C192A8F5A8','F3A08BE9-9A14-439B-AE9E-21316A3DF572','F69AA780-645E-4FAB-AD38-B5B730E8D14E','F8507C95-0BD3-475A-9B94-6F3E46A943F1','F93C642F-8911-4757-A1C8-59E73C9F60D5','F9B7756C-A6DB-431B-A3E6-B097D244C731','FC30F3AA-9112-4F68-8D6D-E56D46E27F9C','FBD7BA5A-F326-472D-AC6A-F026024A19FD','FD94F030-4D10-4B83-847C-A65AE6100715','FDAC1D8F-B79B-49A6-B560-CA9DCB49759E','FE343270-E75D-4D3B-8B17-C606E2E72B53','FE766670-6DE1-424A-B76A-DF48398979DC','FF0E6ACB-2D6F-4593-BD81-DBFB298E25EE')
AND delete_flag IS null
AND tenant_uuid = '5242262B-748A-4D67-90E7-57DE90DB1ED1'

SQL Explorer returns:
sum sum
------------------------- -------------------------
1287.00000000000000000 258723.61436559289000000
Number of rows returned: 1
Query executed in 18 ms.

overall execution time 30 ms

in version 2020.6, I get:
databaseManager.getDataSetByQuery('stsservoy', query, [] , 1);

{rowColumns:null,rowIndex:-1,row_1:null}

The only thing I've not done but have considered is moving to a later version of postgreSQL.

thanks,
--Joe.
Last edited by joe26 on Tue Sep 08, 2020 5:50 pm, edited 1 time in total.
joe26
 
Posts: 172
Joined: Wed Jun 19, 2013 10:30 pm

Re: RESOLVED getDataSetByQuery runs in 7.4.8 not in 2020.6

Postby joe26 » Tue Sep 08, 2020 5:43 pm

Finally found the issue. 2020.6 is stricter for the plugin as Servoy relies upon uniqueness, like making window clones and UUIDs throughout.

The line

SELECT sum(pcmk_qty),sum(item_weight*pcmk_qty) FROM pms

should read

SELECT sum(pcmk_qty) sum1,sum(item_weight*pcmk_qty) sum2 FROM pms

which returns the correct information.

Not an SQL quru, but it fixed the issue.
joe26
 
Posts: 172
Joined: Wed Jun 19, 2013 10:30 pm

Re: RESOLVED getDataSetByQuery runs in 7.4.8 not in 2020.6

Postby swingman » Wed Sep 09, 2020 9:04 pm

Very good to know, thanks for letting us all know...
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1472
Joined: Wed Oct 01, 2003 10:20 am
Location: London


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 10 guests