{"id":20448,"date":"2022-09-15T16:04:28","date_gmt":"2022-09-15T14:04:28","guid":{"rendered":"https:\/\/help.openbee.com\/open-bee-portal\/administrators-guide\/sql-connectivity-module\/external-sql-connections\/"},"modified":"2026-05-13T10:47:50","modified_gmt":"2026-05-13T08:47:50","slug":"external-sql-connections","status":"publish","type":"page","link":"https:\/\/help.openbee.com\/en\/open-bee-portal\/administrators-guide\/sql-connectivity-module\/external-sql-connections\/","title":{"rendered":"External SQL Connections"},"content":{"rendered":"\n

The “External SQL connections” screen lists the different external SQL connections and allows you to create new ones.An external SQL connection contains the information needed to access an existing database.<\/p>\n\n

To create a new connection, click “Add External SQL Connection<\/strong>“.Here are the different fields to fill in:<\/p>\n\n

\n
\n

Open Bee\u2122 Portal On Premises<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n<\/div>\n\n\n\n
\n

Open Bee\u2122 Portal Cloud<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n<\/div>\n<\/div>\n\n

Connection Name<\/strong> : The name you choose for this connection.<\/p>\n\n

ODBC API<\/strong>, to define whether this SQL connection is going to use an ODBC driver.If this is the case, you will need to fill in the “ODBC Connection String<\/strong>“.<\/p>\n\n

\n

When using a cloud base Open Bee\u2122 Portal, an ODBC connection is mandatory.<\/p>\n<\/blockquote>\n\n

D\u00e9tails configuration ODBC (Open Bee\u2122 Portal Cloud et On Premises)<\/h2>
\n

Open Bee\u2122 Portal Cloud<\/h3>\n\n\n\n

Using the Cloud SQL module requires you to first install the Open Bee\u2122 Gateway tool on a Windows server in your local network that can access the database.<\/p>\n\n\n\n

Note: Only 32-bit ODBC drivers are supported.<\/em><\/p>\n\n\n\n

After installing Open Bee Gateway, you need to open it and enter the login details to your Open Bee\u2122\u2122 Portal cloud instance<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

After entering this information, go to the “Gateway” tab and click on “Connect”<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Finally, you will be able to go back to the “External SQL Connections” tab of the SQL module of Open Bee\u2122 Portal and create a new connection by entering the ODBC connection string and the Gateway to use.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Open Bee\u2122 Portal On Premises<\/h3>\n\n\n\n
\n

<\/p>\n\n\n\n

In the case of an ODBC connection on an Open Bee Portal On Premises, it is necessary to prefix the connection string with “odbc:” Example: odbc:Driver={Microsoft Excel Driver (*.xls)}; Dbq=C:\\BDD.xls; <\/p>\n\n\n\n

The driver will need to be installed, in the case of an Open Bee\u2122 Portal On Premises, on the server, otherwise it will need to be installed on the server where the Gateway <\/a>is installed.<\/p>\n<\/blockquote>\n\n\n\n

Note: Only 64-bit ODBC drivers are supported.<\/em><\/p>\n\n\n\n

Connection Strings<\/h3>\n\n\n\n

The connection string is specific to the type of database, refer to the ODBC documentation for your DBMS.You can find examples of ODBC connection strings on this site:https:\/\/www.connectionstrings.com\/<\/a>Examples of connection chains include:XLS file:<\/strong>driver={Microsoft Excel Driver (*.xls)}; Dbq=C:\\My fichier.xls;<\/p>\n\n\n\n

CSV file<\/strong> (only one file must be present in the directory):driver={Microsoft Text Driver (*.txt; *.csv)}; Dbq=C:\\My Directory; extensions=asc,csv,tab,txt;<\/p>\n\n\n\n

Microsoft SQL Server:<\/strong>Driver={SQL Server}; server=<IP>;D atabase=<Comic book>;P ort=<port>; Uid=<wear out>;P wd=<Password><\/p>\n\n\n\n

Oracle:<\/strong>Driver={Microsoft ODBC for Oracle}; Uid=<wear out>;P wd=<Password>; Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<IP>)(PORT=<PORT>))(CONNECT_DATA=(SID=<Comic book>)));<\/p>\n\n\n\n

Note: For On Premises installations, the odbc: intruction must be added in front of the connection string.<\/p>\n\n\n\n

\n\n\n\n
Test the ODBC connection<\/h5>\n\n\n\n

If an error is returned when creating the connection, tools like Excel can be used to test the ODBC connection string<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/div>\n<\/div><\/div>\n\n
<\/div>\n\n

D\u00e9tails configuration sans pilote ODBC (uniquement Open Bee\u2122 Portal On Premises)<\/h2>
\n

If your connection does not use an ODBC driver, and you are using an Open Bee\u2122 Portal On Premises instance, you can choose the DBMS.<\/p>\n\n\n\n

DBMS<\/strong> : 3 types of DBMS are available, value to choose from MySQL, Oracle and SqlServer (version >=2016).PHP’s PDO extension is used to make the connection.<\/p>\n\n\n\n

\n

If you are using Oracle on a Windows environment, you must install the PDO (PDO_OCI) extension.More information on this link<\/a>.<\/p>\n\n\n\n

In case of an Open Open Bee\u2122 Portal update, you will have to re-apply the PDO configuration in the PHP config.ini configuration file.On a Unix environment, PDO connections to Oracle and SqlServer are not supported, prefer ODBC.<\/p>\n<\/blockquote>\n\n\n\n

\n

Please note that if you choose the Oracle DBMS, Open Bee\u2122 Portal is only compatible with versions 12c or higher. If you are using an older database, please set up an ODBC connection.<\/p>\n<\/blockquote>\n\n\n\n

Host Name<\/strong> : The name or IP address of the machine on your local network that hosts the database you want to query.<\/p>\n\n\n\n

Database Name<\/strong> : The name of the existing database to be queried<\/p>\n\n\n\n

Port<\/strong> : The port used to access the database server.<\/p>\n\n\n\n

Identifier<\/strong> : The identifier used to connect to the database.<\/p>\n\n\n\n

Password<\/strong> : the password linked to the username entered above.<\/p>\n<\/div><\/div>\n\n

<\/div>\n\n

In order to secure your database and avoid errors, please use a user account that has only SELECT rights to your database on the necessary tables.This will prevent data from being inserted, updated, or deleted in your DBMS.<\/p>\n\n

Don’t forget to save the information you have entered by clicking on “Validate<\/strong>“.<\/p>\n","protected":false},"excerpt":{"rendered":"

The “External SQL connections” screen lists the different external SQL connections and allows you to create new ones.An external SQL connection contains the information needed to access an existing database. To create a new connection, click “Add External SQL Connection“.Here are the different fields to fill in: Open Bee\u2122 Portal On Premises Open Bee\u2122 Portal […]<\/p>\n","protected":false},"author":158,"featured_media":0,"parent":20409,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"templates\/ob-help-products.php","meta":{"footnotes":""},"class_list":["post-20448","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages\/20448","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/users\/158"}],"replies":[{"embeddable":true,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/comments?post=20448"}],"version-history":[{"count":10,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages\/20448\/revisions"}],"predecessor-version":[{"id":38744,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages\/20448\/revisions\/38744"}],"up":[{"embeddable":true,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages\/20409"}],"wp:attachment":[{"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/media?parent=20448"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}