How to connect to remote Oracle database without TNS names?

[K33p3r]

Greetings everyone,

I am quite new to this kind of programming and I'm currently trying to create a rather complex program.

I've recently set up an Oracle 11g database at my home PC and I want this program to run on my work laptop which will connect to my home database.

The objective of the program is to provide a GUI to the user in which he can select a network equipment (AP, switch, router) based on specifications that he can input. Once the form is completed, he will then press the search button and a SQL query will be created and sent, containing his input, to the remote database. In a short time he will receive a message containing the desired equipment model or an error if the input was incorrect or incomplete.

I have all the GUI components sorted out and functioning properly and I can also locally connect and retrieve the information like so:

$apCon = ObjCreate( "ADODB.Connection" ) ; Create a COM ADODB Object
GUICtrlSetData($apProgress, "Connecting to database...")
With $apCon
.ConnectionString =("Provider='OraOLEDB.Oracle';Data Source='TestDB';User Id='test';Password='test';")
.Open
EndWith

If @error Then
GUICtrlSetData($apProgress, "Failed to connect to the database")
Exit
Else
GUICtrlSetData($apProgress, "Connection successful")
EndIf

$apRecord = ObjCreate( "ADODB.RecordSet" ) ; Create a Record Set to handles SQL Records
GUICtrlSetData($apProgress, "Retrieving information...")
With $apRecord
.ActiveConnection = $apCon
.Source = "select ap_model from meraki_aps"
.Open
EndWith

MsgBox(0, "AutoIT-SQL Result", "Value = " & $apRecord.Fields( "ap_model" ).Value )
$apCon.close

The problem is when I try a remote connection method I receive an error. Here is the code and error output:

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$apCon = ObjCreate( "ADODB.Connection" )
$DSN = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=193.168.0.50)(PORT=1521))" & _
"(CONNECT_DATA=(SID=TestDB)(SERVICE_NAME=TestDB)));uid=test;pwd=test;"
$apCon.Open($DSN)
$apRecord = ObjCreate( "ADODB.RecordSet" )
$apRecord.Open( "select ap_model from meraki_aps", $conn )
MsgBox(0, "AutoIT-SQL Result", "Value = " & $apRecord.Fields( "ap_model" ).Value )
$apCon.Close

Func MyErrFunc()
$HexNumber=hex($oMyError.number,8)
Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !" & @CRLF & @CRLF & _
"err.description is: " & @TAB & $oMyError.description & @CRLF & _
"err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
"err.number is: " & @TAB & $HexNumber & @CRLF & _
"err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
"err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
"err.source is: " & @TAB & $oMyError.source & @CRLF & _
"err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
"err.helpcontext is: " & @TAB & $oMyError.helpcontext _
)
SetError(1)
Endfunc

err.description is: [Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener
err.windescription: Unspecified error

err.number is: 80020009
err.lastdllerror is: 0
err.scriptline is: 9
err.source is: Microsoft OLE DB Provider for ODBC Drivers
err.helpfile is:
err.helpcontext is: 0

I think it might have something to do with tnsname.ora and listener.ora. I've tried to do some tweaking but of no avail.

I appreciate any assistance in this matter.

All the best,

K33p3r

[Bowmore]

The error message indicates that you do not have the Oracle Listener service running.

I'm no expert on setting up Oracle but this link should tell you everything you need to know about starting and configuring the listener service.

http://docs.oracle.com/cd/B28359_01/network.111/b28316/listenercfg.htm

[K33p3r]

The error message indicates that you do not have the Oracle Listener service running.

I'm no expert on setting up Oracle but this link should tell you everything you need to know about starting and configuring the listener service.

http://docs.oracle.com/cd/B28359_01/network.111/b28316/listenercfg.htm

Thanks for the suggestion, I've followed the instructions and created a listener like this one:

SID_LIST_TESTDB =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:apptestproduct11.2.0dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:apptestproduct11.2.0dbhome_1binoraclr11.dll")
    )
  )
TESTDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 193.168.0.50)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_TESTDB = D:apptest

Unfortunately, I still can't connect to the database. I've tried connecting with Oracle SQL Developer and used my IP as hostname and got the same result. Although when I replaced my IP with 'localhost' or 127.0.0.1 it worked flawlessly in both the program and SQL Developer. I guess it has something to do with my home IP. Might the problem arise from trying to connect to my home IP using the same PC?

Cheers,

K33p3r

[Bowmore]

Did you remember to start the listener? following the steps in section 10.5.1 Starting and Stopping a Listener

[K33p3r]

Did you remember to start the listener? following the steps in section 10.5.1 Starting and Stopping a Listener

Silly me, I forgot to start the listener. Thank you for the tip.

I now get another error that regards the service name. I had it set up as TestDB shown in the above code but for some reason it doesn't read it. Am I doing something wrong?

err.description is: [Microsoft][ODBC driver for Oracle][Oracle]ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
err.windescription: Unspecified error
err.number is: 80020009
err.lastdllerror is: 0
err.scriptline is: 9
err.source is: Microsoft OLE DB Provider for ODBC Drivers
err.helpfile is:
err.helpcontext is: 0

Cheers,

Keeper

[lordofthestrings]

you're using the standard ODBC driver from microsoft, you should have installed your oracle client software on your client computer and use that in your connection string (you have a tool called connection string editor: (download here: http://www.codeproject.com/KB/applications/ConnectionStringEditor/ConnectionStringEditor.zip )

you can test your oracle DB connectivity with the command TNSPING <database> this should first resolve the server/database, and attempt a basic connect. this should output <OK> if I remember well.

that should get you up and running.

[K33p3r]

you're using the standard ODBC driver from microsoft, you should have installed your oracle client software on your client computer and use that in your connection string (you have a tool called connection string editor: (download here: http://www.codeproject.com/KB/applications/ConnectionStringEditor/ConnectionStringEditor.zip )

you can test your oracle DB connectivity with the command TNSPING <database> this should first resolve the server/database, and attempt a basic connect. this should output <OK> if I remember well.

that should get you up and running.

This might sound dumb, but are you referring to client software such as InstantClient from Oracle?

[CELIO]

K33p3r

try change: $apRecord.Open( "select ap_model from meraki_aps", $conn )

to: $apRecord.Open( "select ap_model from meraki_aps", $apCon )

[iamtheky]

OP hasnt been back since Oct. 2012.   I'm not saying you shouldnt clean dead peoples homes, thats totally super nice.