Photo Gallery Member List Search Calendars FAQ Ticket List Log Out


About connecting to Oracle

  		Logged in as: Guest
arrSession:exec spGetSession 2,2,25756
Active Users: There are 0 members and 0 guests.
Users viewing this topic: none
 

 

  
 
  Printable Version
All Forums >> [Scripting] >> WSH & Client Side VBScript >> About connecting to Oracle
  Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks! 		Page: [1]
Login
Message << Older Topic   Newer Topic >>
About connecting to Oracle - 9/8/2005 7:25:11 PM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		Hi,
Suppose that I want to have a connection to Oracle in the below way, before that, should I just create a data source name like 'ADODB', through 'ODBC Data Source Administrator'?

   Dim AdConnection As ADODB.Connection
   Dim rsadSet As ADODB.Recordset

But even if I do in such way, I have still got a problem, like
'User-defined type not defined'

with the codes



_____________________________

Thanks & Best Regards,

HuaMin Chen
 
 
Post #: 1
 
RE: About connecting to Oracle - 9/8/2005 7:36:11 PM   
  Zifter


Posts: 318
Score: 0
Joined: 1/5/2005
From: Belgium
Status: offline 		The DIM xxx AS xxx statements don't work in VbScript.
All the variables in VbScript are of the type Variant. You can't specify wich type of variable you're declaring.

I think it should be something like this:

Dim AdConnection
Dim rsadSet

Set AdConnection = CreateObject("ADODB.Connection")
Set rsadSet = CreateObject("ADODB.Recordset")

...



HTH

(in reply to wmec)
 
 
Post #: 2
 
RE: About connecting to Oracle - 9/8/2005 8:01:08 PM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		Hi,
It's OK now and thanks very much.

Now I'm with another problem for '.Open' to the following:

  AdConnection.ConnectionString = AdConnString
  With AdConnection
  If .State <> 0 Then .Close
  .Open
  End With
  rsadSet.Open strSQL, AdConnection, adOpenStatic

_____________________________

Thanks & Best Regards,

HuaMin Chen

(in reply to Zifter)
 
 
Post #: 3
 
RE: About connecting to Oracle - 9/8/2005 8:21:58 PM   
  Zifter


Posts: 318
Score: 0
Joined: 1/5/2005
From: Belgium
Status: offline 		I can't see any problem in your code. Syntactic it is correct.
Are you sure the variables AdConnString and strSQL contain correct values?
Do you get an error?

(in reply to wmec)
 
 
Post #: 4
 
RE: About connecting to Oracle - 9/8/2005 8:33:33 PM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		Hi,
Here are the complete codes:

  addbname = "iqtest2"
  addbserver = "vsiqship.fe.corp.zim.com"
 
  Dim strSQL As String
  Rem -- The Next Line sets up the SQL to whatever you want
  strSQL = "Select customer,customer_name from customer_table where rownum<=10"
 
  AdConnString = ";Provider=" & DBProvider & ";Password=" & pswd & _
  ";Persist Security Info=True;User ID=" & usrid & _
  ";Data Source=" & addbserver & _
  ";Initial Catalog=" & addbname
 
  AdConnection.ConnectionString = AdConnString
  With AdConnection
  If .State <> 0 Then .Close
  .Open
  End With
  rsadSet.Open strSQL, AdConnection, adOpenStatic

I'm with a problem for the last 3rd line and the error says 'Provider cannot be found. It may not be propertly installed'.

Actually "iqtest2" is the Oracle DB for which I've created a data source named "IQTEST2" within ODBC Data Source Administrator and it's been tested successfully. And "vsiqship.fe.corp.zim.com" is the server name of Oracle DB, which I usually use in Oracle 'TNSNAMES.ORA' file.

_____________________________

Thanks & Best Regards,

HuaMin Chen

(in reply to Zifter)
 
 
Post #: 5
 
RE: About connecting to Oracle - 9/8/2005 10:02:10 PM   
  Zifter


Posts: 318
Score: 0
Joined: 1/5/2005
From: Belgium
Status: offline 		Although I'm not familiar with Oracle, looking at your code and the error description, I think your variable DBProvider is not filled in (I can't see where you fill in the pswd and usrid variables either, but I assume you left them out for security reasons).

Does it work if you add the following statement at the beginning of your script?

DBProvider = "MSDAORA"

(in reply to wmec)
 
 
Post #: 6
 
RE: About connecting to Oracle - 9/11/2005 12:51:00 PM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		Hi,
I've still got the error below:

Run-time error - 2147217887 (80040e21):

Mutiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

