Is there a way to set CON property connection apart from the connection string?

[former_SQLA_member1694893]

Hi

I need to put my application username (the username the user uses to login - not the username in the database) in the CON connection parameter in order to use that information in FoxHound and ISQL.

My problem is that I cannot change the parameter connection for each user and are thinking to change it in my application (after the application logs in).

Is there a way to change CON property after the connection? If not, is there any other connection property which I can use to do so?

Best

[Former Member]

In the connection parameter, it connects to the database file using the user name and password which are used to create the database file. For working with login, you have use a table where the login information is saved and checked at the time of login through program and it should not be used in "Connection" paramater. Hope this will be useful to you

[Breck_Carter]

I don't think you understand the question. The CON parameter is just a way to give a name to a connection, it has nothing to do with logins or passwords. Marcos just wants to be able to change the connection name from within the application, AFTER the connection has been established. Sadly, the only time you can specify the connection name is when you give a CON=xxx value in the connection string when MAKING the connection. Using a table will not help.

[MarkCulp · Accepted solution]

This is the same question as this one asked a few days ago. The answer is no, there is no method to change the connection's name after the connection has been established.

[former_SQLA_member1694893 · Accepted solution]

Hi Mark I saw it just after I posted my question. Thanks

[VolkerBarth · Accepted solution]

Well, I tried to answer differently as I think the question somewhat different: If you can not the information given by a connection string, what are the other options...

[Breck_Carter · Accepted solution]

@Mark: Is there any other connection_property string value that could be changed by the application and then retrieved by another process? Foxhound wants to be able to identify each connection in a meaningful way, but sometimes the quartet (connection number, user id, ip address and connection name) is not sufficient.

[Breck_Carter · Accepted solution]

...sadly, CALL sa_user_defined_counter_set ( 'UserDefinedCounterRaw01', 'Hello, World!', 1, 0, 0 ); doesn't work because it expects a bigint... even though properties are all stored as strings AFAIK. It's too bad the wonderful idea of "user defined properties" is so limited.

[VolkerBarth · Accepted solution]

...just number your users/connections:)

[MarkCulp · Accepted solution]

Breck: "UserDefinedCounter*" are counters not strings... i.e. a counter is an integer type (bigint in this case).

[MarkCulp · Accepted solution]

@Breck: I'm not exactly sure what the author of this question is trying to do but another thing that I'll mention is that the environment variable SQLCONNECT can be used to set any part of the connection string prior to running the application including the connection name.

Example:

SQLCONNECT=CON=foobar

myapp.exe

As long as the application does not set the CON connection parameter explicitly in the application the connection will be given the name "foobar" and this can be retrieved using connection_property('name') within the server.

But Glenn's suggestion (using a user-defined public/connection option) is a good method as well.

[Breck_Carter · Accepted solution]

Until the secret internal workings are revealed, we foolish end users tend to believe what SQL Anywhere tells us :)...

SELECT EXPRTYPE ( 'SELECT CONNECTION_PROPERTY ( ''UserDefinedCounterRaw01'' )', 1 );

varchar(32000)

[MarkCulp · Accepted solution]

Another method that the application could uniquely identify a connection is to add a piece of (unique) information into the AppInfo connection parameter when the connection is created.

Example

dbisql -c uid=dba;pwd=sql;appinfo=myuniqueappinfostring

and then use

select substr( row_value, 9 )

from sa_split_list( connection_property('appinfo'), ';' )

where row_value like 'appinfo=%'

With the above example the result will be 'myuniqueappinfostring'

[Breck_Carter · Accepted solution]

If a number was useful, CONNECTION_PROPERTY ( 'Number' ) would be sufficient.

[MarkCulp · Accepted solution]

Ah I see the confusion. The connection_property() function always returns a varchar(32000) regardless of the connection property selected. In the case of 'UserDefinedCounterRaw01' the underlying value is an integer type. Note also that the sa_user_defined_counter_set() procedure is new but is documented in the docs online - http://dcx.sybase.com/index.html#1201/en/dbreference/sa-user-defined-counter-set-system-procedure.ht... - and all parameters (except first) are integer types.

HTH

[Breck_Carter · Accepted solution]

What the user is asking for is some way to set a CONNECTION_PROPERTY() string value at runtime, after the connection has been established. The UserDefined things are a nice addition but only work for numbers. The purpose is for the connection to provide its own unique identification as a property that can be retrieved by other connections via CONNECTION_PROPERTY(). Currently Foxhound records and displays the CONNECTION_PROPERTY ( 'Name' ) value as one of four values (along with connection number, ip address and user id) that can help identify the connection. In this particular case, specifying the Name property value before establishing the connection is not sufficient.

