SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.166 Pro] - PostgreSql - no arguments for some func
Goto page Previous  1, 2
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.166 Pro] - PostgreSql - no arguments for some func
Author Message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
Which issue do you mean? ODBC related?
I stuck with native connector since it seems to be more responsive. Showing function arguments still doesn't work in 9.0.194.
I executed application update. Should I change something manually?

Edit: yeah.. I can confirm that :OBJECT_ID still contains negative value :(
Guys, It's almost half a year already since it doesn't work properly. I think it's right time to deliver final fix.
Mon Dec 19, 2016 8:31 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
Sorry to hear that. I have reopened the ticket. Developers say it's fixed, but I'm not sure if anything needs to be done after the upgrade to 9.0.194 to make it working.
Mon Dec 19, 2016 11:12 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
Can you please confirm that you are using factory default queries? I know in the past you tried customizing them to fix the issue. I need to ensure there is no conflict caused by converting returned OID values to BIGINT or some other unexpected datatype.


A simple test on my system SELECT (4123456789)::oid shows the correct value is returned and displayed.
Mon Dec 19, 2016 11:24 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
I'm wondering if you had a chance to check your queries for customizations. Can you please confirm you're running factory default queries and still seeing that issue?
Wed Dec 21, 2016 1:11 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
Hello. Sorry for delay.. you know, Christmas
Right now I'm on SA 9.0.199 on factory settings. Still no improvement.

I tried to find something on my own.
I modified Objects query, casting all appearances of OIB to BIGINT - No change.
Then I modified Arguments query to just check out a content of OBJECT_ID variable. I did it this way:
Code:
SELECT (:OBJECT_ID)::TEXT, (:OBJECT_ID)::BIGINT::TEXT,'',NULL
UNION
-- original Arguments query right here


See attached screenshot. The OBJECT_ID still contains negative value.



BTW your test case SELECT (4123456789)::oid works for me too. I believe this issue is about how OBJECT_ID is fed with data. I can do some more tests for you. Just give me some scenarios to perform.
Wed Dec 28, 2016 6:05 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
Hi. According to the documentation OID is UINT4. Please don't cast it to BIGINT as it would lead to the parameter overflow and potentially to negative numbers. Would you please check that all casting to BIGINT has been removed?
https://www.postgresql.org/docs/9.1/static/datatype-oid.html


As a test, please run a simple query like

SELECT (one of your big kid values)::OID;

Does that query result appear correctly?
Wed Dec 28, 2016 8:42 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
This is the point. OID is UNSIGNED INT4 while all postgresql's common numeric datatypes are SIGNED. So, if you treat OID as postgresql INTEGER, then you will end up with negative number if value of OID is greater than 2147483647.

This is why CAST to BIGINT should help (and did AFAIR, if you look at older messages; however it was related to data grid issue).

However, I removed CAST to BIGINT from Objects query, and nothing has changed.
I did run Objects query in Editor, and get proper OIDs:
Code:
relname   case   oid   timestamp   timestamp
get_client_attributes   FT   2427107108      


Then a few tests for you
Code:
SELECT (2427107108)::OID
----
2427107108


SELECT (2427107108)::OID::BIGINT
----
2427107108


SELECT (2427107108)::OID::INTEGER
----
-1867860188


Please note, the value from the last test is equal to one shown on screenshot in my previous post
Wed Dec 28, 2016 9:29 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
Cast to BIGINT will not help, it will produce 8 byte value while the parameter is expecting and can fit 4 bytes only. Signed or unsigned might be a factor, but again we don't know that for sure, we believe it makes no difference.

Which particular OID value is causing an issues? What do you get when using I with the test query I provided earlier?
Fri Dec 30, 2016 12:14 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
I'm sorry I've overlooked the second part of your message. The important part is really here

Code:
SELECT (2427107108)::OID
 ----
 2427107108



So the above query produces correct result. If that's the case, then the issue isn't with parameter values populated by the Objects query. Perhaps an additional data type casting occurs down the road. I will ask our development team to run this particular value through the debugger and check what is then sent to the parameters of the Arguments query and whether the value is byte wise the same (same 4 bytes). I'm sorry it may take a little while. Most of the team is taking a break for holidays.
Fri Dec 30, 2016 12:27 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
Yes, this is what I've suspected.
Happy New Year to whole team.
Fri Dec 30, 2016 5:48 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
Thank you. Happy New Year to you too!

I will post updates on this issue as soon as they are available.
Fri Dec 30, 2016 11:14 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
We would like to give you a private build for testing this particular issue as we don't have a matching environment. We believe we have a working fix, but we need you to test and confirm it. Can we send you an email with download instructions? We have your email, we just need a confirmation that you would like to text that fix.
Thu Jan 12, 2017 3:42 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
Yes, please do it.
Mon Jan 16, 2017 10:23 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page Previous  1, 2
Page 2 of 2

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.