;+ ; NAME: ; mysqlquery2 ; a version of mysqlquery modified by M. Perrin to have ; an improved output format ; PURPOSE: ; Submit MySQL query and get response as an array of structures. ; DESCRIPTION: ; Submit a simple SQL query to MySQL server, using the connection ; previously opened with openmysql. Retrieve the result into ; a STRUCTURE ARRAY, as does QUERYVIZIER.PRO ; CATEGORY: ; Database ; ; CALLING SEQUENCE: ; mysqlquery,lun,query,[varables...],[format='(a,f,...)'] ; ; INPUTS: ; lun - The logical unit of the pipe (opened by openmysql). ; query - String (or array of strings) to send to pipe. ; ; OPTIONAL INPUT PARAMETERS: ; KEYWORD INPUT PARAMETERS: ; format - Specify format of output variables (default is ascii). ; cmd - Flag indicating this is a command, not a query so ; don't bother processing the output (but do report the ; number of rows affected/warnings? - not implimented). ; verbose - Flag turns on debugging output to standard out. ; ; OUTPUTS: ; variables - A list of variables to recieve columns of output. ; Default type is ascii, but use the format keyword to ; specify other data types. ; ; KEYWORD OUTPUT PARAMETERS: ; heads - String to receive array of column heads. ; ; COMMON BLOCKS: ; SIDE EFFECTS: ; RESTRICTIONS: ; Requires an open connection to MySQL server (established by ; use of openmysql) as well as valid permissions for whatever ; query or command is to be executed. ; ; PROCEDURE: ; MODIFICATION HISTORY: ; 2002-02-14 Will Grundy, adapted from earlier version called mysql.pro ; 2002-02-27 WG changed behavior so 'NULL' becomes NaN instead of ; making the line be ignored when it occurs in a numerical ; field. ; 2002-03-25 WG changed to split on tab instead of white space, so that ; strings with internal spaces (but not tabs) can be retrieved. ; 2003/01/10, MWB, fixed multi-line query error. Only one query per ; call is allowed. ; 2005-10-11, M. Perrin Fixed NaN replacement for NULLs ; 2006-05-14, M. Perrin Fixed handling of null strings in query results ; 2007-06-01, M. Perrin Modified output format to be a structure array. ; ; BUGS/WISH LIST: ; Ought to verify connection to MySQL server. ; Does nothing helpful with SQL command results. ; Does nothing helpful to identify/report bad SQL syntax. ;- pro mysqlquery2,lun,query,info, $ FORMAT=fmt,HEAD=heads,CMD=cmd,VERBOSE=verbose,$ ngood=ngood ; Zero the output variables, so there's no risk of accidentally ; re-processing a previous result if something went wrong. heads = '' ; lun and query string are manditory, as are at least one output ; variable (unless the /CMD flag is set) if (keyword_set(cmd) and n_params() lt 2) or $ (not keyword_set(cmd) and n_params() lt 3) then begin print,'Usage: n = mysqlquery(lun,query,v1,[v2,v3,v4,...])' return endif mysqlcmd,lun,query,result,nlines,debug=verbose if not keyword_set(cmd) then begin ; First digest the column headings (split on tabs) heads = strsplit(result[0],' ',/extract) ncol = n_elements(heads) if ncol gt 0 then begin vv = 'v' + strtrim( indgen(ncol)+1, 2) for k=0,ncol-1 do begin st = vv[k] + ' = ""' tst = execute(st) endfor endif ; Next process everything else into a series of variables, using ; code shamelessly lifted from the astro library's readcol.pro. ; (thank you kindly, Landsman et al.!) nskip = 0 if N_elements(fmt) gt 0 then begin ;FORMAT string supplied? ; Grind format string into usable form zparcheck, 'MYSQL', fmt, 2, 7, 0, 'FORMAT string' frmt = strupcase(strcompress(fmt,/REMOVE)) remchar, frmt, '(' remchar, frmt, ')' pos = strpos(frmt, 'X', 0) while pos ne -1 DO begin pos = strpos( frmt, 'X', pos+1) nskip = nskip + 1 endwhile endif else begin ; Default is ascii format (least likely to fail) frmt = 'A' if ncol gt 1 then for i = 1,ncol-1 do frmt = frmt + ',A' endelse nfmt = ncol + nskip idltype = intarr(nfmt) ; Create output arrays according to specified formats k = 0L hex = bytarr(nfmt) for i = 0L, nfmt-1 DO begin fmt1 = gettok( frmt, ',' ) if fmt1 eq '' then fmt1 = 'A' ; Default is ascii format case strmid(fmt1,0,1) of 'A': idltype[i] = 7 'D': idltype[i] = 5 'F': idltype[i] = 4 'I': idltype[i] = 2 'B': idltype[i] = 1 'L': idltype[i] = 3 'Z': begin idltype[i] = 3 ;Hexadecimal hex[i] = 1b end 'X': idltype[i] = 0 else: message,'Illegal format '+fmt1+' in field '+strtrim(i,2) endcase ; Define output arrays if idltype[i] ne 0 then begin st = vv[k] + '= make_array(nlines,TYPE = idltype[i] )' tst = execute(st) k = k+1 ;MDP modifications, using code from queryvizier.pro cval = fix(0,type=idltype[i]) if i EQ 0 then info = create_struct(heads[0], cval) else begin ; If you set the /ALLCOLUMNS flag, in some cases (2MASS) you ; get a duplicate column name. Check for this and avoid it by appending ; an extra bit to the duplicate name if where(tag_names(info) eq strupcase(heads[i])) ge 0 then colname[i]+='_2' info = create_struct(temporary(info), heads[i],cval) endelse endif endfor ngood = 0L temp = ' ' for j=1L,nlines-1 DO begin ; Skip first line (headers) k = 0 temp = strtrim(result[j],1) ;MDP modifications to allow blank fields to be extracted ; as null strings tab = 09b var = strsplit(temp,tab,/extract,/preserve_null) ;end MDP for i = 0L,nfmt-1 DO begin if ( idltype[i] ne 0 ) then begin ;Expecting data? if i+1 gt n_elements(var) then begin ngood=ngood-1 goto, badline endif if ( idltype[i] ne 7 ) then begin ;Check for numeric data tst = strnumber(var[i],val,hex=hex[i]) ;Valid number? ; Instead of failing on 'NULL', need to return 'NaN' if strmatch(var[i],'NULL') then begin var[i] = 'NaN' val=!values.f_nan ; MDP hack tst = 1 endif if tst eq 0 then begin ;If not, skip this line ngood = ngood-1 goto, BADLINE endif st = vv[k] + '[ngood] = val' endif else st = vv[k] + '[ngood] = strtrim(var[i],2)' tst = execute(st) k = k + 1 endif endfor BADLINE: ngood = ngood+1 endfor if ngood eq 0 then begin message,'ERROR - No valid lines found for specified format',/INFORM return endif ; Compress arrays to match actual number of valid lines for i = 0,ncol-1 DO tst = execute(vv[i] + '='+ vv[i]+ '[0:ngood-1]') endif else begin ; Executed a command that wasn't a query, just return number of ; lines affected message,'WARNING - cmd executed, but not checked',/INFORM endelse ; more modifications by MDP - pack data into structure array. info = replicate(info,ngood) for i = 0,ncol-1 DO tst = execute("info."+ heads[i] + '='+ vv[i]) end