[RESOLVED] How to Connect to a DB using "Cloudera ODBC Driver for Impala"

[hugomito]

Hi all,

I hope you can help me with this. I need to connect to a Database using Cloudera ODBC Driver for Impala. I installed the driver, created a User DSN (drona2) and tested it successfully (got the message: Successfully connected to data source!) but when I try to connect to the database using a Conneciton String, it simply didn't work.

I tried connecting to the database using the User DSN, I previously created, that has all parameters needed but got the following error:

     $ProviderDatasource = 'DSN=drona2;'
     $conn_Database = ObjCreate ("ADODB.Connection")
     $conn_Database.ConnectionString = $ProviderDatasource
     $conn_Database.Open

     ==> The requested action with this object has failed.:
     $conn_Database.Open
     $conn_Database.Open^ ERROR

Also I tried adding all parameters to my connection string as follows but also got the same error:

     $ProviderDatasource = 'Driver=Cloudera ODBC Driver for Impala;Host=MyHost.domain.com;PORT=21050;AuthMech=3;UID=MyUserId@domain.com;PWD=MyPasswordc;Schema=default;SSL=1;AllowSelfSignedCerts=1;AllowHostNameCNMismatch=1;CheckCertRevocation=1;KrbFQDN=_HOTS;KrbServiceName=impala;ServicePrincipalCanonicalization=1'

I hope someone has used this driver successfully before and can shed some light here.

If you need additional information, let me know.

Regards,

[mLipok]

Try my ADO.au3 UDF 

[water]

Add a COM error handler to your script to get more detailed error information.
For an example please check ObjEvent in the help file.

[hugomito]

Water,

I added the following function: $obj_ErrorHandling = ObjEvent ("AutoIt.Error", "f_ErrorHandling") to my code. I attached the f_ErrorHandling error. I hope this helps.

 

mLipok, I am going to review your UDF.

[mLipok]

"...an architecture mismatch beetwen the Driver and application"

x86 <> x64 

What type of Driver you have installed ?
Are you using the same Au3 architecture as installed driver ?

EDIT:

Add this following snippet:

; #AutoIt3Wrapper_UseX64=y
; #AutoIt3Wrapper_UseX64=n
MsgBox(0, '', @AutoItX64)

at the very beginning of your script.

Uncomment first or second line and check what will happend when you try to run your script.

 

Edited April 5, 2018 by mLipok

[hugomito]

mLipok,

I installed the 64bit version of Cloudera driver (ClouderaImpalaODBC64.msi).

I added the snippet at the very top of my script. The result was the same with one of the two lines uncomment -see image-

 

[mLipok]

use my snippet as a separate script.
Run them, and copy here SciTe console output.

 

[hugomito]

mLipok,

When you say: use my snippet... I assume you are referring to your ADO.au3 snippet, right?

I opened you ADO_Example.au3 and ran it but this is what I got:

C:\Users\hhernand\Documents\My Works\AU3\mLipok ADO\ADO.au3 (10) : ==> Error opening the file.:
#include <AutoItConstants.au3>

From Editor selected About SciTE-Lite

SciTE-Lite 
Version 2.28
    Jan 29 2012 21:33:22
by Neil Hodgson.
 Updated: Valik & Jos

I assume I have to upgrade my version, right?

[mLipok]

  On 4/6/2018 at 5:25 PM, hugomito said:

mLipok,

When you say: use my snippet... I assume you are referring to your ADO.au3 snippet, right?

Expand  

No, just this three lines.

  On 4/6/2018 at 5:25 PM, hugomito said:

C:\Users\hhernand\Documents\My Works\AU3\mLipok ADO\ADO.au3 (10) : ==> Error opening the file.:
#include <AutoItConstants.au3>

Expand  

What AutoIt Version you are using ?

  On 4/6/2018 at 5:25 PM, hugomito said:

From Editor selected About SciTE-Lite

SciTE-Lite 
Version 2.28
    Jan 29 2012 21:33:22
by Neil Hodgson.
 Updated: Valik & Jos

I assume I have to upgrade my version, right?

Expand  

Yes
Now I see you are not using SciTE4AutoIt3 as you still use SciTE-Lite

So as so far this three line snipet will not show you any different.

  On 4/5/2018 at 8:41 PM, hugomito said:

