Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL server to Oracle

Posted on 2002-07-29
15
Medium Priority
?
1,142 Views
Last Modified: 2007-12-19
 i have a system which is developed using vb and connected to Microsoft SQL Server database. it is fine. but now i need to make the system compatible with oracle.

i hit a problem when i try to update a field of a recordset that select records from 2 or more table using this sql :
 
set rs = Conn.Open("Select Table1.column1 from Table1, Table2 where Table1.Column2 = Table2.column2")
 
when i do a vb statement like this:
    rs!column1 = "1"

I get this error message.
    "Run-time error '-2147467259 (80004005)'
     ROW-00009: Cannot update row in a read only rowset"

i didn't get this message if i select the record from only 1 table. i know i can solve this if i change the sql but i will need to change a lot of codes for that.

i have already try using different cursor type and different lock type for vb but it still doesn't works. any help will be much appreciated. thanks.
0
Comment
Question by:max1978ex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
  • +3
15 Comments
 
LVL 53

Accepted Solution

by:
Ryan Chong earned 200 total points
ID: 7184585
Remember that some recordset is not updatable.

Any way try this instead:

Conn.Execute "Update Table1 Inner Join Table2 on Table1.column1 = Table2.column2 Set Table1.Column1 = 1"
0
 
LVL 3

Expert Comment

by:sanjaykattimani
ID: 7185026
Yes use update command which is compatible to both sql server and oracle.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7185175
One thing to keep in mind is that a recordset's abilities are controlled to a great extent by the "provider" that is being used.  So, switching from SQL Server's OleDB provider to Oracle's OleDB provider means that certain recordset methods may not be allowed in certain circumstances.  You can even find differences if you switch between the native OleDB provider and the ODBC OleDB provider, or between Microsoft's OleDB provider for Oracle and Oracle's OleDB provider for Oracle....

Just because you ask for an updateable recordset doesn't mean that the recordset will be opened as updatable. The exact properties of the recordset (such as locktype and cursortype) may be changed to the closest thing that the provider you are using supports, which might be different from what you have requested.

You will have to do some testing with SQL like you have above using different combinations of providers and recordset properties to see if you can get the recordsets to be updatable.

Some things to try, try setting the recordset's cursor type to adOpenKeyset instead of adOpenDynamic (I'm guessing at the constants here).  Try making sure that your select statement includes the primary key column of the table that you plan to update.  Make sure that the table you will be updating does have a primary key defined.

For each of the different combinations of things that you try, try switching between the Microsoft OleDB Provider for Oracle, the Oracle OleDB Provider for Oracle and the ODBC OleDB Provider.  You can see if the recordset was opened to be updateable by checking the recordset's Supports property:

RS.Open
If RS.Supports(adUpdate) then
   msgbox "I have an updateable recordset"
End if
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:jayeshshah
ID: 7185588
is it compulsory to give an equijoin statement. i think you can open the recordset using 1 table itself.
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 7185638
ryancys is correct
where Table1.Column2 = Table2.column2 - creates readonly recordset, you need to use join

but update command should be faster and better.

0
 

Author Comment

by:max1978ex
ID: 7187214
i'm sorry to say that i must use the join because that's the sql i give is simplified. there are other criteria that i need to select the records.

from what i can see here is the recordset is only read only. what i really want to know is are there any ways to work around the problem without changing the sql statement? this is because the system that i'm trying to convert is quite large. if i were to change the SQL, it would mean i have to rewrite the program all over again.

