- This topic has 5 replies, 2 voices, and was last updated 10 years ago by
.
-
Topic
-
We just installed the Data Integrator on our Cloverleaf Engine. We have never connected to a Microsoft SQL database and was looking for some sample TCL code to get us started. Is there anything else we would need to install besides the Data Integrator to be able to Query the database from the cloverleaf interface?
-
Replies
-
-
That should be all you need.
You need to add your data source to $HCIROOT/lib/Connect6.0/odbc.ini. I think you just have to clone the section that says “[SQLServer Wire Protocol]”. Put the name of your database in the brackets and fill in the Address item. You can leave LogonID and Password blank; those can be set in the TCL code.
Here’s some code from our site that connects to SQL Server. I’d probably have written it a little bit differently, but it should get you started.
Code:## Get database server name and database name from user supplied argument keyed list ARGS
keylget args ARGS.DBSERVER dbserver
keylget args ARGS.DBNAME dbname
keylget args ARGS.DBUSER dbuser
keylget args ARGS.DBPWD dbpwd
keylget args ARGS.DOCTYPE docType## Get file name from message metadata
set userdata [msgmetaget $mh USERDATA]
keylget userdata OBFILENAME pdfFileName#puts “$module $pdfFileName”
regexp -nocase — {[[:alpha:]]+d+_d+_(d+)} $pdfFileName dummy distItemId
#puts “$module AFTER $distItemId”## ODBC
set ERR_CODE 0 ;# set good error code.
# ———- initialize ODBC environment ———————
# initialize the ODBC call level interfacepackage require odbc
set rVal [odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv]
# application is ODBC 3
# err could be SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, or SQL_ERROR
#if { $rVal != “SQL_ERROR” } {
set rVal [odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0]
} else {
puts stderr “$module SQLAllocHandle failed.”
}# allocate a connection handle
if { $rVal != “SQL_ERROR” } {
set rVal [odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc]
} else {
puts stderr “$module SQLSetEnvAttr failed.”
}
# ————————————————————————–
# connect to the data source: data source name: ARGS.DBSERVER
if { $rVal != “SQL_ERROR” } {
set rVal [odbc SQLConnect $hdbc $dbserver SQL_NTS $dbuser SQL_NTS $dbpwd SQL_NTS]
if { $rVal == “SQL_ERROR” } {
echo “$module ODBCERROR on SQLConnect”
odbc SQLGetDiagRec SQL_HANDLE_DBC $hdbc 1 SqlState NativeError MessageText 511 TextLength
puts stderr “$module ”
}
}
# allocate a statement handle
set rVal [odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt]if { $rVal == “SQL_ERROR” } {
puts stderr “$module Could not allocate statement handle”
}set getDataSqlStmt “SELECT ${dbname}..acc_report.report_image, ${dbname}..accession_2.accession_no, ${dbname}..patient_mrn_2.med_re
c_no, ${dbname}..patient_visit.visit_number FROM ${dbname}..dist_item INNER JOIN ${dbname}..acc_report ON ${dbname}..dist_item.acc_report_id =
${dbname}..acc_report.id INNER JOIN ${dbname}..accession_2 ON ${dbname}..acc_report.acc_id = ${dbname}..accession_2.id LEFT OUTER JOIN ${dbname
}..patient_mrn_2 ON ${dbname}..accession_2.patient_mrn_id = ${dbname}..patient_mrn_2.id LEFT OUTER JOIN ${dbname}..patient_visit ON ${dbname}..
accession_2.patient_visit_id = ${dbname}..patient_visit.patient_visit_id WHERE ${dbname}..dist_item.dist_item_id = ‘$distItemId'”set rVal [odbc SQLExecDirect $hstmt $getDataSqlStmt SQL_NTS]
if { $rVal == “SQL_ERROR” } {
odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt 1 SqlState NativeError MessageText 511 TextLength
puts stderr “$module ”
}
set rVal [odbc SQLFetch $hstmt]## reset temp variables
set i 0
set sql_report_image {}
set sql_accession_no {}
set sql_med_rec_no {}
set sql_visit_number {}while { $rVal == “SQL_SUCCESS” || $rVal == “SQL_SUCCESS_WITH_INFO” } {
# get size of the BLOB
set rVal [odbc SQLGetData $hstmt 1 SQL_C_BINARY sql_report_image 0 rptSize]
if { $rVal == “SQL_ERROR” } {
odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt 1 SqlState NativeError MessageText 511 TextLength
puts stderr “$module ”
} else {
# if there are more than 0 bytes then attempt to process
if { $rptSize > 0 } {
odbc SQLGetData $hstmt 1 SQL_C_BINARY sql_report_image $rptSize dummy
odbc SQLGetData $hstmt 2 SQL_C_CHAR sql_accession_no 30 dummy
odbc SQLGetData $hstmt 3 SQL_C_CHAR sql_med_rec_no 30 dummy
odbc SQLGetData $hstmt 4 SQL_C_CHAR sql_visit_number 30 dummy#puts “$module ”
if { [catch { set rtfImage [inflate $sql_report_image] } catchRetVal] } {
puts stderr $catchRetVal
}
}
}
set rVal [odbc SQLFetch $hstmt]
incr i}
# ——— clean up —————–
odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt
odbc SQLDisconnect $hdbc
odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc
odbc SQLFreeHandle SQL_HANDLE_ENV $henv
#– -
David, Thanks for the sample code but running into an error when I try to run it and didn’t know if you had run across the same issue or not. The error message is saying it can’t find the driver when it hits the piece of code that does the SQLConnect. Any ideas how to fix this?
cannnot find Driver keyword entry for data source XXX[xlt :xlat:ERR /0:itsengine_xlate:–/–/—- –:–:–] while executing
[xlt :xlat:ERR /0:itsengine_xlate:–/–/—- –:–:–] “odbc SQLConnect $hdbc $dbserver SQL_NTS $dbuser SQL_NTS $dbpwd SQL_NTS”
-
-
-
-
- The forum ‘Tcl Library’ is closed to new topics and replies.