mLipok,

I installed the 64bit version of Cloudera driver (ClouderaImpalaODBC64.msi).

I added the snippet at the very top of my script. The result was the same with one of the two lines uncomment -see image-

 

Expand  

 

Your problem is that you are using Au3 x86 with 64bit Driver.
You must to decied if you want to use x86 or x64 bit version, because you can not mix them together.

[hugomito]

mLipok,

Thanks a lot for staying with me until the end and for providing very valuable tips. My issue is resolved and this is what I did, hopefully this is the real solution:

1. Uninstall 64bits ODBC driver, restarted my Laptop
2. Installed 32bits ODBC driver but never saw the drivers installed in Data Source (ODBC) application, restarted my laptop
3. Installed 64bits ODB driver again, restarted my laptop
4. Gave it a try again running the following script successfully:

;   Error Handling function
   $obj_ErrorHandling = ObjEvent ("AutoIt.Error", "f_ErrorHandling")

;   Data Source Name and database connection
   $ProviderDatasource = 'DSN=MyOdbcName;'
   $conn_Database = ObjCreate ("ADODB.Connection")
   $conn_Database.ConnectionString = $ProviderDatasource
   $conn_Database.Open

;   ***************************
;   Error Handling function
;   ***************************
   Func f_ErrorHandling ($obj_ErrorHandling)
      $HexNumber = Hex ($obj_ErrorHandling.number, 8)
      $txt_MyErrorDescription = ""

   ;    Manage application errors
      MsgBox (0, "Error Handling", "We intercepted a COM Error !"                               & @CRLF  & @CRLF & _
            "Error Number: "            & $obj_ErrorHandling.Number                             & @CRLF & _
            "Error Description: "       & $obj_ErrorHandling.description                        & @CRLF & _
            "Error WinDescription:"     & StringStripWS ($obj_ErrorHandling.windescription, 8)  & @CRLF & _
            "Error Last DLLL Error: "   & $obj_ErrorHandling.lastdllerror                       & @CRLF & _ 
            "Error Script Line: "       & $obj_ErrorHandling.scriptline                         & @CRLF & _
            "Error Source: "            & $obj_ErrorHandling.source)
      Exit (1)
   EndFunc

Then I substituted my DSN with actual Connection String values as follows:

;   Connection string with LDAP authentication
$ProviderDatasource = 'Driver=Cloudera ODBC Driver for Impala;Host=MyServer.domain.com;Port=21050;Schema=MySchemaName;AuthMech=3;UID=MyUserId@domain.com;PWD=MyPassword;UseSASL=1;SSL=1;AllowSelfSignedServerCert=1;AllowHostNameCNMismatch=1;CheckCertRevocation=1;TrustedCerts=C:\Program Files\Cloudera ODBC Driver for Impala\lib\cacerts.pem;'

Thanks again!!!

How do I mark this case as resolved?

Edited April 10, 2018 by hugomito

[mLipok]

Thanks to you for posting your solution.

Try also to install recent version from here:

https://www.autoitscript.com/cgi-bin/getfile.pl?autoit3/autoit-v3-setup.exe

https://www.autoitscript.com/cgi-bin/getfile.pl?../autoit3/scite/download/SciTE4AutoIt3.exe

and here

https://www.autoitscript.com/autoit3/scite/download/beta_SciTE4AutoIt3/

 

  On 4/10/2018 at 2:07 AM, hugomito said:

How do I mark this case as resolved?

Expand  

 

Few years ago it was possible, but then IPB/Invision Dev Team remove this feature.

You can only edit title.

Edited April 10, 2018 by mLipok

[hugomito]

 

  Quote

Try also to install recent version from here:

https://www.autoitscript.com/cgi-bin/getfile.pl?autoit3/autoit-v3-setup.exe

https://www.autoitscript.com/cgi-bin/getfile.pl?../autoit3/scite/download/SciTE4AutoIt3.exe

and here

https://www.autoitscript.com/autoit3/scite/download/beta_SciTE4AutoIt3/

Expand  

Yep!,

I already requested Helpdesk to upgrade my AutoIT version so whenever they contact me, I will provide the Links.

Thanks!!!

PS: Also learning how to use Posting features by seeing what you did.

[mLipok]

Did you also look here:

??