Calling sa_conn_info() always returns only 1 connection when called from app

[vlad1]

Hi, version SQL Anywhere 16,

So I'm trying to create a check using database layer/PB app to check if one particular username is already logged in. Upon supplying of userID and pwd and establishing DB connection, I immediately want to check is there another active connection for the same user to see if the user is already connected.

I've been calling sa_conn_info() from ISQL and I get nicely all active connections, sometimes 7, sometimes 10, depends. In fact, I'm not really calling sa_conn_info() itself, but just using the query that is defined within it, which is

select "Number",
"connection_property"('Name',"Number") as Name,
"connection_property"('Userid',"Number") as UserID,
"connection_property"('CommLink',"Number") as CommLink,
"connection_property"('LastReqTime',"Number") as LastReqTime
from "dbo"."sa_conn_list"(null,null);

If I call that from ISQL I see all DB connections. When I try to do the same from PB code (just after our login and DB connection initialisation) by calling a retrieve on datastore with the above, the datastore always returns 1 row for 1 connection of the user just logged in. I immediately run the same query in ISQL - I see many connections.

So I wrote a DB stored function which calls the above query, loops through it and reports number of connections. When I call it from ISQL, I get 7 or 10 or whatever rows. When I call the stored function explicitly from PB code, I get only one row again. How come?

What am I doing wrong? How come a call from PB app always returns only one, that, connection, and a call from ISQL returns all? Or is there an easier way, is there a SYS table which keeps the data rows on active DB connections?

Thanks

[VolkerBarth · Accepted solution]

With v16 and above, the user needs to have the MONITOR privilege to see details of other connections. I assume the application user does not have that privilege.

The behaviour does not depend on the application itself, so DBISQL does not behave differently here than any other application.

[vlad1 · Accepted solution]

Brilliant, works now. We can probably give users that privilege.

Thanks very much, I knew it was something easy I was overlooking.