Cannot connect to oracle using ODBC

[mfauzim]

I am trying to connect to oracle and pull data using odbc, then load the data to another table in another database using odbc.

based on sample in the forum I comeup with this but it seem do nopt work. Here is the code:


VBSTART

Dim SQLString
Dim MyDB
Dim rsRTN

Sub OpenRecordSet
set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "DSN=EQUESTRTN,Uid=ertn01,Pwd=ertn01"

SQLString = "select * from atlas.ERTN_TOSRTN_INP_PRE"
set rsRTN = MyDB.Execute(SQLString)
rsRTN.MoveFirst
End Sub

Sub CloseDB
MyDB.Close
End Sub

Function GetNextRecord
If Not rsRTN.EOF then
GetNextRecord = rsRTN.Fields("ACTION_CODE") & ";" & _
rsRTN.Fields("TYPE_OF_SERV") & ";" & _
rsRTN.Fields("ACCOUNT")
rsRTN.MoveNext
else
GetNextRecord = 0
end if
End Function

VBEND

// start macro script
VBRun>OpenRecordSet
Label>ReadLoop
VBEval>GetNextRecord,record
if>record=0,doneloop
Separate>record,;,fields
MessageModal>%fields_1%, %fields_2%, %fields_3%
Goto>ReadLoop
Label>doneloop
VBRun>CloseDB


Question:
1. How do I know if the connection/select is succesful?. The above code will show a dialog box with the data, but it does it shows %field1%,%field2 etc
2. How do I load the data to another table in another db?

[Marcus Tettmar]

Have you got the connection string right:
http://www.connectionstrings.com/

If the data source wasn't found you'd get an error thrown up. So if you have no error it probably has connected. So if there's no data then your SQL or field names must be wrong.

[mfauzim]

My last script has some error, My amaneded script I think ok, but the return value is still nil. below is my code. Appreciate any help :

VBSTART
Dim Recordset
Dim SomeVar
Dim eQuestSQLString
dim OpenProcessData
dim pEquestOdbc
dim pEquestData
dim aRtnOdbc
dim aRtn

Function OpenEquestData
OpenEquestData = 0
Set pEquestOdbc = CreateObject("ADODB.Connection")
on error resume next
pEquestOdbc.Open "DSN=EQUESTRTN;UID=ertn01;PWD=ertn01;"

// pEquestOdbc.Open "DSN=EQUESTRTN,UID=ertn01;PWD=ertn01;DATABASE=mydatabasename"
//DSN=DSNname;UID=user;PWD=password;DATABASE=mydatabasename
if err then
OpenEquestData = 1
else
eQuestSQLString = "Select * from atlas.ERTN_TOSRTN_INP_PRE"
set pEquestData = pEquestOdbc.Execute(eQuestSQLString)
if err then
OpenEquestData = 2
end if
on error resume next
pEquestData.MoveFirst
end if
End Function

Function GetFieldCount
GetFieldCount = pEquestData.Fields.Count
End Function

Function GetRec(mx)
dim i
If Not pEquestData.EOF then
For i = 1 to mx
GetRec = GetRec & pEquestData.Fields(i) & ";"
Next
pEquestD.MoveNext
else
GetRec = 0
end if
End Function

Sub CloseProcessData
pEquestOdbc.Close
End Sub

VBEND

//let>cProc=c:\program files\mjt\macroscheduler\beta\DBLoopTest.scp
//let>ODBCname=ProcessData
//let>TableName=ProcessDetails

VBEval>OpenEquestData(),reply
VBEval>GetFieldCount,fcount
let>fcount=fcount-1
Label>ReadData
VBEval>GetRec(%fcount%),record
if>record=0
goto>done
endif>
len>record,lenrec
let>end=lenrec-1
midstr>record,1,%end%,record
sep>record,;,f
goto>ReadData
label>done

[Marcus Tettmar]

Have you debugged this? What value do you get for fcount? I.e. what does GetFieldCount return?

[mfauzim]

Already debug, when call OpenEquestData, reply is no value.
just dont know what's wrogn with the OpenEquestData, test sql with sqlnavigator, it works. odbc is ok.

[Marcus Tettmar]

When you post code please use the Code button so that it is formatted correctly.

You have a syntax error in your VBScript. I get an error trying to run OpenEquestData because you are using // for comments, but VBScript uses ' for comments. Your code should be:

Code: Select all

Function OpenEquestData
  OpenEquestData = 0
  Set pEquestOdbc = CreateObject("ADODB.Connection")
  on error resume next
  pEquestOdbc.Open "DSN=EQUESTRTN;UID=ertn01;PWD=ertn01;"

  ' pEquestOdbc.Open "DSN=EQUESTRTN,UID=ertn01;PWD=ertn01;DATABASE=mydatabasename"
  ' DSN=DSNname;UID=user;PWD=password;DATABASE=mydatabasename
  if err then
  	 OpenEquestData = 1
  else
    eQuestSQLString = "Select * from atlas.ERTN_TOSRTN_INP_PRE"
    set pEquestData = pEquestOdbc.Execute(eQuestSQLString)
  if err then
    OpenEquestData = 2
  end if
  on error resume next
  pEquestData.MoveFirst
