Feed aggregator

This tutorial explains how to run Hugging Face LayoutLM model locally with PyCharm remote interpreter. This is cool, because a remote interpreter allows you to run and debug your custom logic, while running Hugging Face model and its dependencies in Docker container. I share Dockerfile, which helps to setup all dependencies. Enjoy!

hi tom i just finished studying about TKPROF and SQLtrace. when we use TKPROF we can analyze which part of the query is having performance problem. my question is why is cpu time is so important? what does cpu time affect? and what is the best method to reduce cpu time on queries thanks

hi tom i have a simple question about result cache. 1.how exactly does the oracle access the result cache? and how does it knew that it is the same sql statement executed last time? 2.when we cache result of the SQL statement into the result cache and re run the query does oracle instantly access the result cache? or does oracle go into the shared sql area first then go to the result cache to get the result thanks

Hello, I've configured RMAN backup with the following schedulation: - FULL Level 0 , every Sunday at 01:05 AM - INCREMENTAL Level 1, from Monday to Saturday at 01:05 AM - Archive Log Backup every day, every 30 minutes The retention policy is set to Redundancy 2: <code> RMAN> show all; RMAN configuration parameters for database with db_unique_name DATABASE are: CONFIGURE RETENTION POLICY TO REDUNDANCY 2; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/DATABASE/autobackup/cf_sp_%F'; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 3; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_DATABASE.f'; # default </code> The Backup script are the following: - Level 0 <code> CONFIGURE RETENTION POLICY TO REDUNDANCY 2; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/DATABASE/autobackup/cf_sp_%F'; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 3; run { backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10; backup AS COMPRESSED BACKUPSET incremental level 0 database format '/u01/app/oracle/backup/DATABASE/data_%d_FULL_bks%s_%T_%U.bck' include current controlfile; backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10; crosscheck backup; delete noprompt obsolete; } </code> - Level 1 <code> CONFIGURE RETENTION POLICY TO REDUNDANCY 2; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/DATABASE/autobackup/cf_sp_%F'; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 3; run { backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10; backup AS COMPRESSED BACKUPSET incremental level 1 database format '/u01/app/oracle/backup/DATABASE/data_%d_FULL_bks%s_%T_%U.bck' include current controlfile; backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10; crosscheck backup; delete noprompt obsolete; } </code> - Archivelog <code> CONFIGURE RETENTION POLICY TO REDUNDANCY 2; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/DATABASE/autobackup/cf_sp_%F'; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 3; run { backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10; crosscheck backup; delete noprompt obsolete; } </code> With Redundancy set to 2, I expected that RMAN keeps 2 FULL Level 0 copies of the database. For example: I took a first FULL Level 0 at 26/12/2021 at 01:05 AM I took a second FULL Level 0 at 02/01/2022 at 01:05 AM On 09/01/2022 I expected that RMAN make a new FULL Level 0 and delete the Level 0 of 26/12/20...

I have been reviewing RMAN RAC backup for environment having a total of 15 non-production and production databases on the same host excluding APX and MGMTDB.

That’s not a big deal, as I have once had to managed 28 databases residing on the same host, right?

I am just too lazy and too tedious to change RMAN configuration one database at a time.

Luckily, there is a convention where non-production instances ends with T1 and production instances ends with P1.

This allows me to make the same changes to non-production and production in 2 steps.

Goal is to configure RMAN PARALLELISM 2 for NON-PROD and PARALLELISM 4 for PROD and consistent RECOVERY WINDOW OF 14 DAYS.

### Current configuration is inconsistent across databases:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;

====================
### NON-PROD: 
====================

--- RMAN commands: cat configure.rman:
set echo on
connect target;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
show all;
exit

--- Let's make sure the instances are correct:
$ ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -Ev '\P1'|sort
DB01T1
DB02T1
DB03T1

--- Make the change:
$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -Ev '\P1'|sort); do echo 'RMAN configure' $db; . oraenv <<< $db; rman @configure.rman; done;

====================
### PROD:
====================

--- RMAN commands: cat configure.rman:
set echo on
connect target;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;
show all;
exit

--- Let's make sure the instances are correct:
$ ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -E '\P1'|sort
DB01P1
DB02P1
DB03P1

--- Make the change:
$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -E '\P1'|sort); do echo 'RMAN configure' $db; . oraenv <<< $db; rman @configure.rman; done;

This article is part of a series that includes SQLite, Postgresql, Firebird, Oracle RDBMS, Microsoft SQL Server, HSQLDB, MongoDB, and MariaDB. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
Like MongoDB, Excel is not a relational data source, but the ODBC API will allow to access its data using SQL statements. The idea is to access spreadsheet files as if they were relational databases, each sheet being a table, each line a table’s row and each column a table’s column. The first row of a sheet contains the column headers. This is quite self-evident; see the screen copy down below.
As the Excel sheet is the database, there is no server software to install but only the ODBC drivers.
Let’s create a spreadsheet with the data sample from the site. We will just query an existing database, e.g. the PostgreSQL one if the instructions here were applied, and extract the content of the tables, as shown below:

$ psql sampledb
\a
Output format is unaligned.
sampledb=> select * from regions;
region_id|region_name
1|Europe
2|Americas
3|Asia
4|Middle East and Africa
(4 rows)

sampledb=> select * from countries;
country_id|country_name|region_id
AR|Argentina|2
AU|Australia|3
BE|Belgium|1
BR|Brazil|2
...
(29 rows)

and so on with the other tables locations, departments, jobs, employees and dependents.
We will simply create one spreadsheet using LibreOffice Calc or Excel, create one tab per table with the table name as its name, paste the data from the above output with the column headers as the first line. Be sure to select the ‘|’ character as the column separator and remove spurious lines and columns. This step is manual and a bit tedious, but it’ll only take a few minutes.
The image below shows how the spreadsheet may look like with some data in it:

If using isql to extract the data into a tabular format, spurious blanks are added, e.g.:

$ isql -v mysqldb -v debian debian
SQL> select * from regions;
+------------+--------------------------+
| region_id  | region_name              |
+------------+--------------------------+
| 1          | Europe                   |
| 2          | Americas                 |
| 3          | Asia                     |
| 4          | Middle East and Africa   |
+------------+--------------------------+
SQLRowCount returns 4

SQL> select * from countries;
+-----------+-----------------------------------------+------------+
| country_id| country_name                            | region_id  |
+-----------+-----------------------------------------+------------+
| AR        | Argentina                               | 2          |
| AU        | Australia                               | 3          |
| BE        | Belgium                                 | 1          |
...
| ZM        | Zambia                                  | 4          |
| ZW        | Zimbabwe                                | 4          |
+-----------+-----------------------------------------+------------+
SQLRowCount returns 29
29 rows fetched

This leading and trailing blanks must be removed as they don’t belong to the data but were added by isql to format the table so that it displays nicely. The following gawk script can be used to this effect:

for t in {regions,countries,locations,departments,jobs,employees,dependents}; do echo "select * from $t;" | isql -v mysqldb debian debian | gawk -v table_name=$t 'BEGIN {
   print "table", table_name
   do {
      getline
   } while (0 == index($0, "SQL>"))
   n = split ($0, t, "+")
   delete t[1]
   FIELDWIDTHS = "1"
   for (i = 2; i < n; i++)
      FIELDWIDTHS = FIELDWIDTHS sprintf(" %d 1", length(t[i]))
   FIELDWIDTHS = FIELDWIDTHS " 1"
   bHeader = 0
}
{
   bHeader++
   if (index($0, "+-"))
      if (2 == bHeader)
         next
      else
         exit
   for (i = 2; i < NF; i += 2) {gsub(/(^ +)|( +$)/, "", $i); printf("%s|", $i)}
   printf "\n"
}
END {
   printf "\n"
}';
done
Output:
table regions
region_id|region_name|
1|Europe|
2|Americas|
3|Asia|
4|Middle East and Africa|

table countries
country_id|country_name|region_id|
AR|Argentina|2|
...
etc...

If no prior database with the sample data is available, just save the data from here into a text file, say populate-tables.sql, save the following gawk script sql2flat.awk:

# convert sql statements into csv;
# INSERT INTO regions(region_id,region_name) VALUES (1,'Europe');
# becomes
#    1|Europe
# INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US');
# becomes
#    1400|2014 Jabberwocky Rd|26192|Southlake|Texas|US
# sql comes from here for example: https://www.sqltutorial.org/wp-content/uploads/2020/04/postgresql-data.txt;
# Usage:
#   gawk -v sep=separator -f sql2csv.awk data.sql
# Example:
#   gawk -v sep="|" -f sql2csv.awk populate-tables.sql | tee populate-tables.csv
{
   if (match($0, /INSERT INTO ([^)]+)\(.+\) VALUES \((.+)\)/, m)) {
      if (m[1] != ex_table) {
         ex_table = m[1]
         print "# tab", m[1] ":"
      }
      gsub(/'|"/, "", m[2])
      n = split(m[2], data, ",")
      for (i = 1; i  1 ≤ n; i++)
         printf("%s%s", i > 1 ? sep : "", data[i])
      printf "\n"
   }
}

and run it on the data file:

gawk -v sep="|" -f sql2flat.awk populate-tables.sql

Snippet of output:

# tab regions:
1|Europe
2|Americas
3|Asia
4|Middle East and Africa
# tab countries:
AR|Argentina|2
AU|Australia|3
BE|Belgium|1
BR|Brazil|2
...
# tab locations:
1400|2014 Jabberwocky Rd|26192|Southlake|Texas|US
1500|2011 Interiors Blvd|99236|South San Francisco|California|US
...
# tab jobs:
1|Public Accountant|4200.00|9000.00
2|Accounting Manager|8200.00|16000.00
3|Administration Assistant|3000.00|6000.00
...
# tab departments:
1|Administration|1700
2|Marketing|1800
...
# tab employees:
100|Steven|King|steven.king@sqltutorial.org|515.123.4567|1987-06-17|4|24000.00|NULL|9
101|Neena|Kochhar|neena.kochhar@sqltutorial.org|515.123.4568|1989-09-21|5|17000.00|100|9
102|Lex|De Haan|lex.de haan@sqltutorial.org|515.123.4569|1993-01-13|5|17000.00|100|9
...
# tab dependents:
1|Penelope|Gietz|Child|206
2|Nick|Higgins|Child|205
3|Ed|Whalen|Child|200
...

