8 Replies Latest reply: Mar 25, 2014 5:03 AM by CyBorg RSS

    VB Connection Problem (32 bit vs 64 bit)

    Girish Sharma

      Hi,

       

      After struggling and googling a lot, I think now I should knock this door !

       

      My Test Machine :

      OS : Windows 7 64 bit Home Premium

      Oracle Database : 11.2.0.1 64 bit Server (I don't have installed client software, because I have installed Server version)

      Visual Basic : For 32-bit Windows Development

       

      Below Code in AA.VBS Works fine when I runs cmd by saying : Click Start, click Run, type %windir%\SysWoW64\cmd.exe, and then click OK.

      and then :

       

      C:\Windows\SysWOW64>Wscript C:\AA.VBS

       

      Private Sub Form_Load()

      Dim Cn

      Dim constr

      Dim rs

      Set cn = CreateObject("ADODB.Connection")

      Set rs = CreateObject("ADODB.Recordset")

      constr = "Provider=OraOLEDB.Oracle;Data Source=orcl;User ID=un;Password=pw;"

          cn.Open constr

          cn.Close

          rs.Close

      End Sub

       

      It worked.  No message nor any error.  Ok.

       

      When I says same code in Visual Basic :

      I gets :

      Run-time error '3706'

      Provider cannot be found.  It may not be properly installed.

       

      I guess my ADO (VB 32 Bit software part) is not able to see / access the 64 bit OraOLEDB.Oracle provider.  I have tried many other providers and many other codes (by googling "Provider cannot be found.  It may not be properly installed.") which are mentioned in the different internet sources, but issue is 32 Bit ADO is not able to work with 64 bit Provider and thats seems me a basic problem.  I am fully convinced that Oracle 11.2.0.1 is not certified for Windows 7 and when we works with uncertified environment, there are and "should" issues, but at the same time, if anyone can help me to get it resolved or any clue because when I have 64 bit OS and 64 bit database software then issue remains only for 32 bit software's (VB6) ADO capability to work with 64 bit provider and/or ODBC; which seems me not a big deal, but yes at this time it is big for me though...

       

      @ Moderator :

      I know this is not correct forum; I should write in Oracle Provider for OLE DB, but that seems me less activity over there, so I am trying to get help here.

       

      Regards

      Girish Sharma

        • 1. Re: VB Connection Problem (32 bit vs 64 bit)
          sybrand_b

          Windows 7 bit *Home Premium* says it all.
          Rhetorical question: Did Oracle ever certify any edition of Oracle against any edition of Windows Home?

          No they didn't as there are architectitural differences between Windows Home and Windows Professional/Ultimate

          (I know John Watson says it works, but still it ISN'T certified, and John Watson is NOT going to assume responsibility for any of your issues)

           

          Also what you describe seems to indicate Oracle is not the cause but the culprit, so you might be barking at the wrong tree.

           

          @Moderator: It seems to me more and more people are using the General RDBMS forum as a generic junkjard for ALL of their questions, whether it be RMAN, Linux, shell scripting or whatever.

          Can something be done about this? Also can something be done about the high percentage (> 95 percent) of RTFM questions?

           

          ------------

          Sybrand Bakker

          Senior Oracle DBA

          • 2. Re: VB Connection Problem (32 bit vs 64 bit)
            Girish Sharma

            I would like to say "No Comments" because its best; otherwise purpose of thread may be lost for sure.

            • 3. Re: VB Connection Problem (32 bit vs 64 bit)
              cleavitt

              A 32bit application using OleDb will require a 32bit OleDb provider.  You only have the 64bit Oracle client components installed (it installs as part of the server install).  The "provider not found" message is because the OleDb layer is looking for a 32bit provider to link to your 32bit application.  When you run the code as a VBS script with the command prompt it is being run by a 64bit interpreter which is why it works.

               

              You either need to ...

               

              - Set your project to compile to 64bit binaries (doesn't sound like an option with the tools you are using).

              - Install a 32bit OleDb provider for Oracle (Oracle Client).

               

              You can download a 32bit Oracle client and install it on the same system along side the 64bit server.  It is very common to have 32bit clients connecting to a 64bit server, in your case the client and server just happen to be on the same machine.

              • 4. Re: VB Connection Problem (32 bit vs 64 bit)
                jgarry

                sybrand_b wrote:

                 

                Also can something be done about the high percentage (> 95 percent) of RTFM questions?

                 

                One has to wonder that you haven't figured the answer to this out yet (he says while looking at an RTFM answer from Sybrand from the last century)

                • 5. Re: VB Connection Problem (32 bit vs 64 bit)
                  Girish Sharma

                  Thank you for your reply.

                  >Install a 32bit OleDb provider for Oracle (Oracle Client).

                  Yes, I tried by this way :

                  1. Downloaded ODAC112012Xcopy.zip from http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html

                  2. Extracted it to E:\E:\ODAC112012Xcopy

                   

                  C:\Windows\system32>e:

                   

                  E:\>cd ODAC112012Xcopy

                   

                  E:\ODAC112012Xcopy>dir

                  Volume in drive E is HCL_DISK3

                  Volume Serial Number is A62A-F491

                   

                  Directory of E:\ODAC112012Xcopy

                   

                  15-02-2014  10:37    <DIR>          .

                  15-02-2014  10:37    <DIR>          ..

                  15-02-2014  10:37    <DIR>          asp.net

                  15-02-2014  10:37    <DIR>          asp.net4

                  20-07-2010  17:00            27,307 configure.bat

                  13-07-2010  18:43            12,350 install.bat

                  15-02-2014  10:37    <DIR>          instantclient_11_2

                  15-02-2014  10:37    <DIR>          odp.net20

                  15-02-2014  10:37    <DIR>          odp.net4

                  15-02-2014  10:37    <DIR>          oledb

                  15-02-2014  10:37    <DIR>          oo4o

                  15-02-2014  10:37    <DIR>          oramts

                  20-07-2010  17:00             6,684 readme.txt

                  20-07-2010  17:00            13,772 unconfigure.bat

                  29-07-2010  17:32            20,562 uninstall.bat

                                 5 File(s)         80,675 bytes

                                10 Dir(s)  61,593,464,832 bytes free

                   

                  E:\ODAC112012Xcopy>install.bat all c:\oracle odac

                   

                  E:\ODAC112012Xcopy>configure.bat oledb E:\app\serverroom\product\11.2.0\dbhome_1

                   

                  E:\ODAC112012Xcopy>

                   

                  3. Restarted the machine.

                   

                  Now tried to run the above VB code, but problem is still there.  Can you please help me where I am missing.

                   

                  Regards

                  Girish Sharma

                  • 6. Re: VB Connection Problem (32 bit vs 64 bit)
                    Girish Sharma

                    Finally I got the solution.  Let me elaborate what I did and have :

                    1.Oracle 11.2.0.1 64 bit database

                    2.Windows 7 Home 64 bit (Unsuported OS) !!

                    3.ODAC (I tried, but same problem Provider not found).  Then I downloaded and installed;

                    http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

                    win32_11gR2_client.zip (684,581,290 bytes)

                    4.Oracle 32 bit Client Software (in different client home directory). I opted Custom Installation option and checked marked on select all option button.  At the time of instllation; I got one perquisite check failed related to path variable; something like long path variable, so I ignored it and moved ahead.  Installation completed and successfully.

                    5.Now tried with above code....Bingo....Provider not found went away; but ORA-12154... Huh.. ? Why, when I am able to access the db by saying sqlplus scott/tiger@orcl then what is the problem with VB ?  I searched a lot, but no luck... I went my home; because its office closing time, so I shut down the pc.

                    6.New day morning, after started the db and listener, I just tried to re-run the above code, I got ORA-12557...Huh ? What is this mystery; but I took as an interesting question and again google.... There I found to something have the correct path variable i.e. first db home and then client home.. I corrected it, but no luck..  Then I got a link (I forgot what was that, but it was saying to just remove oracle_home environment variable by right click computer--properties--Advanced System Setting--Environment Variable--System Variable--Oracle home and clicked on delete button)

                    7.Now tried above VB code.... Ah.. a... What a cool, after all success....

                    8.So, I don't have oracle_home environment variable

                    9.This is my path variable :

                    C:\Windows\system32>echo %path%

                    E:\app\serverroom\product\11.2.0\dbhome_1\bin;E:\app\serverroom\product\11.2.0\client\BIN;C:\Windows;C:\Windows\System32;......

                    10.This is my oracle_sid :

                    C:\Windows\system32>echo %oracle_sid%

                    orcl

                     

                     

                    11.And no oracle_home

                    C:\Windows\system32>echo %oracle_home%

                    %oracle_home%

                     

                    So, one line conclusion is :

                    Install Oracle 32 bit client software, restart the pc (to avoid ORA-12154), delete oracle_home environment variable (to avoid ORA-12557) and you done.

                     

                    Regards

                    Girish Sharma

                    • 7. Re: VB Connection Problem (32 bit vs 64 bit)
                      CyBorg

                      A very fine and thorough explanation, Girish, and I've tried to follow it to the letter.

                       

                      1) I downloaded and installed the above 32 bit client in its own, brand new directory (client_3)

                      2) I checked the path variable and the client_3/bin is first.

                      3) I ran regsvr32 against the ORAOLEDB11.DLL in the client_3 directory just to make sure it was correctly registered.

                       

                      When I try to log on with the VB app I still get the 3706 error.

                       

                      I also notice I get a syntax error when I try to use Provider=ORAOLEDB.oracle.  The only reference I get is ORAOLEDBLIB.ORAOLEDB, and that doesn't do anything that I can use...

                       

                      I find the provider when I run the 32 bit ODBC administrator but that's it...

                       

                      Please, somebody, have a clue here before I tear my hair out... :-(

                       

                      Regards

                       

                      Carl Borgstrom