Oracle Public Synonym

Posts   
1  /  2
 
    
Kazak1
User
Posts: 39
Joined: 30-May-2006
# Posted on: 19-Jun-2006 00:24:57   

Does the designer support mapping of an Oracle public synonym to an entity?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 19-Jun-2006 08:40:24   

llblgen pro supports synonyms of tables and views, so it should be possible yes simple_smile . Just include the schema which contains the synonym into your project.

Frans Bouma | Lead developer LLBLGen Pro
Kazak1
User
Posts: 39
Joined: 30-May-2006
# Posted on: 19-Jun-2006 18:16:16   

My synonym is a public synonym. So, it does not belong to a schema. Any user can see and access it in schemaless fashion: SELECT * FROM synonym_name.

I also tried to include PUBLIC schema and the synonym is not there either.

I am using version 1.0.2005.1 from June 5th, 2006.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 19-Jun-2006 18:37:02   

Hmm. Synonyms are only checked in the schema(s) retrieved. If it's a public synonym, shouldnt it be in the public schema? (or am I now babbling nonsense?)

Frans Bouma | Lead developer LLBLGen Pro
Kazak1
User
Posts: 39
Joined: 30-May-2006
# Posted on: 19-Jun-2006 19:43:25   

Yes, you are right. It should be in the public schema. I included PUBLIC schema in the designer, but still can't see the synonym.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 19-Jun-2006 20:26:46   

It can be the user you're using to connect to the oracle box has no rights to see the table info of which the synonym is a synonym of, in which the synonym is ignored. Could you check if that's the case?

Frans Bouma | Lead developer LLBLGen Pro
Kazak1
User
Posts: 39
Joined: 30-May-2006
# Posted on: 19-Jun-2006 21:39:56   

select * from all_synonyms s, all_tables t where s.synonym_name = <my synonym name> and t.table_name = s.table_name and t.owner = s.table_owner

This query does return me the table info when I connect as a user I am using in the designer.

Are you using USER_SYNONYMS view? It does not contain PUBLIC synonyms.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 21-Jun-2006 10:54:35   

Kazak1 wrote:

select * from all_synonyms s, all_tables t where s.synonym_name = <my synonym name> and t.table_name = s.table_name and t.owner = s.table_owner

This query does return me the table info when I connect as a user I am using in the designer.

Are you using USER_SYNONYMS view? It does not contain PUBLIC synonyms.

I'm using:


 SELECT * FROM ALL_SYNONYMS 
 WHERE
 TABLE_NAME IN
 (
        SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER=ALL_SYNONYMS.TABLE_OWNER AND 
        TABLE_NAME=ALL_SYNONYMS.TABLE_NAME
 )
 AND OWNER='<currentschema>'

which seems similar to your query.

I'll create a synonym for a table in PUBLIC, and see if I can repro what you're experiencing.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 21-Jun-2006 11:36:04   

Ok, I've created: a table in SCOTT, CURRENCY a public synonym on this table: CREATE PUBLIC SYNONYM P_SCOTT_CURRENCY ... I've granted the user HR all rights on SCOTT.CURRENCY I've created a project with HR and PUBLIC. I can create an entity on PUBLIC.P_SCOTT_CURRENCY without a problem.

It can be the access rights for the user aren't there when the fields for the table of the synonym are retrieved. Could you check that too?

Frans Bouma | Lead developer LLBLGen Pro
Kazak1
User
Posts: 39
Joined: 30-May-2006
# Posted on: 21-Jun-2006 17:37:28   

select *
from
(
            select c.*, s.synonym_name
            from all_synonyms s, all_tables t, all_tab_columns c 
            where s.owner = 'PUBLIC'
            and t.table_name = s.table_name
            and t.owner = s.table_owner
            and t.table_name = c.table_name
            and t.owner = c.owner
)
where synonym_name = <my synonym name>

The query returns columns of the table. Actually, I can't see any objects in PUBLIC schema in the designer.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 21-Jun-2006 18:21:58   

The query I use is: SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT, CHAR_LENGTH FROM ALL_TAB_COLUMNS WHERE OWNER=:schemaOwner AND TABLE_NAME = :tableName ORDER BY TABLE_NAME ASC, COLUMN_ID ASC

