Документ взят из кэша поисковой машины. Адрес оригинального документа : http://www.stsci.edu/~mperrin/software/sources/mysqlquery2.pro Дата изменения: Sat Aug 11 03:00:47 2007 Дата индексирования: Sat Mar 1 16:09:25 2014 Кодировка: Поисковые слова: п п п п п п п р п

;+
; 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