And I'm also with both the user ID and the password when trying to connect to Oracle.

Or Zifter, I can provide you with my Excel file as well.

< Message edited by wmec -- 9/11/2005 12:52:39 PM >

_____________________________

Thanks & Best Regards,

HuaMin Chen

(in reply to Zifter)
 
 
Post #: 7
 
RE: About connecting to Oracle - 9/11/2005 7:40:44 PM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		Can anyone pls help me?

(in reply to wmec)
 
 
Post #: 8
 
RE: About connecting to Oracle - 9/11/2005 8:36:05 PM   
  Zifter


Posts: 318
Score: 0
Joined: 1/5/2005
From: Belgium
Status: offline 		An Excel file? I don't understand what an Excel files has got anything to do with your problem?
... Or are you writing a VBA program instead of VbScript?
Anyhow, I don't think it will be of any use to send it to me. I don't know much about Oracle and I don't have it, so I won't be able to test it anyway.
Maybe you can post your script here on the forum and everybody can have a look if they see anything that needs to be changed.


<edit>
I have a script that I use to connect to a MS SQL DB. I changed the connection string and query according to your specifications. Can you test this script and post your findings?

Option Explicit

Dim objADOConnection
Dim objADORecordSet
Dim strSQLCommand

Set objADOConnection = CreateObject("ADODB.Connection")
Set objADORecordSet = CreateObject("ADODB.Recordset")

strSQLCommand = "Select customer,customer_name " & _
                         "from customer_table " & _
                         "where rownum<=10"

objADOConnection.Open "Provider='MSDAORA';" & _
                                    "Initial Catalog='iqtest2';" & _
                                    "Data Source='vsiqship.fe.corp.zim.com';" & _
                                    "User ID='xxxx';" & _
                                    "Password='xxxx';"

objADORecordSet.Open strSQLCommand,objADOConnection,adOpenStatic

Do Until objADORecordSet.EOF
   WScript.Echo objADORecordSet.Fields("customer_name")
   objADORecordSet.MoveNext
Loop

objADORecordSet.Close
objADOConnection.Close

Set objADORecordSet = Nothing
Set objADOConnection = Nothing


< Message edited by Zifter -- 9/11/2005 8:57:54 PM >

(in reply to wmec)
 
 
Post #: 9
 
RE: About connecting to Oracle - 9/12/2005 12:30:36 PM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		Hi,

Here are my codes:

Dim objADOConnection
Dim objADORecordSet
Dim strSQLCommand
Set objADOConnection = CreateObject("ADODB.Connection")
Set objADORecordSet = CreateObject("ADODB.Recordset")
strSQLCommand = "Select customer,customer_name " & _
                        "from customer_table " & _
                        "where rownum<=10"
objADOConnection.Open "Provider='MSDAORA';" & _
                                   "Initial Catalog='iqtest2';" & _
                                   "Data Source='vsiqship.fe.corp.zim.com';" & _
                                   "User ID='" & usrid & "';" & _
                                   "Password='" & pswd & "';"
objADORecordSet.Open strSQLCommand, objADOConnection, adOpenStatic
Do Until objADORecordSet.EOF
  WScript.Echo objADORecordSet.Fields("customer_name")
  objADORecordSet.MoveNext
Loop
objADORecordSet.Close
objADOConnection.Close


I've still got the same error below, towards that bold statement:

Run-time error - 2147217887 (80040e21):

Mutiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

_____________________________

Thanks & Best Regards,

HuaMin Chen

(in reply to Zifter)
 
 
Post #: 10
 
RE: About connecting to Oracle - 9/12/2005 6:23:24 PM   
  Zifter


Posts: 318
Score: 0
Joined: 1/5/2005
From: Belgium
Status: offline 		I got the same error, but I don't have Oracle.
So probably there is something wrong with your DB name (Data source)

Can you try the following:
Remove the Initial Catalog parameter and change the Data Source parameter into "vsiqship.fe.corp.zim.com\iqtest2"

objADOConnection.Open "Provider='MSDAORA';" & _ 
                                  "Data Source='vsiqship.fe.corp.zim.com\iqtest2';" & _
                                  "User ID='" & usrid & "';" & _
                                  "Password='" & pswd & "';"


HTH

(in reply to wmec)
 
 
Post #: 11
 
RE: About connecting to Oracle - 9/12/2005 7:36:25 PM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		Hi,
The things are improved now and thanks very much.

Now I've got such problem:

Run-time error '-2147467259 (80004005)':

ORA-12154: TNS: could not resolve service name