Next, copy and paste the above output into the respective sheet’s tabs.
After the edition is completed, save the file as SampleWorkbook.xlsx (for compatibility, select the output format as Excel file if using LibreOffice Calc) and keep a copy of it somewhere in case it becomes corrupted during the tests.
We will use the commercial ODBC drivers provided by cdata (https://www.cdata.com/odbc/); other vendors are e.g. Easysoft (https://www.easysoft.com/developer/interfaces/odbc/index.html) and Devart (https://www.devart.com/odbc/).
Download the drivers from https://www.cdata.com/drivers/excel/odbc/ by following the instructions on the screen, and install them as root:

# dpkg -i ExcelODBCDriverforUnix.deb 

Run the licensing script and follow the on-screen instructions:

# cd /opt/cdata/cdata-odbc-driver-for-excel/bin/
# ./install-license.x64 

*************************************************************************
Please provide your name and email to install a trial license.

To install a full version license pass your product key as a parameter.
For instance: ./install-license MY_PRODUCT_KEY

Please refer to the documentation for additional details.
*************************************************************************

Name: debian
Email: ...
Installing TRIAL license...
Downloading license data...
Verifying license data...
License installation succeeded.

Check the system-wide installed drivers:

# odbcinst -q -d
...
[CData ODBC Driver for Excel]

# odbcinst -q -d -n "CData ODBC Driver for Excel"
[CData ODBC Driver for Excel]
Description=CData ODBC Driver for Excel 2021
Driver=/opt/cdata/cdata-odbc-driver-for-excel/lib/libexcelodbc.x64.so
UsageCount=1
Driver64=/opt/cdata/cdata-odbc-driver-for-excel/lib/libexcelodbc.x64.so

Check the system-wide DSN in /etc/odbc.ini:

# odbcinst -q -s
...
[CData-Excel-Source]

# odbcinst -q -s -n "CData-Excel-Source"
[CData Excel Source]
Driver=CData ODBC Driver for Excel
AWS Access Key=
AWS Region=NORTHERNVIRGINIA
AWS Role ARN=
AWS Secret Key=
Azure Access Key=
Azure Shared Access Signature=
Azure Storage Account=
Azure Tenant=
MFA Serial Number=
MFA Token=
Password=
Share Point Edition=SharePointOnline
SSL Mode=AUTOMATIC
User=

Copy the newly inserted DSN into debian’s own ~/.odbc.ini file and append the URI setting as follows:

$ vi ~/.odbc.ini
...
[CData-Excel-Source]
Driver=CData ODBC Driver for Excel
AWS Access Key=
AWS Region=NORTHERNVIRGINIA
AWS Role ARN=
AWS Secret Key=
Azure Access Key=
Azure Shared Access Signature=
Azure Storage Account=
Azure Tenant=
MFA Serial Number=
MFA Token=
Password=
Share Point Edition=SharePointOnline
SSL Mode=AUTOMATIC
User=
URI=/home/debian/odbc4gawk/SampleWorkbook.xlsx

As debian, check its DSN so far:

$ odbcinst -q -s
[mysqlitedb]
[mysqldb]
[myfbdb]
[myfbdb_Devart]
[mypostgresqldb]
[OracleODBC-21]
[mymssqlserverdb]
[myhsqldb]
[mymongodb]
[DEVART_MONGODB]
[DEVART_FIREBIRD]
[CData-Excel-Source]

$ odbcinst -q -s -n [CData-Excel-Source]
[CData-Excel-Source]
Driver=CData ODBC Driver for Excel
AWS Access Key=
AWS Region=NORTHERNVIRGINIA
AWS Role ARN=
AWS Secret Key=
Azure Access Key=
Azure Shared Access Signature=
Azure Storage Account=
Azure Tenant=
MFA Serial Number=
MFA Token=
Password=
Share Point Edition=SharePointOnline
SSL Mode=AUTOMATIC
User=
URI=/media/sf_customers/dbi/odbc4gawk/SampleWorkbook.xlsx

Try a connection to the spreadsheet via the ODBC Driver Manager’s isql tool:

# isql -v "CData Excel Source"
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> help
+-----------+-------------+-------------------------------------------------------------------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME    | TABLE_TYPE | REMARKS                                    |
+-----------+-------------+---------------+------------+--------------------------------------------+
| CData     | Excel       | regions       | TABLE      | Retrieve data from the "regions" sheet.    |
| CData     | Excel       | countries     | TABLE      | Retrieve data from the "countries" sheet.  |
| CData     | Excel       | locations     | TABLE      | Retrieve data from the "locations" sheet.  |
| CData     | Excel       | department    | TABLE      | Retrieve data from the "department" sheet. |
| CData     | Excel       | employees     | TABLE      | Retrieve data from the "employees" sheet.  |
| CData     | Excel       | dependent     | TABLE      | Retrieve data from the "dependent" sheet.  |
| CData     | Excel       | jobs          | TABLE      | Retrieve data from the "jobs" sheet.       |
+-----------+-------------+---------------+------------+--------------------------------------------+
SQLRowCount returns -1
7 rows fetched

Interesting how the driver presents the data dictionary.
Run the test query:

SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN');
Output:
+----------------------------+-------------+------------+-------------------------------------------+----------------------+
| country_name               | country_id  | country_id | street_address                            | city                 |
+----------------------------+-------------+------------+-------------------------------------------+----------------------+
| United Kingdom             | UK          | UK         | 8204 Arthur St                            | London               |
| United Kingdom             | UK          | UK         | Magdalen Centre, The Oxford Science Park  | Oxford               |
| United States of America   | US          | US         | 2014 Jabberwocky Rd                       | Southlake            |
| United States of America   | US          | US         | 2011 Interiors Blvd                       | South San Francisco  |
| United States of America   | US          | US         | 2004 Charade Rd                           | Seattle              |
| China                      | CN          |            |                                           |                      |
+----------------------------+-------------+------------+-------------------------------------------+----------------------+
SQLRowCount returns -1
6 rows fetched

The join was performed as expected.
Let’s now see how the driver behaves when used with python the module pyodbc:

$ python3
import pyodbc Python 3.9.2 (default, Feb 28 2021, 17:03:44) 
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.

import pyodbc

# connect using the DSN;
cnxn = pyodbc.connect(DSN='Cdata-Excel-Source')
# or:
# connect directly using the DRIVER definition, no DSN;
cnxn = pyodbc.connect('DRIVER={CData ODBC Driver for Excel};URI=/media/sf_customers/dbi/odbc4gawk/SampleWorkbook.xlsx')

cursor = cnxn.cursor()      
cursor.execute("""SELECT
               c.country_name,
               c.country_id,
               l.country_id,
               l.street_address,
               l.city
       FROM
               countries c
       LEFT JOIN locations l ON l.country_id = c.country_id
       WHERE
               c.country_id IN ('US', 'UK', 'CN')""") 

row = cursor.fetchone() 
while row:
     print (row) 
     row = cursor.fetchone()

Output:
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre - The Oxford Science Park', 'Oxford')
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')
('China', 'CN', None, None, None)

Excel spreadsheets are now accessible via ODBC under the debian account.
Admittedly, Excel sheets are no natural and reliable data sources for too many reasons to mention here (but they have other advantages) but it is quite impressive and almost magical to query them using SQL vs. some low-level cell-oriented API !
Instructions for the other data sources can be accessed through the following links:
SQLite
Firebird
HSQLDB
MariaDB
PostgreSQL
Oracle
Microsoft SQLServer for Linux
MongoDB

Cet article Installing the cdata ODBC drivers for Excel est apparu en premier sur Blog dbi services.

This article is part of a series that includes SQLite, Postgresql, Firebird, Oracle RDBMS, Microsoft SQL Server, HSQLDB, Excel, and MariaDB. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
MongoDB is a noSQL database but the ODBC API will hide this fact and allow to access its data using SQL statements. This is to show that with the appropriate ODBC drivers doing the translation, any data source can be accessed using SQL. Not all native operations are rendered correctly of course (notably SELECT’s JOIN clauses, see below) but the main ones, the CRUD, such as INSERT, SELECT, UPDATE and DELETE are all available.
As root, install MongoDB Community Edition and its command-line tool from the official package repositories:

# apt install mongodb
# apt install mongodb-mongosh

Follow the instructions here to configure and start mongodb.
A systemd service has been created. Still as root, start the service as shown below:

# ulimit -n 64000
# chown -R mongodb:mongodb /var/lib/mongodb
# systemctl stop mongod
# systemctl start mongod
# systemctl status mongod
● mongod.service - MongoDB Database Server
     Loaded: loaded (/lib/systemd/system/mongod.service; disabled; vendor preset: enabled)
     Active: active (running) since Tue 2021-09-21 09:27:47 CEST; 3s ago
       Docs: https://docs.mongodb.org/manual
   Main PID: 38200 (mongod)
     Memory: 9.8M
        CPU: 26ms
     CGroup: /system.slice/mongod.service
             └─38200 /usr/bin/mongod --config /etc/mongod.conf

Check the MongoDB server availability by connecting to the server through the mongosh tool documented here:

# mongosh
Current Mongosh Log ID:	6149ba4e6d2a951c4e869dac
Connecting to:		mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000
Using MongoDB:		5.0.3
Using Mongosh:		1.0.6
test> quit

The data sample site does not provide scripts for creating and populating MongoDB collections. There are 2 ways to work around this:
1. use isql and ODBC to feed SQL statements to MongoDB;
2. translate SQL statements to javascripts ones suitable for MongoDB;
At this stage, ODBC drivers are not installed yet and therefore isql cannot work so we will select the 2nd alternative. The script below will do just that. As a Mongo database is schema-less, there is no need to create collections beforehand, they will be implicitely created when populated with documents. Any sample populating script will do so we’ll take the one for mysql here and save it to the file data4mongodb.sql. The script pop-mongodb.awk will convert SQL insert statements into MongoDB statements. Here it is:

# format of the input lines, e.g:
#   INSERT INTO regions(region_id,region_name) VALUES (1,'Europe');
# or:
#   INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');
# Output:
#    db.regions.insert({
#    region_id: 1,
#    region_name: "Europe"
#    })
# or:
#    db.locations.insert({
#    location_id: 2500,
#    street_address: "Magdalen Centre, The Oxford Science Park",
#    postal_code: "OX9 9ZB",
#    city: "Oxford",
#    state_province: "Oxford",
#    country_id: "UK"
#    })
# Usage:
#    gawk -v Apostrophe="'" -f pop-mongodb.awk data4mongodb.sql > data4mongodb.json
BEGIN {
   ApoRE="(([^,]+)|(" Apostrophe "([^" Apostrophe "]+)" Apostrophe ")),?" # i.e. (([^,]+)|('([^']+)')),?
   print "use sampledb"
}
{
   if (!$0 || match($0, /^\/\*/)) next

   match($0, /INSERT INTO ([^(]+)\(([^)]+)\)/, stmt)
   table_name = stmt[1]
   if (!bemptied[table_name]) {
      print "db." table_name ".deleteMany({})"
      bemptied[table_name] = 1
   }

   nb_columns = split(stmt[2], columns, ",")

   nb = match($0, /VALUES \(([^)]+)\)/, stmt)
   S = stmt[1]
   nb_values = 0
   while (match(S, ApoRE, res)) {
      values[++nb_values] = res[1]
      S = substr(S, RLENGTH + 1)
   }

   print "db." table_name ".insert({"
   for (i = 1; i <= nb_columns; i++) {
      if ("NULL" == values[i])
         values[i] = "null"
      gsub(Apostrophe, "\"", values[i])
      print columns[i] ": " values[i] (i < nb_columns ? "," : "")
   }
   print "})"
   printf "\n"
}

Invoke it:

