Best practice using SQL Server and Oracle


Author
Message
Ross L. Rooker, Sr.
Ross L. Rooker, Sr.
StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)
Group: Forum Members
Posts: 153, Visits: 462
We separated the Security Database from the actual Data Database in our application. The Security Database is in SQL Server. We have 2 databases for the Data: one in SQL called VALET_DATA and another in Oracle also called VALET_DATA. Created a Busness Object project called TrustedVALET_BO with a business object mapped to the SQL database table called CUSTOMERS. Created a project called TrustedVALET that has a Customer Maintenance form and dragged the TrustedVALET_BO CUSTOMERS business object to the maintenance form and works perfect for the SQL connection.

Now we need to provide the ability to have a different connection to get the data from the Oracle Database which is called VALET_DATA and the table name is also called CUSTOMERS. The column names in the table CUSTOMERS is exactly the same for both ORACLE and the SQL Data. What is the best way to allow for both SQL and Oracle?

It seems that when the Business Object is created (mapped) it is specific to SQL. Is it possible to use this BO with an Oracle connection? Or do we need to create BOs for Oracle?

Replies
Ross L. Rooker, Sr.
Ross L. Rooker, Sr.
StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)StrataFrame User (245 reputation)
Group: Forum Members
Posts: 153, Visits: 462
Please, respond ASAP. I need to get back to our team to determine what we will need to do to accomodate our Oracle customer in our development process.
Bill Cunnien
Bill Cunnien
StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)
Group: Forum Members
Posts: 785, Visits: 3.6K
Interesting scenario!

Have you tried setting up two named connection strings in the connection manager?  Not sure how you would trigger this, or if it would work, but when a user needs the data from the other source, simply change the DataSourceKey of the BO to the appropriate named connection.  After that, fill the BO and let the user do his thing.

Not sure if that helps, but at least it keeps the thinking gears rotating.

Smile
Bill

Bill Cunnien
Bill Cunnien
StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)StrataFrame VIP (1.2K reputation)
Group: Forum Members
Posts: 785, Visits: 3.6K
Here's what I would do in the SetDataSources of the program.cs (the VB equivalent escapes me at the moment):

DataLayer.DataSources.Add(new SqlDataSourceItem("MySQLConn", "Data Source=MySQLServer;Initial Catalog=VALETDB;User ID=myuserid;Password=mypassword"));

DataLayer.DataSources.Add(new SqlDataSourceItem("MyOracleConn", "Data Source=MyOracleServer;Initial Catalog=VALETDB;User ID=myuserid;Password=mypassword"));

I have absolutely no clue how to write a connection string for an Oracle database, but this should give you an idea.  After these are set, then, based on some predefined event or condition, you would set the BO datasourcekey:

MyBO.DataSourceKey = "MyOracleConn";

MyBO.Fill();

You are off and running.  I think.

I haven't actually tried this, but, in theory, it sounds good.  Hope it helps.
Bill


Greg McGuffey
Greg McGuffey
Strategic Support Team Member (4.8K reputation)
Group: Forum Members
Posts: 2K, Visits: 6.6K
I think this is bit more complicated. First, using what Bill indicated, you need to make sure that the oracle data source is using the OracleDataSourceItem, not the SqlDataSourceItem (also check the connection string...I doubt the Oracle connection string looks that simular to the MS SQL server connection string):



// Use SqlDataSourceItem for the SQL data source, with MS SQL Server connection string

DataLayer.DataSources.Add(new SqlDataSourceItem("MySQLConn", "MS SQL server connection string here..."));



// This needs to be the Oracle version of a data source item, with Oracle connection string

DataLayer.DataSources.Add(new OracleDataSourceItem("MyOracleConn", "oracle connection string here..."));




However, you'll also have to handle your Fill/Data retrieval functions differently, as the SQL dialects used between MS SQL server and Oracle differ. If you handle that well, then you just switch data sources and you're good to go (I think..).



I'd look into the QueryInformation object. I believe this is used when you need to handle different types of SQL dialects and is what SF uses to generate SQL based on the type of data source item (i.e. SqlDataSourceItem vs. OracleDataSourceItem).



Hopefully one of the SF guys will chime in here... Ermm



GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Threaded View
Threaded View
Ross L. Rooker, Sr. - 17 Years Ago
Ross L. Rooker, Sr. - 17 Years Ago
Bill Cunnien - 17 Years Ago
Bill Cunnien - 17 Years Ago
                     I think this is bit more complicated. First, using what Bill...
Greg McGuffey - 17 Years Ago
Ross L. Rooker, Sr. - 17 Years Ago
Greg McGuffey - 17 Years Ago
Trent L. Taylor - 17 Years Ago
Ross L. Rooker, Sr. - 17 Years Ago
Greg McGuffey - 17 Years Ago
Trent L. Taylor - 17 Years Ago
                     Ah, got it. That makes sense. :D
Greg McGuffey - 17 Years Ago
                         Cool :cool:
Trent L. Taylor - 17 Years Ago

Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search