end if
End Function

Now, you MUST get a value for reply, because you set OpenEquestData to zero at the top of the function. Therefore the return value will always be something.

If I run it I get a value of 1. Which is what I would expect as the data source doesn't exist on my PC.

[mfauzim]

I've tried your code and also I insert sample from help. but even the sample vbscript do not work.

Code: Select all

VBSTART
Dim Recordset
Dim SomeVar
Dim eQuestSQLString
dim OpenProcessData
dim pEquestOdbc
dim pEquestData
dim aRtnOdbc
dim aRtn

Function OpenEquestData(csql)
  OpenEquestData = 0
  Set pEquestOdbc = CreateObject("ADODB.Connection")
  on error resume next
  pEquestOdbc.Open "DSN=EQUESTRTN;UID=ertn01;PWD=ertn01;"

  ' pEquestOdbc.Open "DSN=EQUESTRTN,UID=ertn01;PWD=ertn01;DATABASE=mydatabasename"
  ' DSN=DSNname;UID=user;PWD=password;DATABASE=mydatabasename
  if err.number > 0 then
  	 OpenEquestData = 1
  else
    eQuestSQLString = csql
    set pEquestData = pEquestOdbc.Execute(eQuestSQLString)
  	if err.number > 0 then
   	    OpenEquestData = 2
  	end if
  	on error resume next
  	pEquestData.MoveFirst
  end if
End Function

Function GetFieldCount
   GetFieldCount = pEquestData.Fields.Count
End Function

Function GetRec(mx)
   dim i
   If Not pEquestData.EOF then
      For i = 1 to mx
         GetRec = GetRec & pEquestData.Fields(i) & ";"
	  Next
   pEquestD.MoveNext
   else
      GetRec = 0
   end if
End Function

Sub CloseProcessData
    pEquestOdbc.Close
End Sub
Function MultiplyNums(d,a)
  MultiplyNums = d * a
End Function

VBEND

Let>a=5
VBEval>MultiplyNums(%a%,2),answer

MessageModal>answer

let csql=Select * from atlas.ERTN_TOSRTN_INP_PRE
VBEval>OpenEquestData(csql),reply
VBEval>GetFieldCount,fcount
let>fcount=fcount-1
Label>ReadData
VBEval>GetRec(%fcount%),record
if>record=0
   goto>done
endif>
len>record,lenrec
let>end=lenrec-1
midstr>record,1,%end%,record
sep>record,;,f
goto>ReadData
label>done

[Marcus Tettmar]

What does OpenEquestData return?

[Marcus Tettmar]

BTW - VBScript expects quotes around strings so you need to use them when evaluating a VBScript expression. So you need them in VBEval:

let csql=Select * from atlas.ERTN_TOSRTN_INP_PRE
VBEval>OpenEquestData("%csql%"),reply

Note also you need to embed the variable in % symbols as you are embedding it in your VBScript evaluation.

You'll get a syntax error without the quotes.

Or do:

VBEval>OpenEquestData("Select * from atlas.ERTN_TOSRTN_INP_PRE"),reply

What is the value of reply - it must be something, because you are initialising the return value of the function to zero. So it should at least be zero.

[mfauzim]

Just find out. if I run the code in editor/debugger, script wont work and openequestdata do not return any value, but if I run it directly outside editor/debuger then the debugger run and openequestdata return 2.

very strange..

[Marcus Tettmar]

Also - if you remove your "on error resume next" lines you might have a better idea of what is wrong. With this line you are switching VBScript's error reporting off - and therefore any subsequent errors won't be reported. At least switch it on at the start of each function with "on error goto 0".

Try this and report back on what the values shown in the message boxes are:

Code: Select all

VBSTART
Dim Recordset
Dim SomeVar
Dim eQuestSQLString
dim OpenProcessData
dim pEquestOdbc
dim pEquestData
dim aRtnOdbc
dim aRtn

Function OpenEquestData(csql)
  OpenEquestData = 0
  Set pEquestOdbc = CreateObject("ADODB.Connection")
  on error resume next
  pEquestOdbc.Open "DSN=EQUESTRTN;UID=ertn01;PWD=ertn01;"

  ' pEquestOdbc.Open "DSN=EQUESTRTN,UID=ertn01;PWD=ertn01;DATABASE=mydatabasename"
  ' DSN=DSNname;UID=user;PWD=password;DATABASE=mydatabasename
  if err.number > 0 then
  	 OpenEquestData = 1
  else
    eQuestSQLString = csql
    set pEquestData = pEquestOdbc.Execute(eQuestSQLString)
  	if err.number > 0 then
   	    OpenEquestData = 2
  	end if
  	on error resume next
  	pEquestData.MoveFirst
  end if
End Function

Function GetFieldCount
   GetFieldCount = pEquestData.Fields.Count