So I read the columns directly.

With the user you're using, is it possible to include the original schema of the table which is the target of the synonym? If so, if you do, can you then add an entity mapped onto the table?

When I logon as SCOTT I also don't see any tables in PUBLIC. WHen I login as HR, I do. My Oracle box is a default 9i install with example db's, and here and there some right-change to be able to run tests, but that's about it.

Frans Bouma | Lead developer LLBLGen Pro
Kazak1
User
Posts: 39
Joined: 30-May-2006
# Posted on: 21-Jun-2006 19:30:26   

As a workaround, I have already added the table directly from its owner's schema.

I have Oracle 10g. However, I am using 9i driver for backward compatibility. Driver: Oracle DBDriver (1.0.06012006)

The script I used as follows:

CREATE TABLE USER1.TABLE1...
CREATE PUBLIC SYNONYM SYN_TABLE1 FOR USER1.TABLE1
GRANT SELECT ON USER1.TABLE1 TO USER2

I am connecting as USER2.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Jun-2006 11:15:50   

and if you use grand all ? I used grand all in my tests... Not sure if that's different though.

Anyway, you can always work around this by creating the synonyms in another schema, and at runtime use schema name overwriting to rewrite the schema name of that 'synonym schema' with 'PUBLIC'.

Frans Bouma | Lead developer LLBLGen Pro
Kazak1
User
Posts: 39
Joined: 30-May-2006
# Posted on: 22-Jun-2006 18:15:48   

Is there a way to switch tracing on for the designer so I could see actual queries sent to Oracle?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Jun-2006 10:41:06   

Kazak1 wrote:

Is there a way to switch tracing on for the designer so I could see actual queries sent to Oracle?

Not for the oracle driver, only for the DQE at runtime in your own application. If you want to see the queries sent to the oracle server, please download the SDK from the Extras section in the customer area on our website, it contains the sourcecode for the drivers. simple_smile . Then refer to schemaretriever class.

Frans Bouma | Lead developer LLBLGen Pro
Kazak1
User
Posts: 39
Joined: 30-May-2006
# Posted on: 23-Jun-2006 18:38:44   

I tried the following code, and it does return me the table synonym:


        private IDBTable RertrieveDBTable(string connectionString, string ownerSchema, string tableName)
        {
            OracleCatalogRetriever cr = new OracleCatalogRetriever();

            cr.SchemaNamesToRetrieve.Add(ownerSchema);

            cr.Properties[CatalogElementRetrievalOption.ManualSelectSProcsFromSchema] = true;
            cr.Properties[CatalogElementRetrievalOption.UnattendedRetrieval] = false;

            cr.TaskProgressInitHandler = delegate(int amountOfTasksToComplete) { };
            cr.TaskProgressTaskStartHandler = delegate(string taskDescription) { };
            cr.TaskProgressTaskCompletedHandler = delegate() { };
            cr.SubTaskProgressInitHandler = delegate(int amountOfTasksToComplete) { };
            cr.SubTaskProgressTaskStartHandler = delegate(string taskDescription) { };
            cr.SubTaskProgressTaskCompletedHandler = delegate() { };

            IDBCatalog catalog = new DBCatalog();
            cr.RetrieveSchemas(connectionString, catalog, SchemaFetchFlags.Tables);

            IDBSchema schema = catalog.DBSchemas[ownerSchema] as IDBSchema;
            if (schema != null)
            {
                return schema.Tables[tableName] as IDBTable;
            }
            
            return null;
        }

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Jun-2006 20:15:39   

hmm, though that seems to use existing code so I don't see how it won't show up in the project using the designer...

Frans Bouma | Lead developer LLBLGen Pro
Kazak1
User
Posts: 39
Joined: 30-May-2006
# Posted on: 26-Jun-2006 19:32:35   

