HTTP-ODBC is a small server that serves as an HTTP access point to an ODBC endpoint. In particular, Experian Pandora uses a propertiary JDBC driver, and so the most effective way to connect to Experian Pandora is through a DSN. However, some clients do not support accessing ODBC via DSN. This tool is ideal for those circumstances.
If you are capable of connecting to the underlying driver directly using JDBC that will be more effective, and performant.
Before using HTTP-ODBC be sure to set the necessary environment variables:
| Environment Variable | Description | Default |
|---|---|---|
| HTTP_ODBC_CONNECTION_STRING | Connection string to be used to connect via ODBC | None |
| HTTP_ODBC_PORT | Port to be used when server starts | 8005 |
Find more information about the connection string to use here.
Please note tha the HTTP_ODBC_PORT is not the port in which your application is exposing itself. This will be the port in which the server itself listens.
So for example, if you're running Experian Pandora or Experian Data Studio and its ODBC port listens at 7800, you will not want to configure the HTTP_ODBC_PORT to also listen 7800. Instead, you might want to make the port an available open port, for example 8000.
In addition, HTTP-ODBC must be running on the same machine that exposes the ODBC DSN name. So if you have a machine called machine-1 that has the DSN name PServer 64bit listed under its ODBC Connections then HTTP-ODBC must be running on machine-1 in order to access the DSN name PServer 64bit if that's the DSN you're trying to use.
Be sure that the service Pandora and/or Data Studio is turned on before attempting to run HTTP-ODBC. If Pandora and/or Data Studio are not on and you run HTTP-ODBC it will attempt to connect to an ODBC connection that is not listening and fail with an error.
This is generally the same practice you would use for any application.
- Download the latest release - be sure to download
server-windows.zip. - Extract server-windows to
C:\Windows\Program Files\Experian\HTTP-ODBC - Make sure your User DSN is set (for Windows machines). See the Troubleshooting section below for details.
- Set the envrionment variables as specified in
Configuration. - Run the server.exe
Alternatively, you can set the configuration in the included
server-pandora.batandserver-aperture.batfiles for Windows and execute the script as necessary for Pandora or Data Studio respectively.
Once the server is running you will send a POST request to the URL in the following format
{
"sql": $QUERY
}Where $QUERY is a properly escaped SQL query, e.g. "SELECT * FROM \"PROFILES\"".
This server is best used when it running in the background. To facilitate this we rely on NSSM - a tool that can convert an exe or script into a Windows Service. Currently we do not support the equivalent tool for Linux. You can download NSSM here (please use 2.24 as that has been tested). Instructions on how to use NSSM can be found here but a shortened instruction guide can be found below.
Simply put, the way to use NSSM is summarized here:
- Download NSSM 2.24.
- Unzip NSSM to a directory of your choosing.
- Open up the Windows Command Prompt in Administrator Mode in the directory it was unzipped in.
- Navigate to
win64. - Use the command
nssm.exe install
You will see the following screen:
- For service name type in
http-odbc. - For Path select the path the server was extracted in (by default
C:\Windows\Experian\HTTP-ODBC\server\server.exe). If you are using theserver-pandora.batorserver-aperture.batfiles to set the environment variables selectserver-pandora.batorserver-aperture.batpaths, respectively. - Navigate to Dependencies and enter in those that apply. Experian Pandora and Experian Aperture Data Studio users should enter in the service that they're using. So for example, if you're using Experian Pandora 5.9.5 you would enter
Experian_dbserver_5.9.5. You can find this information by opening services, and right clicking the service as shown below
At the moment, our binary is only compiled for Windows. However, you can clone this project and set the environment variables as mentioned in the Configuration section and run server.py to use this on Linux machines that have Python installed.
If the server does not start you're most likely getting this particular error (on Windows).
This means your HTTP_ODBC_CONNECTION_STRING has not been properly configured. To confirm that the configuration is correct, you can do the following:
- Open up odbcad32.exe (Win + R -> odbcad32.exe) and make sure your User DSN includes the one you are using in
HTTP_ODBC_CONNECTION_STRING. So as an example, based on these User Data Sources:
You would set HTTP_ODBC_CONNECTION_STRING to DSN=PServer 64bit;UID=administrator;PWD=administrator.
You can see that the PServer 64bit is reflected here. The UID and PWD will depend on the drivers configuration itself. If you're ensure please contact your system administrator.
If you're not getting a response it means the query itself you're attempting to use is invalid.
With Pandora and Data Studio in particular, make sure that table you're querying exists.
Make sure that Microsoft Visual C++ 2010 and 2012 are both installed.