Hiello,
Hi Sreenivasulu,
What is the error that you see when you try to establish a connection?
Can you tell the exact version of driver and the database?
Regards,
Neelima
[obiee1171@VTS-OracleLin27 bin]$ ./nqcmd
-------------------------------------------------------------------------------
Oracle BI ODBC Client
Copyright (c) 1997-2013 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------
Give data source name: MongoDB4
Give user name: cms
Give password:
[0][State: IM003] [DataDirect][ODBC lib] Specified driver could not be loaded
Connect open failed
Connection open failed:
[0][State: IM003] [DataDirect][ODBC lib] Specified driver could not be loaded
Give data source name: ^C
do you really want to exit (y/n):
information from odbc.ini:
[MongoDB4]
Driver=/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_32bit/lib/ivmongo28.so
ServerMachine=vts-oraclelin38.gpc.zodiacmarine.lan
Port=27017
ForwardOnlyCursor =Yes
UID=CMS
PWD=
SelectPhysical =Yes
Regional = Yes
SSL = No
[ODBC Data Sources]
AnalyticsWeb=Oracle BI Server
Cluster=Oracle BI Server
SSL_Sample=Oracle BI Server
MongoDB4=Oracle BI Server
Please see knowledgebase article at: knowledgebase.progress.com/.../000033527 and make sure your environment is set correctly.
Thanks. We will verify get back to you. Please standby.
Verified all values as per the link provided but no luck. Please advise.
1.Verified LD_LIBRARY_PATH:
[obiee1171@VTS-OracleLin27 setup]$ echo $LD_LIBRARY_PATH
/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_32bit/lib:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/bifoundation/server/bin:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/bifoundation/web/bin:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/bifoundation/odbc/lib:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/clients/epm/Essbase/EssbaseRTC/bin:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/bin:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/lib:/usr/lib:/lib:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_32bit/lib:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/lib:
2. Specified libodbc.so library patch in ODBC.ini
[MongoDB4]
Driver=/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_32bit/lib/libodbc.so
ServerMachine=vts-oraclelin38.gpc.zodiacmarine.lan
Port=27017
ForwardOnlyCursor =Yes
UID=CMS
PWD=
SelectPhysical =Yes
Regional = Yes
SSL = No
3. Please confirm driver file name tried with libodbc.so but its not working. Please let me know driver file to use.
4. Specified clinet path.
/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_32bit/lib
Regards,
Sreenivasuluuu.
Hello...
Cann someone help me with this...
Regards,
Sreenivasulu.
In odbc.ini, the driver should be:
Driver=/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_32bit/lib/ivmongo28.so
not libodbc.so.
Are you sure you want to use the 32-bit, not the 64-bit, mongo driver for your app? For 64-bit, the driver name is ddmongo28.so
Hi,
Thanks for the update.
Let me change and try.
Regards,
Sreenivasulu.
This driver requires a JVM. Please also verify that you have the path to a libjvm.so on your shared library path in addition to the path to the driver libraries.
Also, some of the properties you listed in the data source(like ServerMachine) are not valid for the Progress MongoDB ODBC driver. There is a sample odbc.ini that gets installed with the product. Please switch to modifying and using(via the ODBCINI env variable) that file.
This Quick Start Guide should help:
documentation.progress.com/.../mongoodbc_unix_quickstart.htm
Please verify that you can load the driver outside the OBIEE environment and then work on configuring OBIEE for connectivity.
Please keep us posted on your status.
Thank you . I am checking all the specified values and update.
Hi,
That was good document. Now I am getting different error after setting proper driver. Please advise.
[obiee1171@VTS-OracleLin27 bin]$ ./nqcmd
-------------------------------------------------------------------------------
Oracle BI ODBC Client
Copyright (c) 1997-2013 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------
Give data source name: MongoDB4
Give user name: cms
Give password:
[0][State: 60] [DataDirect][ODBC 20101 driver][MongoDB]java.lang.NoClassDefFoundError: com/ddtek/mongodbcloud/sql/JniCommunicator
Connect open failed
Connection open failed:
[0][State: 60] [DataDirect][ODBC 20101 driver][MongoDB]java.lang.NoClassDefFoundError: com/ddtek/mongodbcloud/sql/JniCommunicator
DataSoruce:
==========
[MongoDB4]
Driver=/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_64bit/lib/ddmongo28.so
Database=CMS
HostName=11.93.16.217
PortNumber=27017
[obiee1171@VTS-OracleLin27 setup]$ echo $LD_LIBRARY_PATH
/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_64bit/lib:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/bifoundation/server/bin:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/bifoundation/web/bin:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/bifoundation/odbc/lib:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/clients/epm/Essbase/EssbaseRTC/bin:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/bin:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/lib:/usr/lib:/lib:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_64bit/lib:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_64bit/jre/lib/amd64/server:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/jdk/jre/lib/amd64/server:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/lib:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/bifoundation/server/bin:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/bifoundation/web/bin:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/bifoundation/odbc/lib:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/clients/epm/Essbase/EssbaseRTC/bin:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/bin:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/lib:/usr/lib:/lib:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/jdk/jre/lib/amd64/server:/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/lib:
[obiee1171@VTS-OracleLin27 setup]$ ldd /zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_64bit/lib/ddmongo28.so
linux-vdso.so.1 => (0x00007fff44417000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f2d24d3a000)
librt.so.1 => /lib64/librt.so.1 (0x00007f2d24b32000)
libddicu28.so => /zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_64bit/lib/libddicu28.so (0x00007f2d23c3b000)
libodbcinst.so => /zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_64bit/lib/libodbcinst.so (0x00007f2d23a08000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f2d23804000)
libjvm.so => /zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_64bit/jre/lib/amd64/server/libjvm.so (0x00007f2d22990000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007f2d22689000)
libm.so.6 => /lib64/libm.so.6 (0x00007f2d22405000)
libc.so.6 => /lib64/libc.so.6 (0x00007f2d22071000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f2d21e5a000)
/lib64/ld-linux-x86-64.so.2 (0x000000387a000000)
Regards,
Sreenivasulu.
The driver could not locate mongodb.jar? You can either put it under
/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_80_64bit/java/lib
or specify the yor own full path for mongodb.jar like below in your odbc.ini file:
JVMClasspath=<YOUR PATH/mongodb.jar>
Hope this helps. Good luck.
Thanks . I am checking will provide an update.
Thanks, Looks like now I am able to connect from Backend using nqcmd.
obiee1171@xxxxxxxxxx bin]$ ./nqcmd
-------------------------------------------------------------------------------
Oracle BI ODBC Client
Copyright (c) 1997-2013 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------
Give data source name: MongoDB4
Give user name: cms
Give password:
[T]able info
[C]olumn info
[D]ata type info
[F]oreign keys info
[P]rimary key info
[K]ey statistics info
[S]pecial columns info
[Q]uery statement
Select Option: Q
Give SQL Statement:
But we are getting below error while connecting from BI Answers. Please advise.
Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 16001] ODBC error state: S0002 code: -5501 message: [DataDirect][ODBC 20101 driver][20101]syntax error or access rule violation: base table or view not found: XXUSERS_ZIP_TERR_V. [nQSError: 16002] Cannot obtain number of columns for the query result. (HY000)
SQL Issued: SELECT 0 s_0, "Zodiac Mongo Test"."Users"."Account Number" s_1, "Zodiac Mongo Test"."Users"."City" s_2 FROM "Zodiac Mongo Test" FETCH FIRST 999999 ROWS ONLY
Refresh
Regards,
Sreenivasulu.
A column of a table in a schema should be referenced like this Schema.Table.Column if you prefer this full qualified name. In your case, the first column should be "Users"."Zodiac Mongo Test"."Account Number" if Users is the schema you were using. For the backend I am using for this test against MongoDB 2.6.4, I have to use each of these parts in upper case as they are put in double quotes. I guess you have to do the same for your backend. I am not sure if the case of identifiers is configurable for the backend, though.
Hope this helps. Good luck!
Hi,
Can some help us in fixing these issue.
Regards,
Sreenivasulu.
What is the issue?
Here you go... If this is not correct can you provide sample statement and see if I can fetch data from setup I did.
[obiee1171@VTS-OracleLin27 bin]$ ./nqcmd
-------------------------------------------------------------------------------
Oracle BI ODBC Client
Copyright (c) 1997-2013 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------
Give data source name: MongoDB4
Give user name: CMS
Give password:
[T]able info
[C]olumn info
[D]ata type info
[F]oreign keys info
[P]rimary key info
[K]ey statistics info
[S]pecial columns info
[Q]uery statement
Select Option: Q
Give SQL Statement:
[T]able info
[C]olumn info
[D]ata type info
[F]oreign keys info
[P]rimary key info
[K]ey statistics info
[S]pecial columns info
[Q]uery statement
Select Option: Q
Give SQL Statement: SELECT 0 s_0, "Devices"."Serial Number" s_1, "Periods"."Zodiac Fiscal Year" s_2 FROM "Zodiac Mongo Test" WHERE ("Periods"."Zodiac Fiscal Year" = '2016')
SELECT 0 s_0, "Devices"."Serial Number" s_1, "Periods"."Zodiac Fiscal Year" s_2 FROM "Zodiac Mongo Test" WHERE ("Periods"."Zodiac Fiscal Year" = '2016')
2713MongoDB ODBC driver2726MongoDB ODBC driver.2728
[2713][State: 60] [DataDirect][ODBC 20101 driver]2713MongoDB ODBC driver2726MongoDB ODBC driver.
Statement preparation failed
Regards,
Sreenivasulu.
Thanks for raising this thread Sreenivasulu. I recall when I queried MongoDB from OBIEE Admin on Windows, it was required to *clear* that read-only box on the advanced tab based on some emulation issues. From Linux, the equivalent would be ReadOnly=0. See if that helps or you can also contact us from the website if you want access to a live technical resource to help.
Hi Sumit,
Thanks for the advise. I will check this option and reach out if any others issues.
Regards,
Sreenivasulu.
Hi Sumit,
Set the ReadOnly=0 but still same issue.
Could you suggest how I can reach to live technical resource for assistance. Is it free or will there be any charges.
[obiee1171@VTS-OracleLin27 bin]$ ./nqcmd
-------------------------------------------------------------------------------
Oracle BI ODBC Client
Copyright (c) 1997-2013 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------
Give data source name: MongoDB4
Give user name: CMS
Give password:
[T]able info
[C]olumn info
[D]ata type info
[F]oreign keys info
[P]rimary key info
[K]ey statistics info
[S]pecial columns info
[Q]uery statement
Select Option: Q
Give SQL Statement: SELECT "CMS"."Users"."Account Number" FROM "Users" ;
SELECT "CMS"."Users"."Account Number" FROM "Users" ;
2713MongoDB ODBC driver2726MongoDB ODBC driver.2728
[2713][State: 60] [DataDirect][ODBC 20101 driver]2713MongoDB ODBC driver2726MongoDB ODBC driver.
Statement preparation failed
Give SQL Statement: SELECT "CMS"."Users"."Account Number" FROM "Users"
SELECT "CMS"."Users"."Account Number" FROM "Users"
2713MongoDB ODBC driver2726MongoDB ODBC driver.2728
[2713][State: 60] [DataDirect][ODBC 20101 driver]2713MongoDB ODBC driver2726MongoDB ODBC driver.
Statement preparation failed
Regards,
Sreenivasulu.
We do offer free evaluation support options for prospects. Please fill out the form and include the request in the message - www.progress.com/.../contact
Hi Sumit,
That's great thanks for the information. I will fill the form.
Regards,
Sreenivasulu.
Have you tried to use upper case values for all the values that you put in double quotes?
BTW, See the numbers instead of text in the error message above? your resource file could not be located by the driver. If your driver .so is under .../lib, the resource .mo should be under .../locale/en_US/LC_MESSAGES unless you use a different locale.
Hi,
Both .so and .mo files are in correct location. Also ran query with upper case still same issue.
I have USERS tables under CMS schema and have columns like Email, Account Number etc., can you give sample command to test.
Give SQL Statement: SELECT "CMS"."USERS"."ACCOUNT NUMBER" FROM "USERS" ;
SELECT "CMS"."USERS"."ACCOUNT NUMBER" FROM "USERS" ;
2713MongoDB ODBC driver2726MongoDB ODBC driver.2728
[2713][State: 60] [DataDirect][ODBC 20101 driver]2713MongoDB ODBC driver2726MongoDB ODBC driver.
Statement preparation failed
Regards,
Sreenivasulu.
What is your LOCALE environment variable set to?
If set to anything other than en_US, please add a symbolic link in the ODBC_INSTALL_DIR/locale directory where the name of the link is your locale value and it points to the ODBC_INSTALL_DIR/locale/en_US directory. This should resolve the issue you are having with the driver not being able to find the message files.
Example:
LOCALE=en_US.UTF8
If ODBC Install Dir=/home/users/myuser/odbc80 then run these commands to create the symbolic link:
cd /home/users/myuser/odbc80
ln -s /home/users/myuser/odbc80/locale/en_US en_US.UTF8
Incidentally, the message you are now getting is:
The evaluation period for this MongoDB ODBC driver has expired. Please call Progress Software Corporation to obtain a production version of this MongoDB ODBC driver.
Please use the form to contact our organization to get an extension to your evaluation period.
Hi,
Thanks for the information. I will check the option and get back.
regarding drivers, Currently we are on testing phase by installing evaluation version once it is successful we will contact for production version.
Regards,
Sreenivasulu.
Have you tried a column name without spaces to see if the query works?
Hi,
I am able to get data from using simple query from nqcmd. Now I will check from OBIEE Answers and advise.
select email from users.
Thanks for the help.
Regards,
Sreenivasulu.
Hi,
As said i am able to fetch data after creating data source using nqcmd command. But when I try from OBIEE Answers page it gives below error please suggest.
Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 16001] ODBC error state: 37000 code: -5582 message: [DataDirect][ODBC 20101 driver][20101]syntax error or access rule violation: unknown token: "CMS"... [nQSError: 16002] Cannot obtain number of columns for the query result. (HY000)
SQL Issued: SELECT 0 s_0, "Zodaic MongoDB"."Users"."Address1" s_1, "Zodaic MongoDB"."Users"."First Name" s_2 FROM "Zodaic MongoDB" FETCH FIRST 999999 ROWS ONLY
Regards,
Sreenivasulu.
Have you tried to use LIMIT 999999 instead of FETCH FIRST 999999 ROWS ONLY? The latter does not look like the mongo syntax to me.
We are not writing any query in OBIEE, just joining the tables from RPD. It will automatically generates the SQL to run mongoDB I think.
Please advise.
The FETCH FIRST # ROWS ONLY format is like the DB2 syntax. Somehow without the FIRST, the FETCH # ROWS ONLY works fine. Also, the Microsoft SQL Server format OFFSET X ROWS FETCH NEXT Y ROWS ONLY works as well besides the more popular LIMIT format. Please go ahead to file an enhancement or a defect whatever appropriate.
I think that is the logical SQL statement and we should get an ODBC trace to see physical SQL and see what features are enabled at app level. Did you fill out the contact us form so we can look into this in detail?
Yes Sumit, I have filled the contact us form.
Thanks, the local account team will be reaching out.
In the meantime, you can create an ODBC trace so we can see exactly what query is being sent from the driver:
Thanks for calling Abhishek. I am not able to send email to your email ID abhishek.cont@progress.com. Please confirm your email ID.
I have tried to enable trace with below options in odbc.ini file and reproduced the issue but didn’t see any trace file in the specified location.
[ODBC]
Trace=0
TraceFile=/tmp/odbctrace.out
TraceDll=/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_64/lib/ddtrc28.so
InstallDir=/zodiac/BITEST/OBIAPP_DEV2/MW_HOME/Oracle_BI1/common/ODBC/Progress/DataDirect/ODBC_64
Trace=1
UseCursorLib=0
ODBCTraceFlush=1
IANAAppCodePage=4
TraceOptions=0
Regards,
Sreenivasulu.
Hello,
Could someone help me on this.
Regards,
Sreenivasulu.
You have both Trace=0 and Trace=1 in the [ODBC] block. If you want to trace, please remove Trace=0 and only have Trace=1 there.
Thanks. Enabled trace and got the trace.