Switching DB (Sql to Oracle)

[raja]

Hi,
I develop reports against SQL database (sql express). I use OLDB provider for SQL, (here is the connection string, "Provider=SQLOLEDB.1;Password=xyz;Persist Security Info=True;User ID=sa;Initial Catalog=Database1;Data Source=SERVER1") to design the reports but at run time, it may be required to produce reports against oracle datbase (it has the same schemas as that of SQL db).

Here is the code I use to do this, but I always get a blank report (no error and no data)...

Code: Select all

	string name = stiReport1.Dictionary.Databases [0].Name;
	stiReport1.Dictionary.Databases.Clear();
                string connectionString = "Provider=OraOLEDB.Oracle.1;Data Source=ORCL_DEVORCL;Password=xyz;User ID=USER1";
	stiReport1.Dictionary.Databases.Add(new StiOleDbDatabase(name,connectionString));
	stiReport1.Dictionary.Synchronize();

Can someone please help me how to do this?

Thanks!!!

[Brendan]

Hi raja,

Your Oracle Connection string looks ok.

The only thing I can think of that might be causing this is the Character casing of your Oracle Columns. I beleive when you create a table with Columns they are created in Upper case Characters unless you specify to preserve Case.


If you're SQL Datatabase had a table with columns "TypeID, Description, Amount" and you created a Datasource in Stimul Reports against this table then the Fields in the Datasource will also be the same. However if the same table in Oracle had the columns defined as "TYPEID, DESCRIPTION, AMOUNT", I think they will not match in the Stimul Report Datasource because the columns are case sensitive.

[Edward]

Also please note that the type of the Oracle DataSource is StiOleDbSource or StiOracleSource. But for SQL Server type of the DataSource is StiSQLSouce or StiOleDbSource. It depends of the type of connection you are using. If you are using native clients then the types of the Sources will be different (StiOracleSource and StiSQLSouce). If the type of Connection is StiOleDBDatabase then DataSources types will be the same.

Thank you.

[raja]

Hi raja,

Your Oracle Connection string looks ok.

The only thing I can think of that might be causing this is the Character casing of your Oracle Columns. I beleive when you create a table with Columns they are created in Upper case Characters unless you specify to preserve Case.


If you're SQL Datatabase had a table with columns "TypeID, Description, Amount" and you created a Datasource in Stimul Reports against this table then the Fields in the Datasource will also be the same. However if the same table in Oracle had the columns defined as "TYPEID, DESCRIPTION, AMOUNT", I think they will not match in the Stimul Report Datasource because the columns are case sensitive.

Thank you, it is the stupid "case" issue, the following code solved the issue..

Code: Select all

                                               StiDataSourcesCollection collectionDS = stiReport1.Dictionary.DataSources;
			foreach (StiSqlSource ds in collectionDS) {
				foreach (StiDataColumn dc in ds.Columns) {
					dc.Name = dc.Name.ToUpper();
				}
			}

Thanks again.