thanks.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7187699
Have you tried the suggestions that I gave?  This is your only hope of not having to change the SQL (you might have to anyway, but it's a chance).

It is possible to have updateable recordsets that are the product of a join, but most likely only one of the join's table's columns will be updateable (in this case, it looks OK).

However, you are going to have to experiment.  Can you show the properties that you are setting for the recordset when you open it?  Specifically, what are you setting the CursorLocation, LockType, and CursorType to?  Can you show what you are using as a connect string when opening your Connection?

As I said, using adOpenKeyset may help make your recordset updateable, however it might need to be in combination with some other settings.

Either way, I suspect that you will have to modifiy your program to some extent to port it to Oracle.
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 7187924
>>>there are other criteria that i need to select the records.



Regular where statements should be ok like field a= 1 and field = 2
0
 

Author Comment

by:max1978ex
ID: 7189573
i have tried Cursor type adOpenKeyset and adOpenDynamic, CursorLocation = adUseServer and adUseClient, LockType = adLockPessimistic and adLockOptimistic. it still doesn't works.

the connection string i use is :
"Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=scott;Password=tiger;Data Source=Test"

by the way do u know any connection string that i can use to specify the server and the database name instead of the Service name as the data source?
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7189663
Most Oracle connect strings require the service name.  The only way around it I know of is to use the OleDB provider for ODBC, and just specify a DSN name.  However, in the ODBC DSN, I think you will still have to specify the Service name.  This is the only way that the SQLNet layer can communicate with the program layer.

So, you have tried each of those things, but you have only tried them with the Oracle OleDB provider for Oracle.  You should try the provider, I think it is MSDAORA which is the Microsoft OleDB provider for Oracle.  Or, there is also, I think, MSDASQL (you'd have to look that up), which is the OleDB provider for ODBC.  Then, you have to try each of the combinations again with each of these providers.

It's not enough to just try switching from adOpenKeyset to adOpenDynamic, you have to try every combination (adOpenKeyset with adUseClient and adLockOptimistic, adOpenKeyset with adUseServer and adLockOptimistic, adOpenKeyset with adUseClient and adLockPessimistic etc.)

It is possible, that you will find a combination that will let you do this particular update.

However, because recordset updates are so particular to the Provider and underlying DBMS, most people design their applications to use SQL Updates and Inserts and Deletes.  This avoids all such problems.  If you find that you are not able to update the recordset, then, unfortunately, you should bite the bullet and rewrite a lot of your code to issue the SQL updatates as ryancys first suggested.

0
 

Author Comment

by:max1978ex
ID: 7190164
can anyone tell me is it possible to connect to oracle database from a vb program without installing oracle on the client?
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7190332
Well, to connect to Oracle requires the client to be able to run SQLNet which is part of the Oracle Binaries.  They don't absolutely have to be installed on the client machine, however, there are many registry entries that must be made on the client machine to tell the client where the Oracle binaries are located, and these registry entries are usually made during the installation of the Oracle Client software.

What you can do is this.  Install the Oracle Client software on a network server.  Then, export the Oracle registry tree (I think in HKey_Local_Machine\Software) out to a .reg file.  Edit the .reg file and change any path information that is there, which might look like C:\Oracle\Bin to either a drive letter that all client computers will map to (say G:) or to a UNC name:
\\MyServer\Oracle\Bin

Then, go to each client and double-click this reg file to merge these Oracle registry entries with their registries.

You will also need to edit the environment of each client computer to include the Oracle directories in the Path variable.  Go back to the server where you installed the Oracle Client, copy the Path entries that Oracle made there, and then edit each client to include those Path statements (adjusted as with the paths mentioned above)

The only thing that I couldn't get to work off the network computer was the Oracle ODBC driver.  ODBC would only look for this file in the Window's system directory, even if I set up the Path to point to the network server.

Now, this doesn't mean that it's legal to do this.  That's up to you to figure out.  I'm sure Oracle would prefer that you pay for each client who uses the Oracle Client Binaries......
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 7190469
>>can anyone tell me is it possible to connect to oracle database from a vb program without installing oracle on the client?

You need to develop a client-server application.
In this case you need to install SQLNet only on the server.
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 7190581
p.s you don’t need to register the physical component on a client machine.
You need to register some information about server component on the client pc.


p.s if you have an activex dll on the server you need to use mts or com+ to host it.




This article details a method that can be used to ensure that your client application correctly traps for and resolves error 429 at run-time if the ActiveX DLL is present on the machine but not correctly registered.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q173407

This article demonstrates how to use the CLIREG32.EXE utility to manually register remote Visual Basic components on the client machine.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q194636
0
 

Author Comment

by:max1978ex
ID: 7195335
i have tried the suggestions u all gave, but it still doesn't work. i think in the end i will need to change the sql any way.

about not installing oracle on the client side, i think in the end i will install it anyway because the if i had to change the registry everytime i install my app, the i will need to do the installation for the client every time a new installation is required.

thanks for all the help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question