Using kiodbc with an Oracle / PostgreSQL database
PostgreSQL & Oracle databases can be queried by ODBC clients directly, however by using kiodbc, the application can restrict what data can be made visible:-
The kiodbc server is configured in kconf.xml and shares a Connection Manager service with a KClient/KCML. An additional environment variable, $KDB_ODBC_LOADLIB, defines the path of a KCML library which supplies a 'Odbc(p AS KDB_ODBC) callback routine.
This library is loaded after the user connects and 'Odbc() is initially called to open the database connection:- FLD(p.KDB_ODBC_State) is KDB_ODBC_STATE_CONNECT. If a connection to the database has been established it is returned back to kiodbc via the FLD(p.KDB_ODBC_ConnHandle) field and the FLD(p.KDB_ODBC_ReturnCode) field is to TRUE to indicate success.
When a SQL query is made against the database, 'Odbc() will be called again, but this time FLD(p.KDB_ODBC_State) will be KDB_ODBC_STATE_PERMISSIONS. The FLD(p.KDB_ODBC_TableCollection) field holds a list of tables that have been referenced in the query. Each table's list entry has a list of columns that were alse reference by the query. The application can then use the lists of tables and columns to decide if the query is allowed, or not, by setting the FLD(p.KDB_ODBC_ReturnCode) field to TRUE or FALSE respectively.
If a query has been rejected because it references tables or columns that the application does not wish to publish, then it can return an error message back to the client by setting the FLD(p.KDB_ODBC_ErrorMessage$) field.
$COMPLIANCE 3
PRIVATE DEFSUB 'UserHasAccessToColumn(pUser$,pColumn AS PTR(_kdb_odbc_column)) AS bool
// TODO Decide who has access to what columns
RETURN TRUE
END SUB
PUBLIC CALLBACK 'odbc(p AS PTR(_kdb_odbc))
LOCAL DIM dsn$0
LOCAL DIM dbType$0
LOCAL DIM dbUser$0
LOCAL DIM dbPwd$0
LOCAL DIM sConnErr$256
LOCAL DIM s AS kdb_error_enum
LOCAL DIM hConn AS connectionhandle
LOCAL DIM bAllowed AS bool
LOCAL DIM pTbl AS PTR(_kdb_odbc_table)
LOCAL DIM pCol AS PTR(_kdb_odbc_column)
REDIM dsn$ = "host=toolset2.gbh.dsi.adp.com dbname=k8fusion"
REDIM dbtype$ = "POSTGRES"
REDIM dbuser$ = "greg"
REDIM dbpwd$ = "greg123"
FLD(p.kdb_odbc_returncode) = FALSE
IF (FLD(p.kdb_odbc_state)==_kdb_odbc_state_connect)
// First call, connect to the database ...
CALL KI_ALLOC_CONNECT _kdb_auto_handle,TRUE TO hconn,s
CALL KI_CONNECT dbtype$,hconn,dsn$,dbuser$,dbpwd$ TO s
IF (s==_ke_success)
// Store the connection and query handles
FLD(p.kdb_odbc_connhandle) = hconn
// Pass successful return value back to kiodbc
FLD(p.kdb_odbc_returncode) = TRUE
ELSE
// Failed to connect to the database
// Pass back the reason to the ODBC client
CALL KI_ERROR_TEXT -hconn TO sconnerr$,s
REDIM FLD(p.kdb_odbc_errormessage$) = sconnerr$
END IF
ELSE IF (FLD(p.kdb_odbc_state)==_kdb_odbc_state_permissions)
// Second call. SQL has been prepared & executed
// Test if the authenticated user is allowed
// access to the columns referenced in the SQL query
ballowed = TRUE
FOR (ptbl = FLD(p.kdb_odbc_tablecollection); ptbl <> 0; ptbl = FLD(ptbl.kdb_odbc_table_next))
FOR (pcol = FLD(ptbl.kdb_odbc_table_columnlist); pcol <> 0; pcol = FLD(pcol.kdb_odbc_column_next))
IF (NOT 'userhasaccesstocolumn(FLD(p.kdb_odbc_user$),pcol))
IF (ballowed)
// First column we've disallowed
REDIM FLD(p.kdb_odbc_errormessage$) = "Access denied on column "
ELSE
REDIM FLD(p.kdb_odbc_errormessage$) = FLD(p.kdb_odbc_errormessage$) & ", "
END IF
REDIM FLD(p.kdb_odbc_errormessage$) = FLD(p.kdb_odbc_errormessage$) & FLD(pcol.kdb_odbc_column_name$)
ballowed = FALSE
END IF
END FOR
END FOR
// Tell kiodbc if this query is permitted
FLD(p.kdb_odbc_returncode) = ballowed
ELSE
REM Ignore unknown states that may be introduced in future versions
END IF
END SUB
KCML 7.13 build 21324 introduces a KDB_ODBC_Stat structure that is populated when the kiodbc server has finished fetching the results and has sent them to the ODBC client. The 'odbc() routine is called with a FLD(p.KDB_ODBC_State) value of _KDB_ODBC_STATE_STATS
LOCAL DIM pDbStats AS PTR(_kdb_odbc_stat)
// ... connect to database, check permissions etc ...
ELSE IF (FLD(p.kdb_odbc_state)==_kdb_odbc_state_stats)
pdbstats = FLD(p.kdb_odbc_stats)
IF (pdbstats <> 0)
// Called after returning the result set to the ODBC client
// Record the database load statistics in syslog, application may want to log this internally
WRITE LOG $PRINTF("SQL %s: Prepare=%g msec, execute=%g msec, fetch=%g msec, record length=%g, number of records=%d, bytes sent to client=%g, fetch count=%g",
FLD(p.kdb_odbc_sql$),
FLD(pdbstats.kdb_odbc_stat_preparetime),
FLD(pdbstats.kdb_odbc_stat_exectime),
FLD(pdbstats.kdb_odbc_stat_fetchtime),
FLD(pdbstats.kdb_odbc_stat_recordlength),
FLD(pdbstats.kdb_odbc_stat_records),
FLD(pdbstats.kdb_odbc_stat_bytessent),
FLD(pdbstats.kdb_odbc_stat_fetchcount))
END IF
KCML 6.20 and KCML 7.15 build 22156 introduce support for logging session statistics in a tab-delimited format. If KCML_ODBC_LOG_DIR environment variable in the service is the path of a writable directory, each kiodbc session will log to an odbclog$PID.txt file in it. An example of such a file:
pid 19391 start-time 2016-09-21 10:04:00.869 logname deasp start-request request-start-time 2016-09-21 10:04:00.870 request-end-time 2016-09-21 10:04:00.871 query select * from data1 prepare-duration 0s000000us execute-duration 0s000000us fetch-duration 0s001000us fetch-count 1 row-count 2 end-request start-request request-start-time 2016-09-21 10:04:00.871 request-end-time 2016-09-21 10:04:00.871 query select * from data3 prepare-duration 0s000000us execute-duration 0s000000us fetch-duration 0s000000us fetch-count 1 row-count 6 end-request error 7f S1000 verify column not found 2 (NONEXISTENT) start-request aborted request-start-time 2016-09-21 10:04:00.871 request-end-time 2016-09-21 10:04:00.872 query select nonexistent from data1 prepare-duration 0s001000us execute-duration 0s000000us fetch-duration 0s000000us fetch-count 0 row-count 0 end-request process-real-time 0s003000us process-user-time 0s032064us process-system-time 0s004275us total-row-count 8 bytes-sent 3216 bytes-read 156042 read-operations 173 ru_maxrss 10324 ru_minflt 972 ru_majflt 0 ru_nswap 0 ru_inblock 0 ru_oublock 0 ru_nvcsw 2 ru_nivcsw 50
ODBC applications, such as Microsoft Query, can request the list of tables in the database. Normally this is done by returning all those tables in the database's catalogue. However, this may not be desirable as there may be many tables in the catalogue.
In such cases the application can specify its own catalogue table list by setting FLD(p.KDB_ODB_FilterCatalog) to TRUE when 'odbc() is called with a state of KDB_ODBC_STATE_CONNECT. When a request is made for the database's table list, 'odbc() will then be called with a FLD(p.KDB_ODBC_State) of KDB_ODBC_STATE_CATALOG. The application then constructs a list of KDB_ODBC_CatalogList STRUCTs and assigns it to FLD(p.KDB_ODBC_CatalogList). This list will then be returned back to the ODBC client instead of all the tables from the database catalogue.
00100 $COMPLIANCE 3
: DIM hGlobalPool AS poolhandle
:
: PUBLIC DEFSUB 'Constructor()
: WRITE LOG "Constructor called"
: END SUB
:
: REM Don't allow access to some columns in GB_00_GROUPS
00200 PRIVATE DEFSUB 'Allowed(sColName$) AS bool
: // TODO Decide who has access to this column
: RETURN TRUE
: END SUB
: REM Add a table to our alternative catalogue list
00300 PRIVATE DEFSUB 'AddTable(hPool AS poolhandle,tbl$) AS PTR(_kdb_odbc_cataloglist)
: LOCAL DIM pNew AS PTR(_kdb_odbc_cataloglist)
: pnew = NEW _kdb_odbc_cataloglist USING hpool
: FLD(pnew.kdb_odbc_catalog_next) = 0
: REDIM FLD(pnew.kdb_odbc_catalog_tablename$) = tbl$
: RETURN pnew
: END SUB
00310 PRIVATE DEFSUB 'FetchCat(hCat AS tablehandle,BYREF r$_kdb_catalog) AS kdb_error_enum
: LOCAL DIM rc AS kdb_error_enum
: CALL KI_FETCH hcat,SYM(r$) TO rc
: RETURN rc
: END SUB
00320 PRIVATE DEFSUB 'OpenCat(hConn AS connectionhandle,BYREF hCat) AS kdb_error_enum
: LOCAL DIM rc AS kdb_error_enum
: CALL KI_ALLOC_HANDLE _kdb_auto_handle,hconn TO hcat,rc
: IF (rc==_ke_success)
: CALL KI_TABLES hcat TO rc
: END IF
: RETURN rc
: END SUB
00330 PRIVATE DEFSUB 'VisibleTable(sTableName$) AS bool
: // TODO Decide if table should be visible to an ODBC client application
: RETURN TRUE
: END SUB
: REM If env var sName$ is set return its value, otherwise return sDefault$
00400 PRIVATE DEFSUB 'EnvDefault$(sName$,sDefault$)
: LOCAL DIM sValue$0
:
: REDIM svalue$ = ENV(sname$)
: IF (svalue$=="")
: svalue$ = sdefault$
: END IF
: RETURN svalue$
: END SUB
: REM Main entry point
01000 PUBLIC CALLBACK 'odbc(p AS PTR(_kdb_odbc))
: LOCAL DIM dsn$0,dtype$0,dbUser$0,dbPwd$0
: LOCAL DIM hConn AS connectionhandle
: LOCAL DIM status AS kdb_error_enum
: LOCAL DIM freeStatus AS kdb_error_enum
: LOCAL DIM sConnErr$1024
: LOCAL DIM pCol AS PTR(_kdb_odbc_column)
: LOCAL DIM pTbl AS PTR(_kdb_odbc_table)
: LOCAL DIM pStats AS PTR(_kdb_odbc_stat)
: LOCAL DIM sLogFile$0
: LOCAL DIM bAllowed AS bool
: LOCAL DIM pCat AS PTR(_kdb_odbc_cataloglist)
: LOCAL DIM pNewCat AS PTR(_kdb_odbc_cataloglist)
: LOCAL DIM nCount
: LOCAL DIM hCat AS tablehandle
: LOCAL DIM rCat$_kdb_catalog
: REDIM dsn$ = 'envdefault$("PRIMARYDB_DSN","host=vhost dbname=k8fusion")
: REDIM dtype$ = 'envdefault$("PRIMARYDB_TYPE","POSTGRES")
: REDIM dbuser$ = 'envdefault$("PRIMARYDB_USERID","kcc")
: REDIM dbpwd$ = 'envdefault$("PRIMARYDB_PASSWORD","kcc123")
: WRITE LOG $PRINTF("State = %g",FLD(p.kdb_odbc_state))
: ballowed = TRUE
: FLD(p.kdb_odbc_returncode) = FALSE
: IF (FLD(p.kdb_odbc_state)==_kdb_odbc_state_connect)
: // Connect to the database ...
: CALL KI_ALLOC_CONNECT _kdb_auto_handle,TRUE TO hconn,status
: IF (status==_ke_success)
: CALL KI_CONNECT dtype$,hconn,dsn$,dbuser$,dbpwd$ TO status
: IF (status==_ke_success)
: // Store the connection and query handles
: FLD(p.kdb_odbc_connhandle) = hconn
: FLD(p.kdb_odbc_returncode) = TRUE
: // Request that we be called to specify our own catalogue table list
: FLD(p.kdb_odbc_filtercatalog) = TRUE
: // Done
: WRITE LOG $PRINTF("Connected OK on %g",'kcml_decodemagictovalue(FLD(p.kdb_odbc_connhandle)))
: ELSE
: CALL KI_ERROR_TEXT -hconn TO sconnerr$,freestatus
: REDIM FLD(p.kdb_odbc_errormessage$) = sconnerr$
: WRITE LOG $PRINTF("connect failed %s",FLD(p.kdb_odbc_errormessage$))
: END IF
:
: END IF
: ELSE IF (FLD(p.kdb_odbc_state)==_kdb_odbc_state_permissions)
: WRITE LOG $PRINTF("Test ACL, sql = '%s'",FLD(p.kdb_odbc_sql$))
: REM app tests ACL here
: REDIM slogfile$ = ENV("WORKSPACE") & "/odbc.log"
: SELECT PRINT <slogfile$>(0)
: PRINT $PRINTF("Checking perms for %s",FLD(p.kdb_odbc_sql$))
: ptbl = FLD(p.kdb_odbc_tablecollection)
: FOR (ptbl = FLD(p.kdb_odbc_tablecollection); ptbl<>0; ptbl = FLD(ptbl.kdb_odbc_table_next))
: PRINT "Checking table ";FLD(ptbl.kdb_odbc_table_name$)
: FOR (pcol = FLD(ptbl.kdb_odbc_table_columnlist); pcol<>0; pcol = FLD(pcol.kdb_odbc_column_next))
: PRINT "Column ";FLD(pcol.kdb_odbc_column_name$)
: IF (NOT 'allowed($UPPER(FLD(pcol.kdb_odbc_column_name$))))
: IF (ballowed)
: REM first column we've disallowed
: REDIM FLD(p.kdb_odbc_errormessage$) = "Access denied on column "
: ELSE
: REDIM FLD(p.kdb_odbc_errormessage$) = FLD(p.kdb_odbc_errormessage$) & ", "
: END IF
: ballowed = FALSE
: REDIM FLD(p.kdb_odbc_errormessage$) = FLD(p.kdb_odbc_errormessage$) & FLD(pcol.kdb_odbc_column_name$)
: END IF
: END FOR
: END FOR
: IF (ballowed)
: FLD(p.kdb_odbc_returncode) = TRUE
: PRINT "Access granted"
: ELSE
: PRINT FLD(p.kdb_odbc_errormessage$)
: END IF
: SELECT PRINT /005
: ELSE IF (FLD(p.kdb_odbc_state)==_kdb_odbc_state_stats)
: REM Request for performance statistics
: pstats = FLD(p.kdb_odbc_stats)
: IF (pstats<>0)
: WRITE LOG $PRINTF("prep=%g ms, exec=%g ms, fetch=%g ms, rec len=%g, # recs=%d, bytes sent=%g, fetches=%g",FLD(pstats.kdb_odbc_stat_preparetime),FLD(pstats.kdb_odbc_stat_exectime),FLD(pstats.kdb_odbc_stat_fetchtime),FLD(pstats.kdb_odbc_stat_recordlength),FLD(pstats.kdb_odbc_stat_records),FLD(pstats.kdb_odbc_stat_bytessent),FLD(pstats.kdb_odbc_stat_fetchcount))
: END IF
: WRITE LOG "stats"
: ELSE IF (FLD(p.kdb_odbc_state)==_kdb_odbc_state_catalog)
: // Requested to supply our own table list as an alternative to the catalogue
: // Create list entries in the global pool as this needs to be accessible after 'odbc() has returned
: IF (hglobalpool==0)
: hglobalpool = CREATE POOL
: END IF
: // Build a catalogue table list by reading the catalogue, but selectively adding tables to the list
: status = 'opencat(FLD(p.kdb_odbc_connhandle),BYREF hcat)
: IF (status==_ke_success)
: ncount = 0
: WHILE (TRUE) DO
: status = 'fetchcat(hcat,BYREF rcat$)
: IF (status==_ke_success)
: IF ('visibletable(FLD(rcat$.catalog_tablename$)))
: pnewcat = 'addtable(hglobalpool,FLD(rcat$.catalog_tablename$))
: IF (ncount==0)
: // New list
: FLD(p.kdb_odbc_cataloglist) = pnewcat
: ELSE
: // Chain onto end of existing list
: FLD(pcat.kdb_odbc_catalog_next) = pnewcat
: END IF
: pcat = pnewcat
: ncount++
: END IF
: ELSE
: // No more catalogue records to fetch
: BREAK
: END IF
: WEND
: // No longer need result set
: CALL KI_FREE_HANDLE hcat TO status
: WRITE LOG $PRINTF("Built alternative catalogue list of %d tables",ncount)
: END IF
: END IF
:
: END SUB
Oracle & PostgreSQL support was originally introduced with KCML 7.05.
Database load statistics were introduced in KCML 7.13.
Database load logging was introduced for legacy KISAM/KDB databases in KCML 6.20 & 7.15, build 22156.
Application defined catalogue listings are available in KCML 7.16 or later.
Multi-threaded ODBC client programs must use a separate socket by calling SQLAllocConnect() & SQLDriverConnect() on each thread instead of sharing one connection over multiple threads.
See also