Use OracleCommandBuilder with database linked objects

ddeml77 · Post by **ddeml77** » Fri 25 Jan 2008 12:11

Hello,

I have the following command as OracleDataAdapter.SelectCommand:

select * from COUNTRIES@HR

HR is the name of the database link which connects to the equally named schema shipped with OracleXE.
I successfully used the Fill() method to fetch the countries into a DataSet.
Then I initialized an OracleCommandBuilder for generating Insert/Update/Delete statements and tried to use the Update() method.

While this works perfectly with Tables in the same schema and even tables of another schema, it does not seem to work with database objects of another schema.

Does anyone have an idea how to circumvent this issue, except manually creating the Insert/Update/Delete statements?

Thanks for any help!

Greetings - Richie

Alexey.mdr · Post by **Alexey.mdr** » Fri 25 Jan 2008 12:44

Please send me a small test project to reproduce the problem.
It is desirable to use 'test' schema objects, otherwise include the
definition of your own database objects.

Do not use third party components.
If it is impossible for you to create the test project, send us a piece of
your code where the error occurs.

ddeml77 · Post by **ddeml77** » Fri 25 Jan 2008 15:37

Hello Alexey

You can find a test project here.

The file schema.sql creates a test table and a database link to itself. It is supposed to be executed on an empty schema named SCOTT of a local Oracle XE instance.
You may need to adapt the connection string in the Program.cs and the connection information in the database link to your environment.

If I run this test program, an exception is thrown in Program.cs line 37.

Greetings - Richie

Alexey.mdr · Post by **Alexey.mdr** » Fri 25 Jan 2008 16:00

We could not reproduce the error on OraDirect .NET 4.35.28
Could you post the version of OraDirect .NET installed on your machine?

ddeml77 · Post by **ddeml77** » Mon 28 Jan 2008 13:27

Hello Alexey,

I updated the test project so that it uses the assemblies i added in the lib folder.

Also, i added a ConsoleDump.txt that shows a dump of the console output as i get it. The english translation of the exception message is: "Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information."

I'll check if OraDirect .NET 4.35.28 will work with this test program in my environment.

Greetings - Richie

Alexey.mdr · Post by **Alexey.mdr** » Tue 29 Jan 2008 14:34

We are investigating this problem.
I'll let you know our progress.

ddeml77 · Post by **ddeml77** » Tue 29 Jan 2008 14:50

Hello Alexey,

I forgot to inform you that the problem is indeed fixed in version 4.35.28. I am able to update our application with this version, so from my point of view, this issue is solved.

Thank you very much!

Greetings - Richie

Alexey.mdr · Post by **Alexey.mdr** » Tue 29 Jan 2008 15:49

Sounds good!