with the codes below (towards the bold statement):
Dim objADOConnection
Dim objADORecordSet
Dim strSQLCommand
Set objADOConnection = CreateObject("ADODB.Connection")
Set objADORecordSet = CreateObject("ADODB.Recordset")
strSQLCommand = "Select customer,customer_name " & _
                      "from customer_table " & _
                      "where rownum<=10"
objADOConnection.Open "Provider='MSDAORA';" & _
                                 "Data Source='vsiqship.fe.corp.zim.com\iqtest2';" & _
                                 "User ID='" & usrid & "';" & _
                                 "Password='" & pswd & "';"
objADORecordSet.Open strSQLCommand, objADOConnection, adOpenStatic
Do Until objADORecordSet.EOF
WScript.Echo objADORecordSet.Fields("customer_name")
objADORecordSet.MoveNext
Loop
objADORecordSet.Close
objADOConnection.Close
Set objADORecordSet = Nothing
Set objADOConnection = Nothing


Additionally, 'vsiqship.fe.corp.zim.com' is the database server name, and 'iqtest2' the database service name.

For such error, I'm actually able to connect to Oracle DB through SQL*Plus.

I can even research this deeply in the Oracle forum.

< Message edited by wmec -- 9/12/2005 7:44:16 PM >

_____________________________

Thanks & Best Regards,

HuaMin Chen

(in reply to Zifter)
 
 
Post #: 12
 
RE: About connecting to Oracle - 9/13/2005 12:11:18 AM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		Can anyone pls help me?

(in reply to wmec)
 
 
Post #: 13
 
RE: About connecting to Oracle - 9/13/2005 12:11:03 PM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		I've succeeded in that with the below statement

   objADOConnection.Open "Provider=OraOLEDB.Oracle;" & _
                                   "Data Source=iqship;" & _
                                   "User ID=" & usrid & ";" & _
                                   "Password=" & pswd & ";"

But how can we detect that anything wrong has happened, such as wrong User ID or password, with the connection?

< Message edited by wmec -- 9/13/2005 1:41:27 PM >

_____________________________

Thanks & Best Regards,

HuaMin Chen

(in reply to wmec)
 
 
Post #: 14
 
RE: About connecting to Oracle - 9/14/2005 2:53:48 PM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		Does anyone have any ideas to this?

(in reply to wmec)
 
 
Post #: 15
 
RE: About connecting to Oracle - 9/19/2005 2:44:23 PM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		Hi,
I want to know if there is a limit for the length of 'strSQLCommand' for the below statement.

objADORecordSet.Open strSQLCommand, objADOConnection, adOpenStatic

as I've got a problem with the long query I created, inside that.


Or instead, there should be a way for which we can execute a stored-procedure in the Oracle DB directly from the macro, shouldn't there? I think we can run this through 'System.Data.OracleClient', right?

< Message edited by wmec -- 9/19/2005 9:18:56 PM >

_____________________________

Thanks & Best Regards,

HuaMin Chen

(in reply to wmec)
 
 
Post #: 16
 
RE: About connecting to Oracle - 11/15/2005 9:15:22 PM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		Hi,
For instance, we are going to execute a procedure inside Oracle DB, from Excel macro, by defining this:

objCommand.CommandText = "{call Emp_RefCur_A.EmployeeSearch(?,?)}"

What datatype should we define in Excel, for referring to a recordset, that is a parameter of the procedure?

Thks & Rgds,
HuaMin

(in reply to wmec)
 
 
Post #: 17
 
RE: About connecting to Oracle - 11/16/2005 3:42:12 PM   
  wmec

 

Posts: 125
Score: 0
Joined: 8/5/2004
From: Hong Kong, China
Status: offline 		Hi,
Does anyone have any ideas regarding this?


_____________________________

Thanks & Best Regards,

HuaMin Chen

(in reply to wmec)
 
 
Post #: 18
 
RE: About connecting to Oracle - 11/17/2005 7:12:28 PM   
  TNO


Posts: 1302
Score: 12
Joined: 12/18/2004
From: thenewobjective.com
Status: offline 		Oracle has been a nightmare for me as well.  I've gotten most of my help on the subject from here:

http://www.webdeveloper.com/

The people there are alot more knowledgeable on the Oracle subject.

_____________________________

To iterate is human, to recurse divine. -- L. Peter Deutsch

(in reply to wmec)
 
 
Post #: 19
 
 
 
 

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.
All Forums >> [Scripting] >> WSH & Client Side VBScript >> About connecting to Oracle Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts




Forum Software © ASPPlayground.NET Advanced Edition 2.5.5 ANSI