Is it possible to pass a variable like App through the SQL server database connection string?

The app_name() in SQL server can be override by the App variable in the connection string. Is there a way in Lucee that we can similarly override a variable such that the value can be used in a view?

For testing, I created a view in SQL server as below:

SELECT app_name() AS outputData

When I run this view, returns the string “Microsoft JDBC Driver for SQL Server”.
Is there a way I can set a value to the App variable so that it gets returned when I call this view?

You can likely add this in the connection string within the Application.cfc (Lucee Documentation) and I believe you can also edit the connection string in the admin. Either way, try adding ;App=[whatever] to the connection string and seeing if that gets passed through.

Thank you @ddspringle, I tried that, but it didn’t work. Moreover that will not be a good solution for us. Anyway we changed the course and avoided that requirement.

@ddspringle is correct that you can add that to the connection string, but each JDBC driver has its own format, and some may not support that feature at all.

For example, in Postgres via the pg-jdbc driver, I set the Application Name in my connection string with the ApplicationName query string. So the way I use it, is by defining the datasource in Application.cfc, and setting the connection string to:

connectionString: "jdbc:postgresql://localhost:5432/db_name?ApplicationName=#this.Name#"

That takes the CF-Application name and specifies it in the connection string, so when I inspect queries in the DBMS I can see the CF Application name for the application that executed that query.

2 Likes