You are currently reviewing an older revision of this page.
Logging
The extended drawing attribute (DBQUERY) logging capability has been improved in order to help debug DBQUERY related issues.
By adding DEBUG = 1 in textnodes.txt the logging will additionally list:
When a select statement result in more than one result column a list of column names will be produced. This is great for debugging EXCEL type queries. It is best used in combination with DEBUG = 1. In order to list all column names one would use a SQL statement like:
Select * from <table name>
Drivers
OPIM needs 32 bit ODBC drivers. In case DBQUERYs are use on Microsoft products like Access or Excel one must check if the Windows OS contains recent 32 bit ODBC drivers
Start this application:
C:\Windows\SysWOW64\odbcad32.exe
Go to the drivers tab and check if the following driver is present:
If not it is advised to install a more recent driver set, to be found here
http://www.microsoft.com/en-us/download/details.aspx?id=13255
When installing it is referred to as Microsoft Access database engine 2010. It does however also installs Excel drivers.
Using DBQUERY on Excel spreadsheets
The general format of a DBQUERY statement is:
<DA_NAME> = DBQUERY ( <ODBC specification>, <SQL statement>)
With:
DA_NAME The name of the drawing attribute that will hold the result
ODBC Specification A reference to an ODBC connection (connection string)
SQL statement The statement to retrieve data
Excel ODBC connection string examples
Connection string for excel spreadsheet linelist.xls located in c:\tmp\excel
DB_1 = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=c:\tmp\excel\linelist.xls
Connection string for excel spreadsheet linelist.xls located on a server
DB_2 = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=\\Pluif7010reml\excel\linelist.xls
→ Never locate the spreadsheet in the drive or server root. Don’t use for instance
DBQ=c:\linelist.xlsDBQ=\\Pluif7010reml\linelist.xls
→ Always use a variant of this driver: Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
ODBC driver interpretation of the Excel spreadsheet
Work area
The ODBC driver limits the work area to an area with non-empty cells. For the spreadsheet shown below the work area is D4:F7.
→This is not an absolute truth. Depending on previous editing actions in Excel the work area may be larger than expected
Column names
The first row of the work area specifies the column names. When a cell in the first row is empty a column name is assigned in the format of F<column index>. When the first row partly consists of merged cells the ODBC driver uses an alternative view. In case one wants to be sure what the actual column names are it is suggested to use a “Select *” on the sheet (see logging remarks”
Example #1: a “normal” spreadsheet
is interpreted as
The right hand shows the column name in the shaded top row
Example #2: A spreadsheet with some empty header cells