FUNCTION DI_sqlQuery, serv, database, tblNm, sel, cond, $ DIM=dim, DISTINCT=distinct ;;----------------------------------------------------------------------------- ;; PURPOSE: ;; Queries an SQL database for information Requires that the program is ;; run in a UNIX environement and that MySQL is installed. If an error ;; occurs while acessing the database, an IDL I/O error is thrown ;; ;; CALLING SEQUENCE: ;; RETURN = DI_sqlQuery(serv,database,tblNm,sel,cond,DIM=dim) ;; ;; REQUIRED INPUTS: ;; serv - pointer to a sqlServer structure specifying how to connect to ;; the server(s). When the routine is complete, the linkedList of ;; sqlServers will be destroyed and this pointer will be null. ;; This is done to prevent memory errors. ;; database - string specifying the database on the server to use ;; tblNm - string specifying the table on the SQL server to search ;; sel - string array of the database fields elements to return. ;; One element in each cell ;; cond - string containing an SQL condition statement ;; ;; OUTPUTS: ;; RETURN - A two dimensional array of strings that were the result of ;; the query. Each column will match with the corresponding ;; element specified by sel. Each row is a different image. ;; ;; OPTIONAL INPUT KEYWORDS: ;; DIM - The dimensions of the returned array [cols rows]. ;; DISTINCT - If only distinct results should be returned from any one server ;; ;; EXAMPLE: ;; IDL> data = DI_sqlQuery(serv, database, tblNm, sel, cond) ;; ;; PROCEDURES USED (i.e. called directly!): ;; mysql - UNIX SQL client ;; ;; MODIFICATION HISTORY: ;; 2004-05-31 M. Desnoyer Created ;; 2005-07-13 M. Desnoyer Added DISTINCT ;; ;;----------------------------------------------------------------------------- tab = string(9B) nl = string(10B) errStr = '' ON_IOERROR, err ;; Create the SQL command n = (size(sel))[1] cmd = 'select ' IF keyword_set(distinct) THEN cmd = cmd + 'distinct ' FOR i=0, n-2 DO cmd = cmd + sel[i] + ',' cmd = cmd + sel[n-1] + ' from ' + tblNm + ' where ' + cond + ';' ;; Replace all double appostraphes with single ones so that it can parse correctly cmd = strjoin(strsplit(cmd,'"',/extract),"'") ;; Query all the servers cur = serv errCnt = 0 ;;Count of how many failed server queries out = strarr((size(sel))[1],1) lastCnt = 1 n = 0 WHILE ptr_valid(cur) DO BEGIN n = n+1 sqlCmd = "mysql -h "+(*cur).host+" -u "+(*cur).usr IF (*cur).pass NE '' THEN sqlCmd = sqlCmd +" --password='"+(*cur).pass+"'" sqlCmd = sqlCmd + " -D "+database+' -BN -e "'+cmd+'"' IF !version.os_family EQ 'unix' THEN BEGIN spawn, sqlCmd, res, eStr, COUNT=nrows, /sh ENDIF ELSE BEGIN spawn, sqlCmd, res, eStr, COUNT=nrows ENDELSE ;; Check for errors IF (size(eStr))[0] GT 0 THEN BEGIN errCnt = errCnt+1 errStr = errStr+nl+eStr ENDIF ;; Parse the output from the database if there was any IF nrows GT 0 THEN BEGIN dim = [(size(sel))[1],nrows] out = [[out],[strarr(dim[0],dim[1])]] FOR j=0, dim[1]-1 DO $ out[*,lastCnt+j] = strsplit(res[j],tab,/EXTRACT) lastCnt = lastCnt+nrows ;; Since we got a result, don't bother querying anybody else BREAK ENDIF cur = (*cur).next ENDWHILE IF (size(out, /n_dimensions))[0] EQ 1 THEN BEGIN dim = [0,0] out = '' ENDIF ELSE BEGIN out = out[*,1:*] dim = size(out, /dimensions) ENDELSE ;; See if we were able to connect to any server IF errCnt NE n THEN errStr = '' ;; Deal with file I/O errors when working with the temp file err: ON_IOERROR, NULL ;; Delete the server list freeLinkedList, serv ;; Throw the system error on_error,2 IF strlen(errStr[0]) GT 0 THEN message, errStr[0], /IOERROR RETURN, out END