End Function

Function GetRec(mx)
   on error goto 0
   dim i
   If Not pEquestData.EOF then
      For i = 1 to mx
         GetRec = GetRec & pEquestData.Fields(i) & ";"
	  Next
   pEquestD.MoveNext
   else
      GetRec = 0
   end if
End Function

Sub CloseProcessData
    on error goto 0
    pEquestOdbc.Close
End Sub
Function MultiplyNums(d,a)
  on error goto 0
  MultiplyNums = d * a
End Function

VBEND

Let>a=5
VBEval>MultiplyNums(%a%,2),answer

MessageModal>answer

let>csql=Select * from atlas.ERTN_TOSRTN_INP_PRE
VBEval>OpenEquestData("%csql%"),reply
MessageModal>Reply: %reply%
VBEval>GetFieldCount,fcount
MessageModal>Field Count: %fcount%
let>fcount=fcount-1
Label>ReadData
VBEval>GetRec(%fcount%),record
if>record=0
   goto>done
endif>
len>record,lenrec
let>end=lenrec-1
midstr>record,1,%end%,record
sep>record,;,f
goto>ReadData
label>done

Unless I have missed anything the code looks ok, so problem must be database connection, SQL or privileges. As I don't have access to your database, I cannot help with that.

[Marcus Tettmar]

Just find out. if I run the code in editor/debugger, script wont work and openequestdata do not return any value, but if I run it directly outside editor/debuger then the debugger run and openequestdata return 2.

very strange..

Did you start debugging from the TOP?

Anyway, 2 means connection string or SQL is wrong. It either can't connect or the SQL is wrong. Remove the "on error resume next" lines (or comment them out) and then run it again and you will get an error message from VBscript. What does it say?

[mfauzim]

Ok, reply retrun 0, then I got error at function GetFieldCount with error
object required pEquestData.

[Marcus Tettmar]

Right, so it looks like it's failing to create the recordset. Perhaps the SQL is invalid. Try this version of the script:

Code: Select all

VBSTART
Dim Recordset
Dim SomeVar
Dim eQuestSQLString
dim OpenProcessData
dim pEquestOdbc
dim pEquestData
dim aRtnOdbc
dim aRtn

Function OpenEquestData(csql)
  OpenEquestData = 0
  Set pEquestOdbc = CreateObject("ADODB.Connection")
  on error resume next
  pEquestOdbc.Open "DSN=EQUESTRTN;UID=ertn01;PWD=ertn01;"

  ' pEquestOdbc.Open "DSN=EQUESTRTN,UID=ertn01;PWD=ertn01;DATABASE=mydatabasename"
  ' DSN=DSNname;UID=user;PWD=password;DATABASE=mydatabasename
  if err.number > 0 then
  	 OpenEquestData = 1
  else
    eQuestSQLString = csql
	MsgBox eQuestSQLString
    set pEquestData = pEquestOdbc.Execute(eQuestSQLString)
  	if err.number > 0 then
   	    OpenEquestData = 2
  	end if
  	on error resume next
  	pEquestData.MoveFirst
  end if
End Function

Function GetFieldCount
   GetFieldCount = pEquestData.Fields.Count
End Function

Function GetRec(mx)
   on error goto 0
   dim i
   If Not pEquestData.EOF then
      For i = 1 to mx
         GetRec = GetRec & pEquestData.Fields(i) & ";"
	  Next
   pEquestD.MoveNext
   else
      GetRec = 0
   end if
End Function

Sub CloseProcessData
    on error goto 0
    pEquestOdbc.Close
End Sub
Function MultiplyNums(d,a)
  on error goto 0
  MultiplyNums = d * a
End Function

VBEND

Let>a=5
VBEval>MultiplyNums(%a%,2),answer

MessageModal>answer

let>csql=Select * from atlas.ERTN_TOSRTN_INP_PRE
VBEval>OpenEquestData("%csql%"),reply
MessageModal>Reply: %reply%
VBEval>GetFieldCount,fcount
MessageModal>Field Count: %fcount%
let>fcount=fcount-1
Label>ReadData
VBEval>GetRec(%fcount%),record
if>record=0
   goto>done
endif>
len>record,lenrec
let>end=lenrec-1
midstr>record,1,%end%,record
sep>record,;,f
goto>ReadData
label>done

You should get a msgbox showing your SQL statement. Check that it is ok.

Do you need the database prefix for the table name? Aren't you connecting to that database anyway? What happens if you change your SQL to:

Select * from ERTN_TOSRTN_INP_PRE

Is your connection string correct? How is your data source set up? Have you tried referencing the database directly with an oracle connection string, instead of via the data source? Have you verified the system data source works by connecting to it with MS Access and trying the same SQL? So many things to try .... the problem is almost certainly a database issue, not scripting issue.

[mfauzim]

Ok, its working based on Marcus latest suggestion. I managed to find the error. My odbc setting is correct, but the table is not there in the db. table refer to another db.

I guess few hours sleep help clear up my mind.

regards