The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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

asked 01 Dec '11, 08:04

MarcosCunhaLima's gravatar image

MarcosCunhaLima
3067818
accept rate: 0%

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

(13 Jul '12, 10:03) Srinivas
Replies hidden
1

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.

(13 Jul '12, 13:33) Breck Carter

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.

permanent link

answered 01 Dec '11, 08:37

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264
accept rate: 40%

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

(01 Dec '11, 08:39) MarcosCunhaLima

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...

(01 Dec '11, 08:51) Volker Barth

@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.

(01 Dec '11, 09:48) Breck Carter
Replies hidden

...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.

(01 Dec '11, 10:02) Breck Carter

...just number your users/connections:)

(01 Dec '11, 10:14) Volker Barth

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

(01 Dec '11, 10:18) Mark Culp

@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.

(01 Dec '11, 10:36) Mark Culp
1

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)

(01 Dec '11, 10:49) Breck Carter

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'

(01 Dec '11, 10:51) Mark Culp

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

(01 Dec '11, 10:52) Breck Carter
1

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.html - and all parameters (except first) are integer types.

HTH

(01 Dec '11, 10:58) Mark Culp

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.

(01 Dec '11, 11:05) Breck Carter

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;

(01 Dec '11, 11:12) Breck Carter

@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?

(01 Dec '11, 13:08) MarcosCunhaLima

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.

(01 Dec '11, 13:30) Glenn Paulley
showing 5 of 15 show all flat view

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';

permanent link

answered 01 Dec '11, 08:45

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 01 Dec '11, 08:49

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

(01 Dec '11, 08:53) MarcosCunhaLima
Replies hidden

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...)

(01 Dec '11, 08:57) Volker Barth

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

(01 Dec '11, 13:10) MarcosCunhaLima

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
permanent link

answered 01 Dec '11, 10:10

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

edited 01 Dec '11, 15:44

Breck%20Carter's gravatar image

Breck Carter
26.8k422580826

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?

(01 Dec '11, 15:41) Breck Carter
Replies hidden

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.

(01 Dec '11, 15:49) Breck Carter

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...

(01 Dec '11, 16:47) Volker Barth

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...

permanent link

answered 01 Dec '11, 16:56

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 01 Dec '11, 16:57

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.

(02 Dec '11, 06:46) MarcosCunhaLima
Replies hidden

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...

(02 Dec '11, 07:27) Volker Barth

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...).

(02 Dec '11, 08:01) MarcosCunhaLima
Replies hidden

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...

(02 Dec '11, 08:15) Volker Barth

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)

permanent link

answered 02 Dec '11, 05:46

Breck%20Carter's gravatar image

Breck Carter
26.8k422580826
accept rate: 20%

edited 02 Dec '11, 05:49

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...

(02 Dec '11, 07:15) Volker Barth
1

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

(02 Dec '11, 07:23) Martin
Replies hidden

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

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

(02 Dec '11, 08:19) Volker Barth

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

permanent link

answered 01 Dec '11, 12:34

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

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:)

(01 Dec '11, 12:53) Volker Barth
Replies hidden

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.

(02 Dec '11, 02:29) Martin

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)...

(02 Dec '11, 03:14) Volker Barth

Ah, I have misunderstood you

(02 Dec '11, 04:37) Martin

Volker

Can I change the OSUser parameter?

(02 Dec '11, 06:47) MarcosCunhaLima

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...

(02 Dec '11, 07:05) Volker Barth
showing 1 of 6 show all flat view

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:)

permanent link

answered 02 Dec '11, 07:21

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 02 Dec '11, 08:16

Yes, you defined wonderfully.

(02 Dec '11, 08:02) MarcosCunhaLima
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×31

question asked: 01 Dec '11, 08:04

question was seen: 2,482 times

last updated: 13 Jul '12, 13:33