gawk -v Apostrophe="'" -f pop-mongodb.awk data4mongodb.sql > data4mongodb.json

Example of input:

INSERT INTO regions(region_id,region_name) VALUES (1,'Europe');
...
INSERT INTO countries(country_id,country_name,region_id) VALUES ('AR','Argentina',2);
...
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US');
...
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (1,'Public Accountant',4200.00,9000.00);
...
INSERT INTO departments(department_id,department_name,location_id) VALUES (1,'Administration',1700);
...
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (100,'Steven','King','steven.king@sqltutorial.org','515.123.4567','1987-06-17',4,24000.00,NULL,9);
...
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (1,'Penelope','Gietz','Child',206);
...

and their corresponding generated json statements:

use sampledb
db.regions.deleteMany({})
db.regions.insert({
region_id: 1,
region_name: "Europe",
})
...
db.countries.deleteMany({})
db.countries.insert({
country_id: "AR",
country_name: "Argentina",
region_id: 2,
})
...
db.locations.deleteMany({})
db.locations.insert({
location_id: 1400,
street_address: "2014 Jabberwocky Rd",
postal_code: "26192",
city: "Southlake",
state_province: "Texas",
country_id: "US",
})
...
db.jobs.deleteMany({})
db.jobs.insert({
job_id: 1,
job_title: "Public Accountant",
min_salary: 4200.00,
max_salary: 9000.00,
})
...
db.departments.deleteMany({})
db.departments.insert({
department_id: 1,
department_name: "Administration",
location_id: 1700,
})
...
db.employees.deleteMany({})
db.employees.insert({
employee_id: 100,
first_name: "Steven",
last_name: "King",
email: "steven.king@sqltutorial.org",
phone_number: "515.123.4567",
hire_date: "1987-06-17",
job_id: 4,
salary: 24000.00,
manager_id: null,
department_id: 9,
})
...
db.dependents.deleteMany({})
db.dependents.insert({
dependent_id: 1,
first_name: "Penelope",
last_name: "Gietz",
relationship: "Child",
employee_id: 206,
})

To be sure the populating step is idempotent, the collections are emptied each time the script is run.
From mongosh, populate the tables as shown:

mongosh < data4mongodb.isql

Back in mongosh, check the data:

mongosh  show databases
admin       41 kB
config     111 kB
local     81.9 kB
sampledb   516 kB

test> use sampledb
switched to db sampledb

sampledb> show collections
countries
departments
dependents
employees
jobs
locations
regions