The SQLCONNECT suggestion is a different way to associate a value with a connection name before the connection is established.

As far as I can tell, for a database with 1000 connections, Glenn's suggestions would require 1000 new rows in SYSOPTION... with no apparent way for some other process to associate a value with a connection.

[Breck_Carter · Accepted solution]

A separate user-defined table would probably work, if Foxhound was modified to retrieve it. After connecting, the application would simply

INSERT t ON EXISTING UPDATE VALUES ( @@SPID, [unique-string-id] ); COMMIT;

and Foxhound would retrieve all the rows

SELECT * FROM proxy_t;

[former_SQLA_member1694893 · Accepted solution]

@Glen: WONDERFUL solution! That way I can create a public option through a script and my application can change that option when a user logs in. The only issue is to show that option in a monitoring tool like FixHound.

@Breck: I think the table solution was the way I would be going but the option solution is simpler, isn't it?

[Former Member · Accepted solution]

It wasn't clear at the time I wrote this that you were looking for connection-level metadata. The database option technique works for users, because the values in SYSOPTION are per-user; but they are not per-connection, so if a user has multiple connections the application would be overwriting the same values for the same user.

Mark and I discussed this briefly before lunch - AppInfo may be the way to go.

[Breck_Carter]

Here's a kludge which serves to demonstrate exactly what is needed: a way to dynamically set a value that will show up in subsequent calls to CONNECTION_PROPERTY and sa_conn_properties:

-- dbisql session 1

SET TEMPORARY OPTION connection_authentication = 'Hello, World!';

-- dbisql session 2

SELECT Number, Value FROM sa_conn_properties() WHERE PropName = 'connection_authentication';

Number,Value
2,Hello, World!
1,Company=Sybase;Application=DBTools;Signature=000fa55157edb8e14d818eb4fe3db41447146f1571g2a1b5949cab32c7760419117ca3ce88770fecfd7

