PRO DI_sqlInsert, serv, database, tblNm, vals, IGNORE=ignore, FORMAT=format

;;-----------------------------------------------------------------------------
;; PURPOSE:
;;	Inserts data into an existing table in an SQL database.
;;	If an error occurs while acessing the database,
;;	an IDL I/O error is thrown.
;;
;; CALLING SEQUENCE:
;;	DI_sqlQuery serv,database,tblNm,values, /IGNORE
;;
;; REQUIRED INPUTS:
;;	serv - pointer to a sqlServer structure specifying how to connect to the server
;;	database - string specifying the database on the server to use
;;	tblNm - string specifying the table on the SQL server to add to
;;	vals - An IDL structure of the data to insert into the table with
;;		each field holding a value. If it is an array of structures,
;;		then each element in the array will be added as a row to the
;;		database. A field must contain either a string, non-complex
;;		number, or be undefined.
;;
;; OUTPUTS:
;;
;; OPTIONAL INPUT KEYWORDS:
;;	IGNORE - Ignore errors that occur if we try to rewrite an entry that's
;;		already there.
;;	FORMAT - An optional array of format codes for the values to enter. Each item in the array
;;		corresponds to a field in vals and thus the array must be the same size as the number
;;		of fields.
;;
;; EXAMPLE:
;;      IDL> DI_sqlInsert(serv, database, tblNm, valStruct)
;;
;; PROCEDURES USED (i.e. called directly!):
;;    mysql - UNIX SQL client
;;
;; MODIFICATION HISTORY:
;;   2004-06-11  M. Desnoyer    Created
;;
;;-----------------------------------------------------------------------------
tab = string(9B)
nl = string(10B)
valid = 0

IF SIZE(vals, /type) NE 8 THEN $
	message, 'Values to insert into database must be in a structure'

ON_IOERROR, err

;; Determine how big the list of data to insert is
valDim = SIZE(vals,/N_DIMENSIONS)
CASE valDim OF
0:BEGIN	n = 1
	m = N_TAGS(vals)
END
1:BEGIN	n = (size(vals,/DIMENSIONS))[0]
	m = N_TAGS(vals[0])
END
ELSE:	message, 'The array of values to insert contains too many dimensions'
ENDCASE

;; Check for format codes
IF keyword_set(format) THEN BEGIN
	IF n_elements(format) NE m then message, 'Size of format array must be the same as the number of fields in the vals structure'
ENDIF

;; Generate a temp file that contains the SQL commands to send to the host
OPENW, f, 'DI_SQL_Insert.tmp', /XDR, /GET_LUN
FREE_LUN, f
OPENW, f, 'DI_SQL_Insert.tmp', /GET_LUN

;; Create the SQL commands
printf, f, 'use ' + database 
FOR i=0, n-1 DO BEGIN
	;; Do each row
	cmds = 'insert '+ (keyword_set(ignore)?'ignore ':'') + 'into ' $
		+ tblNm + ' values('
	FOR j=0, m-1 DO BEGIN
		;; Insert each value
		type = SIZE(vals[i].(j),/type)
		SWITCH type OF
		;; Undefined
		0:BEGIN	cmds = cmds + 'NULL'
			BREAK
		END
		;; Numerical
		1:
		2:
		3:
		4:
		5:
		12:
		13:
		14:	
		15:BEGIN 
			IF keyword_set(format) THEN $
				cmds = cmds + strtrim(string(vals[i].(j),format=format[j]),2) $
			ELSE	cmds = cmds + strtrim(string(vals[i].(j)),2)
				
			BREAK
		END
		;; String
		7:BEGIN	cmds = cmds + "'"+vals[i].(j)+"'"
			BREAK
		END
		ELSE: message, 'Invalid value to enter into the database'
		ENDSWITCH
		
		;; Determine if a comma separator is required
		IF j LT m-1 THEN cmds = cmds + ','
	ENDFOR
	printf, f, cmds + ');'
ENDFOR
printf, f, 'quit'

FREE_LUN, f
valid = 1

;; Query the server
spawn, "mysql -h "+(*serv).host+" -u "+(*serv).usr+" --password='"+$
	(*serv).pass+ "' -BN < DI_SQL_Insert.tmp", res, errStr

IF (size(errStr))[0] GT 0 THEN BEGIN
	errStr = errStr[0]
	GOTO, err
ENDIF

;; Deal with file I/O errors when working with the temp file
err:
ON_IOERROR, NULL

;; First delete the temp file if it was made
FILE_DELETE, 'DI_SQL_Insert.tmp', /QUIET

;; An error occured while working with the temp file so pass that error along
IF NOT valid THEN errStr = !ERROR_STATE.MSG

;; Throw the system error
IF strlen(errStr) GT 0 THEN message, errStr, /IOERROR 

RETURN
END