Can I change the schema name in the connection string rather than before every table name??

[#1]

Hi Community,

I have a document that is intended to be a generic qvw and pulls a few hundred tables from a database. To edit this, we change the connection string to connect to a new database and the document should reload properly.

Unfortunately, in the current scenario, the document requires a schema. Normally, one would select the schema via the "Owner" dropdown in the "Create Select Statement" box, shown below.

In this case, with the "Owner" (or Schema) name "DIP", it would produce a statement in Qlikview like:

Select *

FROM DIP.tablename;

This is no good for me, because the code to load tables is already set which means I would have to manually add "DIP." before the hundreds of table names in FROM statements of my qvw. I am hoping there is a way to manually add a schema name in the connection string or something so that I do not need to take a long, long time running through my entire .qvw and finding and replacing "DIP." in front of all of my tables.

Is my question clear? Thanks for any help or further information.

Shanrahan

[#2]

Hi Shanrahan,

I assume you are using ODBC-Connect. Then you can specify your user_id (=schema name) in your connection string like this:

ODBC CONNECT TO db_user (XUserId is KUXYISdOIbXYWXNNBDbSC, XPassword is aQONBSdOILLA);

(to create the line above I used the wizard and put in the db_user and the according paswd)

If done so, it is not nessecary to qualify the table with the schema name, except you are using i.e. public synonyms (or views or so) with same names as your local tables.

Hope this helps

Roland

[stephencredmond]

Hi,

The user in your OLEDB connection should be DIP. The DIP user should be able to access the DIP scheme without the pre-pended schema name.

Regards,

Stephen

[#4]

I was actually using an OLE DB connection, but I changed to ODBC to try that method. I think the user_id you're talking about is different than the name I am talking about.

I need a specific username and password to log into the database - we'll call it username1 and password1. When I create the ODBC connection, I enter that username and password. I can't just enter DIP into the username, or it won't connect to the database. The schema I am referring to is the one that I would select in that picture I attached above, which is not just a list of user ID's (right?)

I did actually see in the ODBC add connection wizard (Control Panel -> Admin Tools -> Data Sources (ODBC) -> add / new) a tab for SQL Server migration where you seemingly could choose a schema, but my only options were "database" and "owner" and neither of those seemed to work.

[#5]

Stephen, do you mean change the part of the string 'User ID=xxxx' ? If so, I can't change that because I have a specific username and password that is required to log into the database at all, which is separate from the schema I refer to to choose the "DIP" shown in my picture above. If not, how do I add that to the string?

[stephencredmond · Accepted solution]

Hi,

If you can't log in as the same user as the schema name, you will need to set the default schema for the user that you are logging in as. Not sure how to do that in Oracle - it is a fairly trivial setup in SQL Server.

Regards,

Stephen

[#7]

Thanks Stephen. For the record, I changed the default schema for the user right in my qvw, and changed the connection to an ODBC connection (it didn't seem to work with OLE DB). After the ODBC connection string, I included the line:

sql alter session set current_schema = [schema name goes here];