sampledb> db.regions.find()
[
  {
    _id: ObjectId("616eef8e230e4e4893edd45f"),
    region_id: 1,
    region_name: 'Europe'
  },
...
sampledb> db.countries.find()
[
  {
    _id: ObjectId("616eef8f230e4e4893edd463"),
    country_id: 'AR',
    country_name: 'Argentina',
    region_id: 2
  },
...
sampledb> db.locations.find()
[
  {
    _id: ObjectId("616eef91230e4e4893edd47c"),
    location_id: 1400,
    street_address: '2014 Jabberwocky Rd',
    postal_code: '26192',
    city: 'Southlake',
    state_province: 'Texas',
    country_id: 'US'
  },
...
sampledb> db.jobs.find()
[
  {
    _id: ObjectId("616eef92230e4e4893edd483"),
    job_id: 1,
    job_title: 'Public Accountant',
    min_salary: 4200,
    max_salary: 9000
  },
...
sampledb> db.departments.find()
[
  {
    _id: ObjectId("616eef94230e4e4893edd496"),
    department_id: 1,
    department_name: 'Administration',
    location_id: 1700
  },
...
sampledb> db.employees.find()
[
  {
    _id: ObjectId("616eef95230e4e4893edd4a1"),
    employee_id: 100,
    first_name: 'Steven',
    last_name: 'King',
    email: 'steven.king@sqltutorial.org',
    phone_number: '515.123.4567',
    hire_date: '1987-06-17',
    job_id: 4,
    salary: 24000,
    manager_id: null,
    department_id: 9
  },
...
sampledb> db.dependents.find()
[
  {
    _id: ObjectId("616eef9c230e4e4893edd4c9"),
    dependent_id: 1,
    first_name: 'Penelope',
    last_name: 'Gietz',
    relationship: 'Child',
    employee_id: 206
  },
...
sampledb> quit

The MongoDB ODBC drivers are available here. They are a modified version of MySQL ODBC driver. However, they don’t work on my test machine running Debian Linux v11 because of a missing openssl v1.0.2 library, which was predictable because those drivers are several years old and my test machine runs the latest Debian linux. For this reason, a commercial replacement from Devart has been installed; it comes with a one-month evaluation period. Once registered, it can be downloaded and installed as root as follows:

# wget https://www.devart.com/odbc/mongodb/devartodbcmongo_amd64.deb
# apt install /home/debian/Downloads/devartodbcmongo_amd64.deb

Check the system-wide installed drivers:

# odbcinst -q -d
...
[MongoDB Unicode]
[MongoDB ANSI]
...
[Devart ODBC Driver for MongoDB]
# odbcinst -q -d -n "Devart ODBC Driver for MongoDB"
[Devart ODBC Driver for MongoDB]
Driver=/usr/local/lib/libdevartodbcmongo.so

Check the system-wide DSN in /etc/odbc.ini:

# odbcinst -q -s
...
[DEVART_MONGODB]
#  odbcinst -q -s -n "DEVART_MONGODB"
[DEVART_MONGODB]
Description=My MongoDB sample database
Driver=Devart ODBC Driver for MongoDB
Data Source=
Port=27017
Database=sampledb
User ID=
Password=
Client Library=
BSON Library=
Additional Servers=
Connection Options=

Copy the newly inserted DSN into debian’s ~/.odbc.ini file:

$ vi ~/.odbc.ini
...
[DEVART_MONGODB]
Description=My MongoDB sample database
Driver=Devart ODBC Driver for MongoDB
Data Source=
Port=27017
Database=sampledb
User ID=
Password=
Client Library=
BSON Library=
Additional Servers=
Connection Options=

As debian, check its DSN so far:

$ odbcinst -q -s
...
[DEVART_MONGODB]
...

$ odbcinst -q -s -n DEVART_MONGODB
[DEVART_MONGODB]
Description=My MongoDB sample database
Driver=Devart ODBC Driver for MongoDB
Data Source=
Port=27017
Database=sampledb
User ID=
Password=
Client Library=
BSON Library=
Additional Servers=
Connection Options=

Try a connection to the MongoDB database via ODBC:

isql -v DEVART_MONGODB
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> help
+-----------+-------------+-------------+------------+---------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME  | TABLE_TYPE | REMARKS |
+-----------+-------------+-------------+------------+---------+
| sampledb  |             | countries   | TABLE      |         |
| sampledb  |             | dependents  | TABLE      |         |
| sampledb  |             | regions     | TABLE      |         |
| sampledb  |             | locations   | TABLE      |         |
| sampledb  |             | departments | TABLE      |         |
| sampledb  |             | jobs        | TABLE      |         |
| sampledb  |             | employees   | TABLE      |         |
+-----------+-------------+-------------+------------+---------+ 
SQLRowCount returns -1
7 rows fetched

It looks good. Run the test query now:

SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN');
Output:
+--------------+-----------------+-----------------+----------------+------+
| country_name | country_id      | country_id_1    | street_address | city |
+--------------+-----------------+-----------------+----------------+------+
| China        | CN              |                 |                |      |
+--------------+-----------------+-----------------+----------------+------+ 
SQLRowCount returns -1
1 rows fetched

Only one row is returned because the left join is not implemented in the combo MongoDB/ODBC driver. In effect, joins in relational database are a necessity due to the data normalization; as MongoDB does not care about data duplication and works with complete, self-standing documents, no joins are necessary although inner queries can be simulated with aggregate() and lookup() as illustrated by the following equivalent query:

db.countries.aggregate([
   {
      $match:{$or:[{"country_id" : "US"}, {"country_id" : "UK"}, {"country_id" : "CN"}]}
   },
   {
     $lookup:
       {
         from: "locations",
         localField: "country_id",
         foreignField: "country_id",
         as: "r"
       }
  }
])

with its result:

[
  {
    _id: ObjectId("616eef90230e4e4893edd469"),
    country_id: 'CN',
    country_name: 'China',
    region_id: 3,
    r: []
  },
  {
    _id: ObjectId("616eef91230e4e4893edd478"),
    country_id: 'UK',
    country_name: 'United Kingdom',
    region_id: 1,
    r: [
      {
        _id: ObjectId("616eef92230e4e4893edd480"),
        location_id: 2400,
        street_address: '8204 Arthur St',
        postal_code: null,
        city: 'London',
        state_province: null,
        country_id: 'UK'
      },
      {
        _id: ObjectId("616eef92230e4e4893edd481"),
        location_id: 2500,
        street_address: 'Magdalen Centre, The Oxford Science Park',
        postal_code: 'OX9 9ZB',
        city: 'Oxford',
        state_province: 'Oxford',
        country_id: 'UK'
      }
    ]
  },
  {
    _id: ObjectId("616eef91230e4e4893edd479"),
    country_id: 'US',
    country_name: 'United States of America',
    region_id: 2,
    r: [
      {
        _id: ObjectId("616eef91230e4e4893edd47c"),
        location_id: 1400,
        street_address: '2014 Jabberwocky Rd',
        postal_code: '26192',
        city: 'Southlake',
        state_province: 'Texas',
        country_id: 'US'
      },
      {
        _id: ObjectId("616eef91230e4e4893edd47d"),
        location_id: 1500,
        street_address: '2011 Interiors Blvd',
        postal_code: '99236',
        city: 'South San Francisco',
        state_province: 'California',
        country_id: 'US'
      },
      {
        _id: ObjectId("616eef91230e4e4893edd47e"),
        location_id: 1700,
        street_address: '2004 Charade Rd',
        postal_code: '98199',
        city: 'Seattle',
        state_province: 'Washington',
        country_id: 'US'
      }
    ]
  }
]

To easy up the comparaison, we save that output to the file mongodb.out and run the following commands to reformat it into a tabular presentation.
First, convert mongodb.out to correct json syntax:

$ gawk -v Apo="'" '{
   gsub(Apo, "\"", $0)
   if (match($1, /^_id/)) next
   print gensub(/^( +)([^:]+)(:.+$)/, "\\1\"\\2\"\\3", "g", $0)
}' mongodb.out > mongodb.json

Now, save the following python script into the python script mongodb2tab.py:

$ cat - <<eop mongodb2tab.py
import os
import json
with open("./mongodb.json") as json_file:
   mongodb_out = json.load(json_file)
widths = [25, 10, 10, 42, 10]
print(f"{'country_name': <{widths[0]}}  {'country_id': <{widths[1]}}  {'country_id': <{widths[2]}} \ {'street_address': <{widths[3]}}  {'city': <{widths[4]}}")
for row in mongodb_out:
   if row['r']:
      for l in row['r']:
         print(f"{row['country_name']: <{widths[0]}}  {row['country_id']: <{widths[1]}}  {l['country_id']: <{widths[2]}}  {l['street_address']: <{widths[3]}}  {l['city']: <{widths[4]}}")
   else:
      print(f"{row['country_name']: <{widths[0]}}  {row['country_id']: <{widths[1]}}")
eop

Finally, execute it:

$ python3 mongodb2tab.py
Output:
country_name               country_id  country_id  street_address                              city      
China                      CN        
United Kingdom             UK          UK          8204 Arthur St                              London    
United Kingdom             UK          UK          Magdalen Centre, The Oxford Science Park    Oxford    
United States of America   US          US          2014 Jabberwocky Rd                         Southlake 
United States of America   US          US          2011 Interiors Blvd                         South San Francisco
United States of America   US          US          2004 Charade Rd                             Seattle   

Which shows that the output of the MongoDb query to simulate the left outer join was correct.
Let’s now test the DSN with pyodbc:

$ python3
import pyodbc 
cnxn = pyodbc.connect(DSN='mymongodb_Devart')
cursor = cnxn.cursor()	
cursor.execute("""SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN')""")
row = cursor.fetchone() 
while row:
    print (row) 
    row = cursor.fetchone()
Output:
('China', 'CN', None, None, None)

Here too, there are missing values as expected.
MongoDB is now accessible from ODBC under the debian account, albeit not all SQL statements are fully supported, which is understandable with a NoSQL database.
Instructions for the other data sources can be accessed through the following links:
SQLite
Firebird
HSQLDB
MariaDB
PostgreSQL
Oracle
Microsoft SQLServer for Linux
Excel

Cet article Installing the ODBC drivers for MongoDB est apparu en premier sur Blog dbi services.

This article is part of a series that includes SQLite, Postgresql, Firebird, Oracle RDBMS, HSQLDB, MariaDB, MongoDB, and Excel. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
As root, follow the documentation here to get and install sqlserver express for Linux. Here are the needed steps:

# wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
# add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"
# apt-get install -y mssql-server
# /opt/mssql/bin/mssql-conf setup

A systemd service was set up and launched:

# systemctl status mssql-server --no-pager
● mssql-server.service - Microsoft SQL Server Database Engine
     Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2021-08-27 15:22:14 CEST; 15s ago
       Docs: https://docs.microsoft.com/en-us/sql/linux
   Main PID: 7795 (sqlservr)
      Tasks: 120
     Memory: 880.5M
        CPU: 5.983s
     CGroup: /system.slice/mssql-server.service
             ├─7795 /opt/mssql/bin/sqlservr
             └─7817 /opt/mssql/bin/sqlservr

Get and install the ODBC drivers for sqlserver:

# apt install tdsodbc
# apt install curl
# curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
# apt-get update 
# apt-get install mssql-tools unixodbc-dev

Check that the device has been added system-wide:

# odbcinst -q -d
...
[ODBC Driver 17 for SQL Server]

# odbcinst -q -d -n "ODBC Driver 17 for SQL Server"
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1

As debian, create the sampledb database using the native administration tool sqlcmd:

$ sqlcmd -S localhost -U SA -P admin2021!
CREATE DATABASE sampledb
go
1> SELECT Name from sys.Databases;
2> go
Name                                                                                                                            
-----------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
sampledb                                                                                                                        

(5 rows affected)

Populate the sampledb database; statements for table creation for slqserver are available here and the ones to populate the tables here.
Click and save the files to create_tables_mssql.sql respectively populate_tables_mssql.sql.
Execute the above SQL scripts still using the default command-line administration tool, sqlcmd:

$ sqlcmd -S localhost -U SA -P admin2021! -i create_tables_mssql.sql
$ sqlcmd -S localhost -U SA -P admin2021! -i populate_tables_mssql.sql

Let’s check the data:

cat - <<eot | sqlcmd -S localhost -U SA -P admin2021!
USE sampleDB
SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN');
eot
Changed database context to 'sampledb'.
country_name                             country_id country_id street_address                           city                 
---------------------------------------- ---------- ---------- ---------------------------------------- ---------------------
China                                    CN         NULL       NULL                                     NULL                          
United Kingdom                           UK         UK         8204 Arthur St                           London                        
United Kingdom                           UK         UK         Magdalen Centre, The Oxford Science Park Oxford                        
United States of America                 US         US         2014 Jabberwocky Rd                      Southlake                     
United States of America                 US         US         2011 Interiors Blvd                      South San Francisco           
United States of America                 US         US         2004 Charade Rd                          Seattle                       

(6 rows affected)
SQL> 

Configure an ODBC DSN by editing the user’s DSN file:

$ vi ~/.odbc.ini
...
[mymssqlserverdb]
Driver = ODBC Driver 17 for SQL Server
Server = localhost
#Port = 1433
User = SA
Password = admin2021!
Database = sampledb
Language = us_english
NeedODBCTypesOnly = 1

Check the DSN:

$ odbcinst -q -s
...
[mymssqlserverdb]

$ odbcinst -q -s -n mymssqlserverdb
[mymssqlserverdb]
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1
Server=localhost
Port=
Database=sampledb
User=SA
Password=admin2021!
Language=us_english
NeedODBCTypesOnly=1

Try a connection to the mssql db via ODBC using the ODBC Driver Manager test tool, isql:

$ isql -v mymssqlserverdb SA admin2021!
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN');
Output:
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| country_name                            | country_id| country_id| street_address                          | city          |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| China                                   | CN        |           |                                         |               |
| United Kingdom                          | UK        | UK        | 8204 Arthur St                          | London        |
| United Kingdom                          | UK        | UK        | Magdalen Centre, The Oxford Science Park| Oxford        |
| United States of America                | US        | US        | 2014 Jabberwocky Rd                     | Southlake     |
| United States of America                | US        | US        | 2011 Interiors Blvd                     | South San Francisco           |
| United States of America                | US        | US        | 2004 Charade Rd                         | Seattle       |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
SQLRowCount returns 6
6 rows fetched

Test the DSN with the python module pyodbc:

$ python3
import pyodbc 
cnxn = pyodbc.connect(DSN='mymssqlserverdb;UID=SA;PWD=admin2021!')
cursor = cnxn.cursor()	
cursor.execute("""SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN')""")
row = cursor.fetchone() 
while row:
    print (row) 
    row = cursor.fetchone()
Output:
('China', 'CN', None, None, None)
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')

mssqlservr is now fully accessible from any ODBC application under the debian account.
Instructions for the other data sources can be accessed through the following links:
SQLite
Firebird
HSQLDB
MariaDB
PostgreSQL
Oracle
MongoDB
Excel

Cet article Installing the ODBC drivers for Microsoft SQLServer for Linux est apparu en premier sur Blog dbi services.

This article is part of a series that includes SQLite, Postgresql, Firebird, Oracle RDBMS, Microsoft SQL Server, MariaDB, MongoDB, and Excel. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
As user debian, follow the documentation here and here to get and install hsqldb for Linux. Here are the needed steps:

$ export workdir=~/odbc4gawk
$ mkdir $workdir
$ cd $workdir
$ wget https://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_2_6/hsqldb-2.6.0.zip/download
$ mv download hsqldb-2.6.0.zip
$ unzip hsqldb-2.6.0.zip

As root, install a system-wide JDK from the official packages repository:

# apt install openjdk-17-jdk
# java -version
openjdk version "17-ea" 2021-09-14
OpenJDK Runtime Environment (build 17-ea+19-Debian-1)
OpenJDK 64-Bit Server VM (build 17-ea+19-Debian-1, mixed mode, sharing)

No special action is needed for the ODBC drivers because hsqldb starting in v2.0 can use the PostgreSQL ones, a clever decision; why reinventing the wheel when a few modifications are enough to make HSQLDB compatible with mainstream ODBC drivers ? Moreover, the choice of those drivers make sense as PostgreSQL is here to stay. If needed, please refer to the article Installing the ODBC drivers for PostgreSQL for step by step instructions. Later, we will only have to create a DSN for the hsqldb database.
As debian, start the database server process and send it to the background:

$ cd $workdir/hsqldb-2.6.0/hsqldb/lib
$ java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:sampledb --dbname.0 xdb &

Connect to the local hsqldb service using the hsqldb’s provided administration application:

$ java -jar ../lib/sqltool.jar --inlineRc=url=jdbc:hsqldb:localhost/runtime,user=sa

Note that the tool uses JDBC for the connection, the java counterpart to ODBC. From that tool, create the tables for hsqldb using the Oracle script here; save it into the text file create_tables_hsqldb.sql, edit it and change occurrences of NUMBER to INT and occurrences of VARCHAR2 to VARCHAR using your favorite text editor. Finally, execute it:

\i /home/debian/odbc4gawk/create_tables_hsqldb.sql

Populate the tables for hsqldb using the Oracle script here; save it into the text file populate_tables_hsqldb.sql and execute it unchanged.

\i /home/debian/odbc4gawk/populate_tables_hsqldb.sql

Check the data:

sql> SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN');
Output:
COUNTRY_NAME              COUNTRY_ID  COUNTRY_ID  STREET_ADDRESS                            CITY
------------------------  ----------  ----------  ----------------------------------------  -------------------
China                     CN                      [null]                                    [null]
United Kingdom            UK          UK          8204 Arthur St                            London
United Kingdom            UK          UK          Magdalen Centre, The Oxford Science Park  Oxford
United States of America  US          US          2014 Jabberwocky Rd                       Southlake
United States of America  US          US          2011 Interiors Blvd                       South San Francisco
United States of America  US          US          2004 Charade Rd                           Seattle

Fetched 6 rows.

The data are OK.
Configure an ODBC DSN by editing the user’s DSN file:

$ vi ~/.odbc.ini
...
[myhsqldb]
Description=My Postgresql sample database
Driver=PostgreSQL Unicode
Database=sampledb

On line 4, we have specified that the PostgreSQL ODBC driver is to be used.
Check the DSN:

$ odbcinst -q -s
...
[myhsqldb]

$ odbcinst -q -s -n myhsqldb
...
[myhsqldb]
Description=My Postgresql sample database
Driver=PostgreSQL Unicode
Database=sampledb

Try a hsqldb connection to the hsqldb database via ODBC using the native administrative tool isql:

isql -v myhsqldb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN');
Output:
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| country_name                            | country_id| country_id| street_address                          | city          |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| United States of America                | US        | US        | 2014 Jabberwocky Rd                     | Southlake     |
| United States of America                | US        | US        | 2011 Interiors Blvd                     | South San Francisco           |
| United States of America                | US        | US        | 2004 Charade Rd                         | Seattle       |
| United Kingdom                          | UK        | UK        | 8204 Arthur St                          | London        |
| United Kingdom                          | UK        | UK        | Magdalen Centre, The Oxford Science Park| Oxford        |
| China                                   | CN        |           |                                         |               |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
SQLRowCount returns 6
6 rows fetched

It looks good. Test now the DSN from a python script using the pyodbc module:

$ python3
import pyodbc 
cnxn = pyodbc.connect(DSN='myhsqldb')
cursor = cnxn.cursor()	
cursor.execute("""SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN')""")
row = cursor.fetchone() 
while row:
    print (row) 
    row = cursor.fetchone()
Output:
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('China', 'CN', None, None, None)

Everything is OK. hsqldb is now fully accessible from ODBC under the debian account.
Instructions for the other data sources can be accessed through the following links:
SQLite
Firebird
MariaDB
PostgreSQL
Oracle
Microsoft SQLServer for Linux
MongoDB
Excel

Cet article Installing the ODBC drivers for HSQLDB est apparu en premier sur Blog dbi services.

This article is part of a series that includes SQLite, Postgresql, Firebird, MongoDB, Microsoft SQL Server, HSQLDB, Excel, and MariaDB. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
As we already have a running Oracle remote instance, we don’t need to set one up and only the ODBC drivers need to be installed.
As user debian, get and install the ODBC drivers for Oracle (an account is needed), cf here.

$ id
uid=1000(debian) gid=1000(debian) groups=1000(debian)
$ cd ~/Downloads
$ wget https://download.oracle.com/otn_software/linux/instantclient/213000/instantclient-basiclite-linux.x64-21.3.0.0.0.zip
$ wget https://download.oracle.com/otn_software/linux/instantclient/213000/instantclient-sqlplus-linux.x64-21.3.0.0.0.zip
$ wget https://download.oracle.com/otn_software/linux/instantclient/213000/instantclient-odbc-linux.x64-21.3.0.0.0.zip
$ mkdir ~/odbc4gawk
$ export workdir=~/odbc4gawk
$ mkdir $workdir
$ unzip instantclient-basiclite-linux.x64-21.3.0.0.0.zip -d ${workdir}/.
$ unzip instantclient-sqlplus-linux.x64-21.3.0.0.0.zip -d ${workdir}/.
$ unzip instantclient-odbc-linux.x64-21.3.0.0.0.zip -d ${workdir}/.

The instant client software is required; we also download and install Oracle’s native command-line administration tool sqlplus to populate the test schema.
Follow the installation instructions here.
Add the Instant Client path to the shared library path:

$ vi ~/.bashrc
export LD_LIBRARY_PATH=/home/debian/odbc4gawk/instantclient_21_3:$LD_LIBRARY_PATH

Note: If WordPress does not render them correctly, there is an underscore between the name, 21 and 3 in instantclient_21_3 above.
As there is a bug in the Oracle script odbc_update_ini.sh, a work-around is provided here.

cd $workdir/instantclient_21_3
$ mkdir etc
$ cp /etc/odbcinst.ini etc/.
$ cp ~/.odbc.ini etc/odbc.ini

Run the configuration script now:

$ ./odbc_update_ini.sh .

Oracle has updated the local copy of the odbcinst.ini file. Let’s copy it to /etc to make the changes system-wide:

$ sudo cp etc/odbcinst.ini /etc/.

Check the ODBC driver file /etc/odbcinst.ini:

$ odbcinst -q -d
...
[Oracle 21 ODBC driver]

Correct.
See here for further configuration of the ODBC driver.
Let’s test the connection to the remote db via the instant client:

$ cd ${workdir}/instantclient_21_3
$ ./sqlplus scott/tiger@'(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))'

SQL*Plus: Release 21.0.0.0.0 - Production on Mon Oct 18 19:34:07 2021
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Sat Oct 16 2021 01:17:00 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 

The remote database is available and reachable natively.
Still in the sqlplus session, let’s populate the schema scott with the sample data. As the sample’s date values assume a different format, let’s switch to it in the Oracle session and avoid formatting errors:

SQL> alter session set nls_date_format = 'yyyy-mm-dd';

Let’s download the tables creation script using from here and the data populating script from here:

wget https://www.sqltutorial.org/wp-content/uploads/2020/04/oracle.txt --output-document=oracle.sql
wget https://www.sqltutorial.org/wp-content/uploads/2020/04/oracle-data.txt --output-document=oracle-data.sql

Create the tables and load the data now:

@oracle
@oracle-data

-- test the query:
set pagesize 10000
set linesize 200
set tab off
col country_name format a25
col STREET_ADDRESS format a30
col city format a20
SQL> SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN')';

COUNTRY_NAME              CO CO STREET_ADDRESS                 CITY
------------------------- -- -- ------------------------------ --------------------
United States of America  US US 2014 Jabberwocky Rd            Southlake
United States of America  US US 2011 Interiors Blvd            South San Francisco
United States of America  US US 2004 Charade Rd                Seattle
United Kingdom            UK UK 8204 Arthur St                 London
United Kingdom            UK UK Magdalen Centre, The Oxford Sc Oxford
                                ience Park

China                     CN

6 rows selected.
SQL> quit

The test data are ready.
Let’s edit debian’s ODBC DSN definitions and add the settings below:

$ vi ~/.odbc.ini
...
[OracleODBC-21]
...
Driver=Oracle 21 ODBC driver
DSN=OracleODBC-21
...
ServerName=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
...
UserID=scott
Password=tiger

Check the DSN:

$ odbcinst -q -s -n "OracleODBC-21" 
[OracleODBC-21]
AggregateSQLType=FLOAT
Application Attributes=T
Attributes=W
BatchAutocommitMode=IfAllSuccessful
...
ServerName=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
...
UserID=scott
Password=tiger

Test it using the ODBC Driver Manager test tool, isql:

$ isql -v OracleODBC-21
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN')
Output:
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| COUNTRY_NAME                            | COUNTRY_ID| COUNTRY_ID| STREET_ADDRESS                          | CITY          |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| United States of America                | US        | US        | 2014 Jabberwocky Rd                     | Southlake     |
| United States of America                | US        | US        | 2011 Interiors Blvd                     | South San Francisco           |
| United States of America                | US        | US        | 2004 Charade Rd                         | Seattle       |
| United Kingdom                          | UK        | UK        | 8204 Arthur St                          | London        |
| United Kingdom                          | UK        | UK        | Magdalen Centre, The Oxford Science Park| Oxford        |
| China                                   | CN        |           |                                         |               |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
SQLRowCount returns -1
6 rows fetched

The ODBC connection is OK. Test the DSN with the python ODBC module pyodbc:

$ python3
import pyodbc 
cnxn = pyodbc.connect('DSN=OracleODBC-21')
cursor = cnxn.cursor()	
cursor.execute("""SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN')""")
row = cursor.fetchone() 
while row:
    print (row) 
    row = cursor.fetchone()
Output:
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('China', 'CN', None, None, None)
>>> 

Oracle is now fully accessible via ODBC under the debian account.
Instructions for the other data sources can be accessed through the following links:
SQLite
Firebird
HSQLDB
MariaDB
PostgreSQL
MongoDB
Microsoft SQLServer for Linux
Excel

Cet article Installing the ODBC drivers for Oracle RDBMS est apparu en premier sur Blog dbi services.

This article is part of a series that includes SQLite, Postgresql, Firebird, Oracle RDBMS, Microsoft SQL Server, HSQLDB, MongoDB, and Excel. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
The MariaDB ODBC drivers can be installed from the platform’s default package repositories. As it is missing on this test environment, let’s install the MariaDB RDBMS (v10.5.11-1 ) too, and the ODBC drivers (v3.1.9-1) as root:

# apt install mariadb-server
# apt install odbc-mariadb

A systemd service was set up and launched:

# systemctl status mariadb
● mariadb.service - MariaDB 10.5.11 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2021-08-23 21:10:48 CEST; 5min ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
...

Check the ODBC driver file /etc/odbcinst.ini:

# odbcinst -q -d -n "MariaDB Unicode" 
[MariaDB Unicode]
Driver=libmaodbc.so
Description=MariaDB Connector/ODBC(Unicode)
Threading=0
UsageCount=1

Grant the necessary permissions to the test user debian using the provided native administration client, mysql:

# mysql
MariaDB [(none)]> GRANT CREATE, INSERT, SELECT, DELETE, UPDATE, DROP, ALTER ON *.* TO 'debian'@'localhost';
MariaDB [(none)]> exit;
Bye

As the user debian, create the sampledb database and its tables, and populate them using mysql again:

$ mysql --user=debian --password=debian
create database sampledb;
use sampledb;
-- create & populate the tables for mariadb by copying and pasting the statements from the above files;
-- test the data;
SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN');
Output:
+--------------------------+------------+------------+------------------------------------------+---------------------+
| country_name             | country_id | country_id | street_address                           | city                |
+--------------------------+------------+------------+------------------------------------------+---------------------+
| China                    | CN         | NULL       | NULL                                     | NULL                |
| United Kingdom           | UK         | UK         | 8204 Arthur St                           | London              |
| United Kingdom           | UK         | UK         | Magdalen Centre, The Oxford Science Park | Oxford              |
| United States of America | US         | US         | 2014 Jabberwocky Rd                      | Southlake           |
| United States of America | US         | US         | 2011 Interiors Blvd                      | South San Francisco |
| United States of America | US         | US         | 2004 Charade Rd                          | Seattle             |
+--------------------------+------------+------------+------------------------------------------+---------------------+
6 rows in set (0.001 sec)
\q

The data are OK. Configure ODBC by editing the user’s DSN file:

$ vi ~/.odbc.ini
[mysqldb]
Description=My mysql sample database
Driver=MariaDB Unicode
Database=sampledb

Check the DSN definition:

$ odbcinst -q -s -n mysqldb
[mysqldb]
Description=My mysql sample database
Driver=MariaDB Unicode
Database=sampledb
Socket=/var/run/mysqld/mysqld.sock

See below for an explanation about the highlighted “Socket=…” line.
Test the DSN via isql:

$ isql mysqldb -v debian debian
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN');
+---------------------------+-----------+-----------+-----------------------------------------+---------------------+
| country_name              | country_id| country_id| street_address                          | city                |
+---------------------------+-----------+-----------+-----------------------------------------+---------------------+
| China                     | CN        |           |                                         |                     |
| United Kingdom            | UK        | UK        | 8204 Arthur St                          | London              |
| United Kingdom            | UK        | UK        | Magdalen Centre, The Oxford Science Park| Oxford              |
| United States of America  | US        | US        | 2014 Jabberwocky Rd                     | Southlake           |
| United States of America  | US        | US        | 2011 Interiors Blvd                     | South San Francisco |
| United States of America  | US        | US        | 2004 Charade Rd                         | Seattle             |
+---------------------------+-----------+-----------+-----------------------------------------+---------------------+
SQLRowCount returns 6
6 rows fetched

The ODBC connection works fine. Test the DSN from a python script using the pyodbc module:

$ python3
Python 3.9.2 (default, Feb 28 2021, 17:03:44) 
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
import pyodbc 
cnxn = pyodbc.connect(DSN='mysqldb;user=debian;password=debian')
cursor = cnxn.cursor()      
cursor.execute("""SELECT
...         c.country_name,
...         c.country_id,
...         l.country_id,
...         l.street_address,
...         l.city
... FROM
...         countries c
... LEFT JOIN locations l ON l.country_id = c.country_id
... WHERE
...         c.country_id IN ('US', 'UK', 'CN')""")

row = cursor.fetchone() 
 while row:
...     print (row) 
...     row = cursor.fetchone()
... 
Output:
('China', 'CN', None, None, None)
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')

Everything is just fine.
Note: When the MariaDB database is restarted, an error message such as the one below from isql is displayed when connecting to a database as a non-root user:

$ isql -v mysqldb debian debian
[S1000][unixODBC][ma-3.1.7]Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[ISQL]ERROR: Could not SQLConnect

or from pyodbc:

pyodbc.Error: ('HY000', "[HY000] [ma-3.1.7]Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) (2002) (SQLDriverConnect)")

If this happens, just make sure the line Socket=/var/run/mysqld/mysqld.sock is present in ~/.odbc.ini. Alternatively, but not as good because as it is needed each time the database is restarted, create the symlink below as root:

# ln -s /run/mysqld/mysqld.sock /tmp/mysql.sock

MariaDB is now fully accessible from any ODBC application under the debian account.
Instructions for the other data sources can be accessed through the following links:
SQLite
Firebird
HSQLDB
PostgreSQL
Oracle
Microsoft SQLServer for Linux
MongoDB

Cet article Installing the ODBC drivers for MariaDB est apparu en premier sur Blog dbi services.

This article is part of a series that includes SQLite, Postgresql, Microsoft SQLServer, Oracle RDBMS, HSQLDB, MariaDB, MongoDB, and Excel. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
As root, install the Firebird RDBMS and its ODBC drivers from the official repositories:

# apt install firebird3.0-server

A systemd service was set up and launched:

# systemctl status firebird3.0.service
● firebird3.0.service - Firebird Database Server ( SuperServer )
     Loaded: loaded (/lib/systemd/system/firebird3.0.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2021-10-18 15:00:25 CEST; 50min ago
    Process: 546 ExecStart=/usr/sbin/fbguard -daemon -forever (code=exited, status=0/SUCCESS)
   Main PID: 576 (fbguard)
      Tasks: 4 (limit: 4659)
     Memory: 10.1M
        CPU: 75ms
     CGroup: /system.slice/firebird3.0.service
             ├─576 /usr/sbin/fbguard -daemon -forever
             └─577 /usr/sbin/firebird

Oct 18 15:00:23 debian systemd[1]: Starting Firebird Database Server ( SuperServer )...
Oct 18 15:00:25 debian systemd[1]: Started Firebird Database Server ( SuperServer ).

The service runs as the newly created firebird account:

# ps -ef | grep firebird
firebird   28053       1  0 15:15 ?        00:00:00 /usr/sbin/fbguard -daemon -forever
firebird   28054   28053  0 15:15 ?        00:00:00 /usr/sbin/firebird

Create the symlink below:

# ln -s /usr/lib/x86_64-linux-gnu/libfbclient.so.3.0.7 /usr/lib/x86_64-linux-gnu/libgds.so

Get and install the binaries ODBC drivers for Firebird:

# wget https://sourceforge.net/projects/firebird/files/firebird-ODBC-driver/2.0.5-Release/OdbcFb-LIB-2.0.5.156.amd64.gz/download
# gunzip OdbcFb-LIB-2.0.5.156.amd64.gz
# tar xvf OdbcFb-LIB-2.0.5.156.amd64
# cp libOdbcFb.so /usr/lib/x86_64-linux-gnu/odbc/.

Edit the odbcinst.ini file:

# vi /etc/odbcinst.ini
...
[Firebird]
Description=InterBase/Firebird ODBC
Driver=/usr/lib/x86_64-linux-gnu/odbc/libOdbcFb.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libOdbcFb.so
Threading=1
FileUsage=1
CPTimeout=
CPReuse=

Check the ODBC driver file /etc/odbcinst.ini:

# odbcinst -q -d
...
[Firebird]

# odbcinst -q -d -n Firebird
[Firebird]
Description=InterBase/Firebird ODBC
Driver=/usr/lib/x86_64-linux-gnu/odbc/libOdbcFb.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libOdbcFb.so
Threading=1
FileUsage=1
CPTimeout=
CPReuse=

As debian, create the sampledb database using the native administrative tool isql-fb:

$ cd $workdir
$ mkdir firebird
$ cd firebird
$ isql-fb -user SYSDBA -password 'SYSDBA'

Use CONNECT or CREATE DATABASE to specify a database

SQL> create database "/home/debian/odbc4gawk/firebird/sampledb.fb" page_size 8192 user SYSDBA password 'SYSDBA';
SQL> commit;
SQL> quit;

As there are no sample scripts specifically for Firebird, use the postgresql’s ones to create the tables but first replace the “SERIAL” keyword in the “CREATE TABLE” statements with the equivalent firebird’s syntax “INT GENERATED BY DEFAULT AS IDENTITY”. Use your favorite text editor or even sed for this. Save the above file as create_tables_postgresl.sql. With the vi editor, this could be done as shown below:

$ vi create_tables_postgresl.sql
:1,$s/ SERIAL / INT GENERATED BY DEFAULT AS IDENTITY /g
:w create_tables_firebird.sql
:q

Launch again isql-fb with a connection to the newly created database and copy/paste the statements from create_tables_firebird.sql as they are just a few of them:

$ isql-fb /home/debian/odbc4gawk/firebird/sampledb.fb -u sysdba -p sysdba
Database: /home/debian/odbc4gawk/firebird/sampledb.fb, User: SYSDBA
SQL> show tables;

There are no tables in this database
Paste the create table statements here.
Also, copy/paste the statements from here and append a final commit; to confirm the INSERT. Finally, check the data:

SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN');
Output:
COUNTRY_NAME                             COUNTRY_ID COUNTRY_ID STREET_ADDRESS                           CITY                           
======================================== ========== ========== ======================================== =====================
China                                    CN                                                                          
United Kingdom                           UK         UK         8204 Arthur St                           London                         
United Kingdom                           UK         UK         Magdalen Centre, The Oxford Science Park Oxford                         
United States of America                 US         US         2014 Jabberwocky Rd                      Southlake                      
United States of America                 US         US         2011 Interiors Blvd                      South San Francisco            
United States of America                 US         US         2004 Charade Rd                          Seattle                        
SQL> 

Configure an ODBC DSN by editing the user’s DSN file:

$ vi ~/.odbc.ini
...
[myfbdb]
Description=Firebird
Driver=Firebird
Dbname=/home/debian/odbc4gawk/firebird/sampledb.fb
User=SYSDBA
Password=SYSDBA
Role=
CharacterSet=UTF8
ReadOnly=No
NoWait=No

Check it:

$ odbcinst -q -s -n 
...
[myfbdb]
...

$ odbcinst -q -s -n myfbdb
[myfbdb]
Description=Firebird
Driver=Firebird
Dbname=/home/debian/odbc4gawk/firebird/sampledb.fb
User=SYSDBA
Password=SYSDBA
Role=
CharacterSet=UTF8
ReadOnly=No
NoWait=No

Try a connection to the firebird db via ODBC:

$ isql -v myfbdb sysdba sysdba
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN');
Output:
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| country_name                            | country_id| country_id| street_address                          | city          |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| China                                   | CN        |           |                                         |               |
| United Kingdom                          | UK        | UK        | 8204 Arthur St                          | London        |
| United Kingdom                          | UK        | UK        | Magdalen Centre, The Oxford Science Park| Oxford        |
| United States of America                | US        | US        | 2014 Jabberwocky Rd                     | Southlake     |
| United States of America                | US        | US        | 2011 Interiors Blvd                     | South San Francisco           |
| United States of America                | US        | US        | 2004 Charade Rd                         | Seattle       |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
SQLRowCount returns 6
6 rows fetched

Test the DSN with pyodbc:

$ python3
Python 3.9.2 (default, Feb 28 2021, 17:03:44) 
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc 
>>> con = pyodbc.connect('DSN=myfbdb;UID=SYSDBA;PWD=SYSDBA;DBNAME=sampledb;')
Segmentation fault

Although isql has no problem with them, it looks like the official Firebird ODBC drivers don’t work well with pyodbc. The same behavior was noticed later with the gawk the extension (see article here …). For this reason, we tried the commercial drivers from Devart available here. After having installed them by following the clear and simple instructions and having created the DSN myfbdb_devart using those drivers, pyodbc could work fine.
After installation of those drivers, check the DSN definition:

$ odbcinst -q -s -n myfbdb_devart
[myfbdb_devart]
Description=Firebird
Driver=Devart ODBC Driver for Firebird
Database=/media/sf_customers/dbi/odbc4gawk/sampledb.fb
Port=3050
User=SYSDBA
Password=SYSDBA
Role=
CharacterSet=UTF8
ReadOnly=No
NoWait=No

And the drivers:

$ odbcinst -q -d -n "Devart ODBC Driver for Firebird"
[Devart ODBC Driver for Firebird]
Driver=/usr/local/lib/libdevartodbcfirebird.so

Retry the pyodbc module:

$ python3
Python 3.9.2 (default, Feb 28 2021, 17:03:44) 
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
import pyodbc 
cnxn = pyodbc.connect('DSN=myfbdb_devart;UID=SYSDBA;PWD=SYSDBA;DBNAME=sampledb;')
cursor = cnxn.cursor()      
cursor.execute("""SELECT
...         c.country_name,
...         c.country_id,
...         l.country_id,
...         l.street_address,
...         l.city
... FROM
...         countries c
... LEFT JOIN locations l ON l.country_id = c.country_id
... WHERE
...         c.country_id IN ('US', 'UK', 'CN')""")

row = cursor.fetchone() 
 while row:
...     print (row) 
...     row = cursor.fetchone()
... 
Output:
('China', 'CN', None, None, None)
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')

The Devart’s drivers work fine. However, let’s try to recompile the official ODBC drivers for Firebird from their source code available here. To compile them, follow the following steps:

$ apt install unixodbc-dev
$ apt install firebird-dev

Once downloaded and untarred, move the directory OdbcJdbc/Builds/Gcc.lin and compile the drivers:

$ export FBINCDIR=/usr/include/firebird
$ export FBLIBDIR=/usr/lib/x86_64-linux-gnu
$ make -e -d -f makefile.linux

The compiled libraries are put in OdbcJdbc/Builds/Gcc.lin/Release_x86_64. As root, install the shared library libOdbcFb.so in its usual sub-directory and make it readable for everyone:

# cp OdbcJdbc/Builds/Gcc.lin/Release_x86_64/libOdbcFb.so /usr/lib/x86_64-linux-gnu/odbc/.

# chmod a+r /usr/lib/x86_64-linux-gnu/odbc/libOdbcFb.so

With those recompiled drivers, isql still works and pyodbc still fails but only while executing the SELECT statement. As shown later, the gawk extension has no problem anymore: it works with the drivers from Devart as well as the recompiled drivers; thus, we still have the option of free drivers.
Despite pyodbc, Firebird is now fully accessible from any ODBC application under the debian account.
Instructions for the other data sources can be accessed through the following links:
SQLite
HSQLDB
MariaDB
PostgreSQL
Oracle
Microsoft SQLServer for Linux
MongoDB
Excel

Cet article Installing the ODBC drivers for Firebird est apparu en premier sur Blog dbi services.

This article is part of a series that includes SQLite, MariaDB, Firebird, Oracle RDBMS, Microsoft SQL Server, HSQLDB, MongoDB, and Excel. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
The postgresql drivers can be installed from the platform’s default package repositories using the following steps as root. As the postgresql service was missing on this test environment, let’s install it too:

# apt install postgresql

A systemd service has been created and started, check it:

# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Mon 2021-08-23 19:04:20 CEST; 1min 15s ago
   Main PID: 10528 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4659)
     Memory: 0B
        CPU: 0
     CGroup: /system.slice/postgresql.service

Check its processes:

# ps -ef | grep postgres
postgres     682       1  0 Oct16 ?        00:00:02 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
postgres     807     682  0 Oct16 ?        00:00:00 postgres: 13/main: checkpointer 
postgres     808     682  0 Oct16 ?        00:00:04 postgres: 13/main: background writer 
postgres     809     682  0 Oct16 ?        00:00:04 postgres: 13/main: walwriter 
postgres     810     682  0 Oct16 ?        00:00:01 postgres: 13/main: autovacuum launcher 
postgres     811     682  0 Oct16 ?        00:00:01 postgres: 13/main: stats collector 
postgres     812     682  0 Oct16 ?        00:00:00 postgres: 13/main: logical replication launcher

The processes run under the postgres account.
Install the ODBC drivers:

# apt install odbc-postgresql

Check this step:

# odbcinst -q -d
...
[PostgreSQL ANSI]
[PostgreSQL Unicode]

Two drivers have been installed, one for the ANSI character encoding and one for Unicode; check the Unicode one:

# odbcinst -q -d -n 'PostgreSQL Unicode'
[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

Verify that the default native administration tool, psql, is there:

# psql -V
# psql (PostgreSQL) 13.3 (Debian 13.3-1)

Switch to the postgres account and create the database:

# su – postgres
$ createdb sampledb

Launch psql and create the tables for postgresql using the scripts here:
As those are small files, copying and pasting their content into psql will do just fine.
Also, populate the tables for postgresql using the data here and test:

$ psql sampledb
sampledb=# SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN');
Output:
       country_name       | country_id | country_id |              street_address   |    city         
--------------------------+------------+------------+-------------------------------+-----------
 United States of America | US         | US         | 2014 Jabberwocky Rd           | Southlake
 United States of America | US         | US         | 2011 Interiors Blvd           | South San Francisco
 United States of America | US         | US         | 2004 Charade Rd               | Seattle
 United Kingdom           | UK         | UK         | 8204 Arthur St                | London
 United Kingdom           | UK         | UK         | Magdalen Centre, The Oxford   | Oxford
                          |            |            | Science Park                  | 
 China                    | CN         |            |                               | 
(6 rows)

To test ODBC, first edit ~/.odbc.ini and add the postgresql database details:

$ vi ~/.odbc.ini 
[mypostgresqldb]
Description=My Postgresql sample database
Driver=PostgreSQL Unicode
Database=sampledb

Verify that the edition was successful:

$ odbcinst -q -s -n mypostgresqldb
[mypostgresqldb]
Description=My Postgresql sample database
Driver=PostgreSQL Unicode
Database=sampledb

Next, launch isql against that database:

$ export LD_LIBRARY_PATH=/usr/lib/x86_64-linux-gnu/odbc:$LD_LIBRARY_PATH
$ isql -v mypostgresqldb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> help
+------------------+-------------+-------------+------------+---------+
| TABLE_QUALIFIER  | TABLE_OWNER | TABLE_NAME  | TABLE_TYPE | REMARKS |
+------------------+-------------+-------------+------------+---------+
| sampledb         | public      | countries   | TABLE      |         |
| sampledb         | public      | departments | TABLE      |         |
| sampledb         | public      | dependents  | TABLE      |         |
| sampledb         | public      | employees   | TABLE      |         |
| sampledb         | public      | jobs        | TABLE      |         |
| sampledb         | public      | locations   | TABLE      |         |
| sampledb         | public      | regions     | TABLE      |         |
+------------------+-------------+-------------+------------+---------+
SQLRowCount returns 7
7 rows fetched
SQL> quit

The ODBC connectivity to postgresql is confirmed for the postgres account; this is fine for that user but we want the database to be usable by the debian test account too. To this effect, use the following steps from with psql:

sampledb=# CREATE ROLE debian superuser;
CREATE ROLE
sampledb=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO debian;
GRANT
sampledb=# ALTER ROLE debian with login;
ALTER ROLE
sampledb=# \q

Back as root, become debian and test the accessibility of the sampledb:

# su - debian
$ psql sampledb
sampledb=# select count(*) from employees;
 count 
-------
    40
(1 row)

Check with isql:

$ isql -v mypostgresqldb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN')
+---------------------------+-------------+-----------+-----------------------+----------------------+
| country_name              | country_id  | country_id| street_address        | city                 |
+---------------------------+-------------+-----------+-----------------------+----------------------+
| United States of America  | US          | US        | 2014 Jabberwocky Rd   | Southlake            |
| United States of America  | US          | US        | 2011 Interiors Blvd   | South San Francisco  |
| United States of America  | US          | US        | 2004 Charade Rd       | Seattle              |
| United Kingdom            | UK          | UK        | 8204 Arthur St        | London               |
| China                     | CN          |           |                       |                      |
+---------------------------+-------------+-----------+-----------------------+----------------------+ 
SQLRowCount returns 6
6 rows fetched

Note that the order of the result set may differ in other data sources; the SQL standard does not define the result set’s order and the ORDER BY clause should be used to enforce one if needed.
Finally, let’s verify the ODBC connectivity through pyodbc:

$ python3
import pyodbc Python 3.9.2 (default, Feb 28 2021, 17:03:44) 
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.

import pyodbc

# connect directly using the DRIVER definition, no DSN;
cnxn = pyodbc.connect('DRIVER={PostgreSQL Unicode};Direct=True;Database=sampledb;String Types= Unicode')

# using the DSN is OK too:
# cnxn = pyodbc.connect('DSN=mypostgresqldb')
cursor = cnxn.cursor()      
cursor.execute("""SELECT
...         c.country_name,
...         c.country_id,
...         l.country_id,
...         l.street_address,
...         l.city
... FROM
...         countries c
... LEFT JOIN locations l ON l.country_id = c.country_id
... WHERE
...         c.country_id IN ('US', 'UK', 'CN')""") 

row = cursor.fetchone() 
while row:
...     print (row) 
...     row = cursor.fetchone()
... 
Output:
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('China', 'CN', None, None, None)

Note that we used the PostgreSQL Unicode driver, not the ANSI one because the latter gives the error below:

pyodbc.Error: ('07006', '[07006] Received an unsupported type from Postgres. (14) (SQLGetData)')

postgreslq databases can now be used locally by any ODBC client application, e.g. any python program with the pyodbc module, or a desktop application such as LibreOffice. In the case of python, many native modules for postgresql are available but they require ad hoc function calls whereas ODBC lets one use the same statements with any database target, which simplifies the maintenance of ODBC applications.
Instructions for the other data sources can be accessed through the following links:
SQLite
HSQLDB
MariaDB
Firebird
Oracle
Microsoft SQLServer for Linux
MongoDB
Excel

Cet article Installing the ODBC drivers for PostgreSQL est apparu en premier sur Blog dbi services.

This article is part of a series that includes Firebird, Postgresql, Microsoft SQLServer, Oracle RDBMS, HSQLDB, MariaDB, MongoDB, and Excel. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Excepting the present part which deals with the ODBC Driver manager’s installation which is a prerequisite, each part can be used independently from the others.
The test system is a debian v11 (bullseye).

Installation of the ODBC driver Manager

There are 2 main implementations of ODBC for Linux: UnixODBC (http://www.unixodbc.org/) and iODBC (http://www.iodbc.org); we picked the former for no particular reason.
The driver manager can be installed as root through the standard package management tool in Debian:

root@debian:~# apt-get install libodbc1
root@debian:~# apt install unixodbc 

root@debian:~# odbcinst -j
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

The system-wide file /etc/odbcinst.ini stores the list of installed ODBC drivers and is maintained by root. It is the default one, but its location can be changed and set in the environment variable $ODBCINST.
Let’s create a working folder in user debian’s home directory, our test account:

$ id
uid=1000(debian) gid=1000(debian) groups=1000(debian)
$ mkdir ~/odbc4gawk
$ export workdir=~/odbc4gawk
$ mkdir $workdir
$ cd $workdir

As most of the ODBC drivers have their shared libraries installed in /usr/lib/x86_64-linux-gnu/odbc, this path must be added the $LD_LIBRARY_PATH environment variable of any user that must use ODBC, preferably in the ~/.bashrc file (if bash is the select shell):

export LD_LIBRARY_PATH=/usr/lib/x86_64-linux-gnu/odbc:$LD_LIBRARY_PATH

We are now ready to install the ODBC drivers for each data source of interest.

Installation of the SQLite ODBC driver

SQLite along with its administrative tool isql are installed by the native package manager as root:

# apt install sqlite3
# apt install libsqliteodbc

Each time an ODBC driver is installed, it updates the file /etc/odbcinst.ini with driver-dependent information, which can later be checked using the above command odbcinst, e.g.:
List the installed ODBC drivers:

$ odbcinst -q -d
[SQLite3]

Query a particular driver:

$ odbcinst -q -d -n SQLite
SQLite3]
Description=SQLite3 ODBC Driver
Driver=libsqlite3odbc.so
Setup=libsqlite3odbc.so
UsageCount=1

As user debian, edit the file ~/.odbc.ini and add the SQLite data source’s details we will use:

$ cd $workdir 
$ mkdir sqlite
$ cd sqlite
$ vi ~/.odbc.ini
[mysqlitedb]
Description=My SQLite sample database
Driver=SQLite3
Database=/home/debian/odbc4gawk/sqlite/sampledb

Let’s check it:
List all the DSN currently defined in ~/.odbc.ini

$ odbcinst -q -s
[mysqlitedb]

List our new DSN:

$ odbcinst -q -s -n mysqlitedb
[mysqlitedb]
Description=My SQLite sample database
Driver=SQLite3
Database=/home/debian/odbc4gawk/sqlite/sampledb

which is the information we just entered above.

The ~/.odbc.ini file allows to hide a drivers’ private settings so that they don’t need to be specified later. When connecting to a data source, the name that is specified here between square brackets, the DSN, is enough. It is also possible to directly specify in-line all the parameters in this section when connecting programmatically but the code will need to be edited (and maybe recompiled) in case one them has to be changed, so using a DSN is preferable.
The default file location is the current user’s home directory but it can be changed and its full path name set in the environment variable $ODBCINI.

Let’s test the connection via ODBC using the command isql included with the ODBC driver manager:

$ isql mysqlitedb -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

The ODBC driver for SQLite does work.

To populate a database, start sqllite3:

$ /usr/bin/sqlite3
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
Connected to a transient in-memory database.

Use “.open FILENAME” to reopen a persistent database.

sqlite> .open sampledb

The file sampledb has been created in the directory specified in ~/.odbc.ini.
Go to the following links, copy the SQL statements and paste them in sqllite3: table creation and insert data.

Still in sqlite3, check that the database has been populated by running the test SQL query listed above:

sqlite> SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN');
Output:
China|CN|||
United Kingdom|UK|UK|8204 Arthur St|London
United Kingdom|UK|UK|Magdalen Centre, The Oxford Science Park|Oxford
United States of America|US|US|2014 Jabberwocky Rd|Southlake
United States of America|US|US|2011 Interiors Blvd|South San Francisco
United States of America|US|US|2004 Charade Rd|Seattle

Now, quit sqlite3 and re-run the same query from isql, the ODBC-based utility:

$ isql -v mysqlitedb
SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN')
+---------------------------+-------------+-----------+---------------------------------------------+----------------------+
| country_name              | country_id  | country_id| street_address                              | city                 |
+---------------------------+-------------+-----------+---------------------------------------------+----------------------+
| China                     | CN          |           |                                             |                      |
| United Kingdom            | UK          | UK        | 8204 Arthur St                              | London               |
| United Kingdom            | UK          | UK        | Magdalen Centre, The Oxford Science Park    | Oxford               |
| United States of America  | US          | US        | 2014 Jabberwocky Rd                         | Southlake            |
| United States of America  | US          | US        | 2011 Interiors Blvd                         | South San Francisco  |
| United States of America  | US          | US        | 2004 Charade Rd                             | Seattle              |
+---------------------------+-------------+-----------+---------------------------------------------+----------------------+
SQLRowCount returns 0
6 rows fetched

Note: the output has been shrunk a bit to reduce horizontal scrolling.

Everything looks good so far. Let’s now install the pyodbc ODBC module for python as root;

# apt install pip
# needed to compile pyodbc:
# apt install unixodbc-dev
# pip install pyodbc

As debian, execute the following python script with the following connection string:

import pyodbc 
cnxn = pyodbc.connect(DSN='mysqlitedb;UID=SA;PWD=admin2021!')
cursor = cnxn.cursor()  
cursor.execute("""SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN')""")
row = cursor.fetchone() 
while row:
    print (row) 
    row = cursor.fetchone()
Output:
('China', 'CN', None, None, None)
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')Output:

Everything works as expected.
We have completed the steps to access an SQLite database via ODBC from isql and from the python module pyodbc. It was quite straightforward with that RDBMS. Once the gawk interface is completed, it should return the same output.
Turn now to the links below for the other data sources:
Firebird
HSQLDB
MariaDB
PostgreSQL
Oracle
Microsoft SQLServer for Linux
MongoDB
Excel

Cet article Installing the ODBC Driver Manager with SQLite on Linux est apparu en premier sur Blog dbi services.

I was listening to a podcast recently about “Thinking Clearly” when the presenter gave a very rapid description of a common mistake that occurs when human intuition meets statistical information.

The particular bit of statistical analysis, when you meet its effects in real-life, can be hard to understand so I thought I’d draw a picture that might help people to understand the mechanism of putting a “threat percentage” into the correct context.

Imagine there is a “nasty medical condition X” that affects one in 20 of all men over the age of 55. Now imagine that there is a diagnostic test for the condition that is 90% accurate (meaning it will return the correct result 90 times out of a hundred and the wrong result 10 times out of 100).

You are male, just past your 55th birthday, and your doctor tells you that you’ve tested positive. How terrified should you be or, to put it another way, which is more likely (and by how much): you’ve got X, or you haven’t got X?

The human, intuitive, response is simple: you’ve been told the test is 90% accurate and you’ve tested positive; so your digestive system is probably telling you that it’s almost certain you’ve got X.

The statistician’s approach (after the initial reflexive shock, perhaps) is to apply Bayesian thinking which can be turned into pictures as follows:

• Step 1: What does “one in 20” look like? (left hand square)

• Step 2: What does “90% accurate” look like? (right hand square)

• Step 3: What does the picture look like when we superimpose the individual (independent) probabilities:

The big white block toward the top left tells us about the group of people who are correctly informed that they are X-free; the tiny red block in the bottom right tells us about the really unlucky ones who are told they’re X-free when they actually have X (false negatives).

Now compare the two pink blocks: the vertical rectangle to the right is the group that have X and test positive; the horizontal rectangle at the bottom is the group who don’t have X but test positive anyway (false positives).

The visual impression from this image is that if you’ve been told that you tested positive it’s nearly twice as likely that you are X-free than you are to have X: but let’s put the numbers into the picture to get a better handle on this. I’ll use a population of 10,000 (which, conveniently, I can represent as a square measuring 100 by 100):

In a population of 10,000

• X-free = 95 * (90 + 10) = 9,500 (95%)
• Got X = 5 * (90 + 10) = 500 (5%)

• Correct result given = 90 * (95 + 5) = 9,000 (90%)
• Wrong result given =10 * (95 + 5) = 1,000 (10%)

• X-free and received right result = 8,550 … (95 * 90, top left)
• Got X and received wrong result = 50 … (5 * 10, bottom right)

• Got X and received right result = 450 … (5 * 90, top right)
• X-free and received wrong result = 950 … (95 * 10, bottom left)

Given the underlying population data (“priors”) for this example, we see that a positive result from a test that’s 90% accurate means there’s a probability of 450 / (950 + 450) = 0.32 (32%) that you’ve got X.

Footnote

The result of this very simple hypothetical case is not intuitively obvious to most people; but if you thought it was easy to get to the right answer you might want to look at the Monty Hall problem, which also leads to Bertrand’s Boxes and the Three Prisoners problem.-

hello tom i wanted to know what is the perfect scenario to implement a IOT(index organized table) on a database design from what i know so far we need to create an IOT when the table is small(both row and column) and almost all of the column in the table are indexed.is there another scenario where IOT can boost the database performance? also i wanted to know why logical rowid is slower than physical rowid can you explain it pls? thanks

I have a SQL query as: <code> With jtable as (:inp) Select * from table t,jtable where t.DATE = jtable.DATE</code> Here <code>inp </code>is a dataframe which I take care of by converting to json and eveything and it works perfectly fine except when it has to deal with datetime object. So if I have dataframe with datetime object and I push it to json I get it in posix and hence the date in oracle which is still datetime wont be equal to it. I tried changing it to make string TO_DATE(%y-%m-%d,YYYY-MM-DD) when I see datetime in dataframe but its non numeric and hence binding doesnot work. Is there any way to bypass it so that I dont have to change my sql queries (sql query can be changed to TO_DATE and I can pass datettime simply as string) <code>jason-data == [{"TEST_DATE"16163612361}]</code> when the dataframe has TEST_DATE = pd.timestamp(2019,6,28) or something. Is there any way to tackle it?

Some debug-events and debug-actions are not safe and should be exposed to users with caution. In previous releases, privilege control for the usage of these diagnostics was not sufficient.

With Oracle 21c, regular users can be blocked from using these diagnostics to better support separation of duty.

Indeed with Oracle 21c, we have a new mechanism to control the debug-events and debug-actions through ALTER SESSION and/or ALTER SYSTEM. This mechanism is implemented by two new features
-ENABLE DIAGNOSTICS system privilege
-DIAGNOSTICS_CONTROL initialization parameter

Let’s see how these features work
As the DIAGNOSTICS_CONTROL is set to IGNORE, The default behavior is that every user can perform diagnostic tasks without error if he has ALTER SESSION PRIVILEGE

SQL> show parameter diagnostics_control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostics_control                  string      IGNORE
SQL>

Let’s consider the user EDGE with the following privileges

SQL>  select privilege from dba_sys_privs where GRANTEE='EDGE';

PRIVILEGE
----------------------------------------
CREATE TABLE
ALTER SESSION
CREATE SESSION

SQL>

The user EDGE can execute following query

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> show user
USER is "EDGE"
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

Session altered.

SQL>

Let’s now set the DIAGNOSTICS_CONTROL to WARNING

SQL> alter system set diagnostics_control=WARNING;

System altered.

SQL> show parameter diagnostics_control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostics_control                  string      WARNING
SQL>

And let’s retry the SET EVENTS action with the user EDGE

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> show user
USER is "EDGE"
SQL>


SQL> ALTER SESSION SET EVENTS '1357 trace name context forever, level 2';

Session altered.

SQL>

The action is executed but we have an entry in database alert log

2022-01-12T12:56:22.136454+01:00
PDB1(3):User 'EDGE' has set the following debug-event(s) on the event-group 'session':
PDB1(3):'1357 trace name context forever, level 2'
PDB1(3):To disable such messages, refer to documentation about parameter 'diagnostics_control'.

Now let’s put the DIAGNOSTICS_CONTROL to ERROR

SQL> alter system set diagnostics_control=ERROR;

System altered.

SQL> show parameter diagnostics_control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostics_control                  string      ERROR
SQL>

And let’s try again the SET EVENTS action with the user EDGE

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> show user
USER is "EDGE"
SQL> ALTER SESSION SET EVENTS '1357 trace name context forever, level 2';
ERROR:
ORA-01031: insufficient privileges
ORA-49100: Failed to process event statement [1357 trace name context forever,
level 2]

SQL>

We can see that the statement is not executed and that we get an error.

We can conclude than using the parameter DIAGNOSTICS_CONTROL, we can control which users can perform diag events. One can see following in Oracle documentation

ERROR: If a user who does not have the SYSDBA or ENABLE DIAGNOSTICS privilege attempts to enable a diagnostic, then the attempt will fail and an ORA-01031: insufficient privileges error appears.
WARNING: A user who does not have the SYSDBA or ENABLE DIAGNOSTICS privilege will be able to enable a diagnostic, but a warning message is written to an alert log. The warning message is similar to the following:
IGNORE: The user can perform the diagnostic task without any error messages appearing. This setting is the default.

But how if I want to set the parameter to ERROR and want to allow some non-DBA users to perform diag events? Just grant them the new system privilege ENABLE DIAGNOSTICS.
Let do a test.

SQL>  select privilege from dba_sys_privs where GRANTEE='EDGE';

PRIVILEGE
----------------------------------------
ENABLE DIAGNOSTICS
CREATE TABLE
ALTER SESSION
CREATE SESSION

SQL> show parameter diagnostics_control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostics_control                  string      ERROR
SQL>

And now let’s do a diag event

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> show user
USER is "EDGE"

SQL> ALTER SESSION SET EVENTS '1357 trace name context forever, level 2';

Session altered.

SQL>

As now EDGE has the privilege ENABLE DIAGNOSTICS, he can perform diag events even if the DIAGNOSTICS_CONTROL is set to ERROR.

Conclusion

This new feature can be very useful to limit the users who can perform diag actions. More information can be found in the Oracle documentation

Cet article Oracle 21C Security : diagnostics_control and enable diagnostics est apparu en premier sur Blog dbi services.