Solved

SQL server to Oracle

Posted on 2002-07-29
15
1,043 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
  • 4
  • 4
  • 4
  • +3
15 Comments
 
LVL 49

Accepted Solution

by:
Ryan Chong earned 100 total points
Comment Utility
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
Comment Utility
Yes use update command which is compatible to both sql server and oracle.
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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
 
LVL 5

Expert Comment

by:jayeshshah
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:rkot2000
Comment Utility
>>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now