; ###############################################################################################
; 
; Kixstart Script - Client Mailing List
;
; Script purpose   : Extract email addresses from database
;                    Disgard duplicate entires 
;                    Disgard invalid email address (i.e. anything without an @ in it)
;                    Disgard spaces due to typos (beginning, middle and end of address)
;                    Output to a file each email address in a string delimited by a semicolon (;)
;
; Kix32 Version    : 4.53.0.0
;
; Script Date      : 27/03/07
; Script Version   : 1.0
;
; ###############################################################################################


; #### Set Variables ####

DIM $DSN
DIM $SQLQueryRS

$DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; UID=; PWD=; DBQ=ActinicCatalog.mdb"
$SQLQueryRS = "SELECT 'Email Address' FROM 'Person'"



; #### Open Database Connection ####

$objConn = DBConnOpen("$DSN")
? "@ERROR"



; #### Open Database Recordset ####

$recordset = DBGetRecordset("$objConn, $SQLQueryRS")
? "@ERROR"



; #### Close Database Connection ####

$retcode = DBConnClose($objConn)
? "@ERROR"

$recordset = DBGetRecordset("$objConn, $SQLQueryRS")

$recordset = DBGetRecordset($objConn,$SQLQueryRS)

"SELECT DISTINCT 'Email Address' FROM 'Person'"

$ = redirectoutput(@SCRIPTDIR + "\foo.csv",1)

for $row=0 to ubound($recordset,1)
 for $column=0 to ubound($recordset,2) - 1
   $recordset[$row,$column] + ","
 next
 $recordset[$row,ubound($recordset,2)] + @CRLF
next

$ = redirectoutput("")

$= SetOption("WrapAtEol", "On")

; #### Set Variables ####

$DBPath = "@scriptdir\ActinicCatalog.mdb"
$CNstring = "provider=microsoft.jet.oledb.4.0;data source=$DBpath;persist security info=false"
$CMDtxt = 'SELECT "Email Address" FROM Person'



; #### Create ADO Objects ####

$cn = CreateObject ("ADODB.Connection")
? "ADO Conn       ERROR @ERROR - @SERROR"

$cmd = CreateObject ("ADODB.Command")
? "ADO Cmd        ERROR @ERROR - @SERROR"

$rs = CreateObject ("ADODB.RecordSet")
? "ADO RS         ERROR @ERROR - @SERROR"



; #### Open Database connection ####

$cn.connectionstring = $CNstring
? "ADO CN String  ERROR @ERROR - @SERROR"

$cn.Open
? "ADO CN Open    ERROR @ERROR - @SERROR"



; #### Set Active commands ####

$cmd.activeconnection = $cn
? "ADO ActConn    ERROR @ERROR - @SERROR"

;$rs.activecommand = $cmd       Is this needed? It errors if left in!
;? "ADO ActCmd     ERROR @ERROR - @SERROR"



; #### Open Recordset and Populate with results of Query ####

$cmd.commandtext = $CMDtxt $rs.Open ($cmd)
? "ADO Query      ERROR @ERROR - @SERROR"



; #### Move to the first record ####

$rs.Movefirst()
? "ADO Movefirst  ERROR @ERROR - @SERROR"
? ""



; #### Read all data in the recordset ####

$L=0
$Field = $rs.Fields.count -1
do
for $i=0 to $Field ; count through each column
$rs.Fields.item($i).Value ; spit out the value in that column
if $i<>$Field ", " endif ; comma separate fields
$L = $L +1
? "Number of times rs.Fields.Item(i).Value has been called:" $L
next

$rs.MoveNext() ; goto the next record
? "ADO Movenext  ERROR @ERROR - @SERROR"
? ; CRLF separate records

until $rs.EOF ; until we reach the End Of the file