| Author |
Message
|
| akil |
 Posted: Fri Dec 19, 2014 12:56 am Post subject: IIB9: Dynamic Database MSSQL |
 |
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
With a Oracle database, I am able to make calls with a runtime provided dataSource & schema name.
| Code: |
CREATE PROCEDURE GET_ACCOUNT_SUMMARY
...
EXTERNAL NAME "*.get_account_summary"
...
CALL GET_ACCOUNT_SUMMARY
....
IN Database.{dataSourceName}.{schemaName} INTO retVal;
|
This however fails to work for Microsoft SQL server, as it does not find the unit.
There are a few differences between Oracle & Microsoft SQL, which makes it a bit difficult to understand the syntax.
Oracle:
-- there's one server
-- there's one database
-- there are multiple users
-- each user has a default schema.
-- a user can access multiple schemas
Microsoft SQL Server
-- there's one server
-- there are multiple databases
-- there are multiple logins
-- each login can have one user created per database
-- each user has a default schema (called dbo)
-- a user can have access to multiple schemas
Correspondingly the data source definition for Oracle does not have any property for a database, while SQL server has a property called 'Database' . This as per the documentation is the 'default database' that is presented after doing a login ( I suppose it does a USE database command or equivalent ).
Given that SQL Server has one additional variable called 'database' , how does one specify this name at runtime ?
The documentation talks about a runtime dataSource & a runtime Schema definition, it is silent on specifying a database at runtime.
Is this possible to do or is a dataSource to be created per SQL server database ?
_________________
Regards |
|
| Back to top |
|
 |
| maurito |
| Posted: Fri Dec 19, 2014 2:52 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
you need an ODBC connection that points to your SQL server database for each database you want to access.
Last edited by maurito on Fri Dec 19, 2014 5:21 am; edited 1 time in total |
|
| Back to top |
|
|
| akil |
| Posted: Fri Dec 19, 2014 4:03 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
The documentation says
| Quote: |
In Database, type the name of the database to which you want to connect by default. If you do not specify a value, the default value is the database that is defined by your system administrator for each user.
|
Since it says 'by default', I was hoping that there's a way to change it to something else ..
_________________
Regards |
|
| Back to top |
|
|
| maurito |
| Posted: Fri Dec 19, 2014 4:10 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| akil wrote: |
The documentation says
| Quote: |
In Database, type the name of the database to which you want to connect by default. If you do not specify a value, the default value is the database that is defined by your system administrator for each user.
|
Since it says 'by default', I was hoping that there's a way to change it to something else .. |
you need to specify a database (odbc connection) in the node configuration (compute, filter or database), and that will be the default. then you can access other databases the way you coded it, but you need an odbc for that as well. |
|
| Back to top |
|
|
| akil |
| Posted: Fri Dec 19, 2014 4:28 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
what is specified in the nodes is the 'datasource' isn't it ?
where is a database specified?
_________________
Regards |
|
| Back to top |
|
|
| maurito |
| Posted: Fri Dec 19, 2014 4:37 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| akil wrote: |
what is specified in the nodes is the 'datasource' isn't it ?
where is a database specified? |
Read my first post. |
|
| Back to top |
|
|
| akil |
| Posted: Fri Dec 19, 2014 5:00 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Your first post says
| Quote: |
you need and ODBC connection that points to your SQL server database for each database you want to access.
|
From this I understand that one way to is to create one data-source per database. I get that.
My question however is - is there a way to use a single data-source, and specify the database at runtime,
_________________
Regards |
|
| Back to top |
|
|
| maurito |
| Posted: Fri Dec 19, 2014 5:08 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| akil wrote: |
Your first post says
| Quote: |
you need and ODBC connection that points to your SQL server database for each database you want to access.
|
From this I understand that one way to is to create one data-source per database. I get that.
My question however is - is there a way to use a single data-source, and specify the database at runtime, |
Your understanding is incorrect. Read what I said again, you will see it does not say "one way is". |
|
| Back to top |
|
|
| akil |
| Posted: Fri Dec 19, 2014 5:43 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
I really cannot make out what you are trying to say.
Is it possible to have a single data-source configured, and change the database at runtime or is it not possible?
_________________
Regards |
|
| Back to top |
|
|
| maurito |
| Posted: Fri Dec 19, 2014 5:49 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| akil wrote: |
I really cannot make out what you are trying to say.
Is it possible to have a single data-source configured, and change the database at runtime or is it not possible? |
I does not matter how many times you ask the same question, the answer will be the same, and read it carefully:
"YOU NEED ONE DATASOURCE (ODBC CONNECTION ) PER DATABASE" |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Dec 19, 2014 6:03 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Okay.
You need one DataSource for each unique connection pool you want to a given Database.
You have to specify a DataSource on each node that wants to use ODBC. That datasource must point to the same type of database - DB2, Oracle, etc. that the node wants to access.
Particularly in anythigng that uses ESQL, you can use a different DataSource in your code. The name of this datasource can be chosen dynamically at runtime.
But it must be an existing datasource.
HOW to do this is left as an exercise. You need to understand how to read and interpret the Knowledge Center if you are going to be successful with this product. |
|
| Back to top |
|
|
| akil |
| Posted: Fri Dec 19, 2014 6:31 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
I am sorry, I can see that I am not communicating my question clearly enough.
I am aware of how to set the dataSource at runtime.
I am aware of how to set the schemaName at runtime.
Like you said, the above are detailed out in the info-centre, and there are samples as well.
So that helped was sufficient to get this working. I wasn't asking about that.
My question was, is there a way to set the 'dataBase' at runtime. ( this dataBase is a property only in the MSSQL section of odbc.ini, it is not applicable for Oracle ).
I did not find anything about changing the dataBase in the documentation of the CALL statement & the SELECT statement.
I however, found a mention about this in the 'configuring datasource for ms-sql' , where it mentions that the database specified in odbc.ini is a 'default'. Maybe the default is meant to be interpreted in some other way, I was wondering if this default meant that there is a way to dynamically change the dataBase name at runtime.
Specifically here the word dataBase is in the context of ms-sql (which allows creation of multiple databases within the same installation - they all of course share the same ip/port/ etc'. I am not talking about the 'database' as in 2 separate oracle databases, that's pretty clear will need separate data-sources (as even the IP/PORT/SID will be different).
_________________
Regards |
|
| Back to top |
|
|
| maurito |
| Posted: Fri Dec 19, 2014 6:37 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| akil wrote: |
| it would be easier however to programmatically set the dataBase name , just like the dataSource & the userName can be set. |
Ok, go ahead and specify the dataSource if that is easier for you. |
|
| Back to top |
|
|
|
|