(don't let the numbers confuse you... "dbisql session 1" has connection "Number" 2 in the result set)

[VolkerBarth]

So you're asking for a user-defined connection property?

How would another process know the name of that connection property?

Or are you asking for a general enhancement like "UserDefinedProperty01" to "UserDefinedProperty05"?

Or even combined with "UserDefinedPropertyName01" to "UserDefinedPropertyName05" that would get the user-defined name of the user-defined properties ("Meta level, can you hear me?"?

All with the according (temporary) options added...

[MCMartin]

You should add this as a Product Suggestion to have dynamic user connection properties

[VolkerBarth]

Agreed! - (Though personally, I don't have that requirement.)

Besides that, it's obvious that Glenn and Mark are already in for that suggestion:)

[VolkerBarth]

A further suggestion:

You might even use the InitString connection parameter to specify a statement that will be executed right after the connection is established.

That way, you could add a statement to set the user-defined database option or to insert into a particular table "automatically" - i.e. without having to change the application itself.

(A custom login procedure would work as well but could possibly have side effects on other applications.)

Obviously this approach would require that the added information is known at that time (or can be accessed from the database with the help of such a statement...

[former_SQLA_member1694893]

Volker I cannot change each connection parameter as sugested as this is in the client environment which I cannot access. What I need is something that I could change as a user logs in in my application. What would be ideal is to have something like this:

call property_change('name', 'something useful');

in a connection basis.

[VolkerBarth]

Have a look at the "login_procedure" option:

It allows to add a stored procedure that is run every time a user connects. You could add your statement as part of such a procedure:

create procedure DBA.MyLoginProcedure( )
begin
  -- always call the builtin proc...
  call sp_login_environment;
  call property_change('name', 'something useful');
end;

grant execute on DBA.MyLoginProcedure to public;
set option public.login_procedure = 'DBA.MyLoginProcedure';

However, as you can see, that proc doesn't allow for external information (at least AFAIK), i.e. the "something useful" might have to be accessed from the database itself...

[former_SQLA_member1694893]

Volker

I am aware of login_procedure option but the problem is that we cannot change the name connection property (even there). The problem is not where but how we change the name connection property. AFAIK, this property could only be changed at the connection string (...CON=something useful...).

[VolkerBarth]

Sorry, then I have misunderstood your question.

I just wanted to show that - given you cannot make changes in the client environment as stated - that a login_procedure might be a way to "do something" automatically when connecting.

I agree that the "how to change" part of the question is still open to discussion, and it seems that without a change in either FoxHound or SQL Anywhere itself you won't get further at the moment...

[Former Member]

A DBA can create a PUBLIC option, and once created anyone can override it with a connection-level setting; all such options are then stored and modified in the catalog.

DBA:

set option public.brecks_option = 'default'

Paulley:

set option brecks_option = 'paulleys string'

DBA:

SELECT * FROM SYS.SYSOPTION where option = 'brecks_option'

user_id,option,setting
2,'brecks_option',default
102,'brecks_option',paulleys string

[Breck_Carter]

Alas, when a comment is converted to an answer, it doesn't drag other comments with it. Here is a copy of the comment/reply from MarcosCunhaLima...

@Glen: WONDERFUL solution! That way I can create a public option through a script and my application can change that option when a user logs in. The only issue is to show that option in a monitoring tool like FixHound.

@Breck: I think the table solution was the way I would be going but the option solution is simpler, isn't it?

[Breck_Carter]

As Glenn noted in another comment, the "SET OPTION brecks_option" syntax specifies a value unique to the user id, not connection (and I'm guessing in your case every connection uses the same user id, am I right? 🙂

So, if it worked, it would be a fine solution, since I think you can play that trick with all version of SQL Anywhere (and Foxhound works with them all back to 5.5)... but alas...

Also alas... Foxhound 1.2 is feature-frozen and ready to ship, so the new-table-solution won't be available any time soon.

Let's hope Glenn and Mark think of a way to abuse use AppInfo... it seems ideal.

[VolkerBarth]

In my humble experience, converting a "comment tree" to an answer usually has worked fine - if not, you might still be able to up- and down-cast the child comments manually - though that won't work for grandchildern...

[VolkerBarth]

I don't think you can change the information specified in a connection string after the connection has been established (other than do a re-connect).

Besides that, you might use some of the following:

1. If your application username is the OS user name and you are using v11 or above, then the follwoing call will return that user name:

select connection_property('OSUser');

1. You might use a connection-specific variable and fill it from within your application. Such a variable can be used like a "global variable" but is connection-specific.

create variable MyUserName varchar(100);
set MyUserName = 'WhatEverYouLike';

[former_SQLA_member1694893]

Volker It would be wonderful to change CON as it's what FoxHound shows in all its forms. Anyway, I'll try your solution. Best

[VolkerBarth]

Sorry, I've overseen the "show that in DBISQL or FoxHound" statement.

Then a connection-level variable won't do: By design, it's only accessible from the same connection, i.e. not from any "monitoring tool". (These connections could just see the value of their own variables, if they are defined there at all...)

[former_SQLA_member1694893]

@Volker: I think I will create (as Glenn sugested above) a public option and overwrite it after a user logs in. Best

[VolkerBarth]

IMHO, besides a application-specific approach based on user-tables, the particular problem discussed here could be solved in a general fashion if there were enhanced user-defined database options that could be both

1. connection-specific (just like temporary options) and
2. permanently stored (just like non-temporary options).

Such options could then be read by other connections on a per-connection base.

However, AFAIK this combination does not exist today, and besides this particular case, I would not know of a use case...but that's just me:)

[former_SQLA_member1694893]

Yes, you defined wonderfully.

[MCMartin]

The AppInfo connection property also includes the OSUser, this information should be accessible like the CON peroperty.

[VolkerBarth]

The OSUser can even be directly accessed by its own connection property - see my answer:)

Besides that, I guess we are all more or less brain-storming how FoxHound can offer its users a way to identify each connection in a user-defined meaningful way - and I guess that will only be possible by a FoxHound-specific way...

In the end, I guess (yes, another guess...) Breck might add a feature to let FoxHound user configure the name of a table with a particular schema (or a simple connection-id -> user-defined-description-string mapping) that they can fill and that FoxHound will then use to add the user-defined-description-string to its display...

Just my 2 cents:)

[MCMartin]

Regarding its own connection agreed, but it is also still accessible from the outside, so the FoxHound should be able to provide this information the same way as the CON parameter. Therefore I added this as an extra answer to MarcosCunhaLima.

[VolkerBarth]

FWIW: Specifying a particular connection id as 2nd param, you can get the OSUSer connection property of other connections as well as all other connection properties (including AppInfo)...

[MCMartin]

Ah, I have misunderstood you

[former_SQLA_member1694893]

Volker

Can I change the OSUser parameter?

[VolkerBarth]

I don't think so.

AFAIK it's simply a value that is automatically fed into the APPINFO connection parameter for each connection by the SQL Anywhere client library, just like process name, thread ID, API and the like. And it's simply the name of the OS user running the client application...