How do you build a connection string in the designer? I did the following:


            OracleDBDriver driver = new OracleDBDriver();

            Hashtable connectionParams = new Hashtable();
            connectionParams[ConnectionElement.ServerName] = "TNS NAME";
            connectionParams[ConnectionElement.UserID] = "USER2";
            connectionParams[ConnectionElement.Password] = "PASSWORD";

            string connectionString = driver.ConstructConnectionString(connectionParams);

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 26-Jun-2006 20:33:42   

Why would you need that? Also, the code, did it work, or not and which purpose do you have for the code?

Frans Bouma | Lead developer LLBLGen Pro
Kazak1
User
Posts: 39
Joined: 30-May-2006
# Posted on: 26-Jun-2006 21:13:21   

I wanted to use exactly the same connection string as it is used by the designer. The code worked fine and the purpose was to simulate the designer's behaviour when reading metadata from the database.

I still can't get PUBLIC synonyms shown in the designer though.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 26-Jun-2006 21:38:16   

Kazak1 wrote:

I wanted to use exactly the same connection string as it is used by the designer. The code worked fine and the purpose was to simulate the designer's behaviour when reading metadata from the database.

Please check the driver sourcecode if you're in doubt. De elements are constructed in the connection control of the driver, the connection string code is in the OracleDBDriver.cs file.

I still can't get PUBLIC synonyms shown in the designer though.

Strange, I can here, I'm sure it's some sort of access right thingy. disappointed But I can imagine how frustrating it might be.

Frans Bouma | Lead developer LLBLGen Pro
Kazak1
User
Posts: 39
Joined: 30-May-2006
# Posted on: 26-Jul-2006 22:32:49   

It is even more frustrating in version 2.0.0.0 (July 21). Now refresh catalog operation fails with "ORA-01013: user requested cancel of current operation" exception when I include PUBLIC schema.

Is it a timeout? PUBLIC schema has a lot of objects in it.

-----[Core exception]-------------------- at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.DataAccess.Client.OracleCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at SD.LLBLGen.Pro.DBDrivers.Oracle10g.OracleSchemaRetriever.PopulateAllSynonymTableFields(OracleConnection openOracleConnection) at SD.LLBLGen.Pro.DBDrivers.Oracle10g.OracleSchemaRetriever.RetrieveSchema(String connectionString, IDBSchema schemaToRetrieve, SchemaFetchFlags fetchFlags) at SD.LLBLGen.Pro.DBDrivers.Oracle10g.OracleCatalogRetriever.RetrieveSchemas(String connectionString, IDBCatalog catalogToRetrieve, SchemaFetchFlags fetchFlags) at SD.LLBLGen.Pro.DBDrivers.Oracle10g.OracleDBDriver.PopulateSelectedCatalogs(Hashtable callBacks, Hashtable connectionElements, SchemaFetchFlags fetchFlags, Hashtable properties) at SD.LLBLGen.Pro.Gui.Forms.MainWindow.RefreshCatalogs(Boolean unattended, Hashtable catalogsToRefresh) at SD.LLBLGen.Pro.Gui.Forms.MainWindow.OnProjectExplorerCatalogRefreshClicked(Object sender, CatalogRefreshEventArgs e) at SD.LLBLGen.Pro.Gui.Forms.ProjectExplorer._menuItemCatalogRefresh_Click(Object sender, EventArgs e) at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e) at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e) at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ScrollableControl.WndProc(Message& m) at System.Windows.Forms.ToolStrip.WndProc(Message& m) at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 26-Jul-2006 23:07:00   

yes public will likely timeout the query. You can set the timeout in the preferences though to give it more time simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Kazak1
User
Posts: 39
Joined: 30-May-2006
# Posted on: 26-Jul-2006 23:48:56   

I set the timeout to 5 minutes and the process failed after 5 minutes. I think 5 minutes should be sufficient, shouldn't it? wink

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 27-Jul-2006 10:53:46   

It executes for every synonym found the field retrieval query. This can be quite slow if you have a truckload of public synonyms. 5 minutes is indeed a long time, but retrieving thousands and thousands of synonym's fields requires a lot of time too, and you don't want that I think. Define the synonyms in another schema, add that to your project, use at runtime name overwriting tmpschemaname -> PUBLIC.

Frans Bouma | Lead developer LLBLGen Pro
1  /  2