ExpandVarStrings parsing

[paz]

I've searched the forum for an answer, came across topics very close to what I'm looking for, but no real answers... I wonder if anyone can explain to me if the following behavior is normal (or not) in the function I copy-pasted below.

Basically, my goal is to have a function that dumps Oracle recordsets into a custom "semicolon-separated values" text file. The "feature" I want to implement is to have the possibility to use AutoIT variables *in* the SQL statements.

I have Opt("ExpandVarStrings",1) at the beginning of my script, so in theory, if I use $var$ in my SQL Script, AutoIT should parse them, right?

I know now that reading a file with FileRead() is not enough to have the AutoIT variables parsed - I have to "do" something with the data read from the file to make sure AutoIT expands the variables.

In the following script, I was expecting that using a variable as a temporary holder for my SQL statement was doing the trick (the $ORA_Query variable), and expected that using this variable as a parameter for the ADODB.RecordSet.Open method would have AutoIT parse the variables for me... but looks like it's not the case - I really have to do the dumb $ORA_Query = $ORA_Query instruction to have the script behave properly (that is, replace the AutoIT variables with the actual values)

I'd prefer using FileRead() directly in the recordset.Open method, but can understand that the parsing would slow down the file read function... but having to assing a variable to itself seems a bit odd, and wonder if there is a technical reason for this?

Thanks for any infos

(and sorry for the looong post)

expandcollapse popup

;Take a SQL Script, runs it, and save the recordset to a text file.
;Each fields are separated by semicolons, so I don't have to worry of the database fields having comma in thier values,
;AND save some disk space as I won't need quotes either (68K rows x 10 fields = ~1.3Mb of useless Quote chars!)
;
;$script: fully qualified filename for the SQL script to run
;$connect: semicolon-separated values for the database name, username and password to connect to Oracle
;$output: fully qualified filename for the output file
Func SQL2TXT($script,$connect,$output)
    
    ;Connection to Oracle
    $connect = StringSplit($connect,';');split each params
    $ORA_dbc = ObjCreate( "ADODB.Connection")
    
    ;ok, ok... this func expects the password to be in plain text
    ;I'll take care of the security sometimes in the future;-)
    $ORA_dbc.Open("Driver={Microsoft ODBC for Oracle};Server=" & $connect[1] & ";Uid="&$connect[2]&";Pwd="&$connect[3]&";")
    
    
    ;Read the SQL script. May contains AutoIT variables, (like $province_id$)
    ;The variables should be replaced by AutoIT, so the script becomes a valid SQL statement
    $ORA_Query = FileRead($script)  
    
    ;Looks like I have to do that in order to AutoIT to parse environment variables???
    ;If I don't do this, I get a COM error when the SQL statement have $var$ stuff :-/
    $ORA_Query = $ORA_Query         
    
    
    ;Runs the SQL query
    $ORA_rst = ObjCreate("ADODB.Recordset")
    $ORA_rst.Open($ORA_Query, $ORA_dbc)
    
    
    ;Write the database text file
    ;The GetString method is used to convert the recordset into my semicolon-separated format
    ;I limit myself to 99999 records for now, as out database is "forward-cursor" only, and do not
    ;support any rows count methods... and I can't see the day where we will have more than 80K polling divisions anyways.
    $temp = FileOpen($output,2)
    FileWrite($temp, $ORA_rst.GetString(2,99999,';',@crlf,''))  
    FileClose($temp)
    
    
    ;Write the database file schema.
    $schema = StringLeft($output,StringInStr($output,"\",0,-1)) & "schema.ini";isolate the output path, append "shema.ini"
    $filename = StringTrimLeft($output,StringInStr($output,"\",0,-1));isolate output filename, used as the schema section name.
    
    ;make sure the shema is aware that it's a semicolon-separated database
    IniWrite($schema,$filename ,"Format","Delimited(;)")    
    
    ;no headers in the file, as I define them in the schema anyways.
    IniWrite($schema,$filename ,"ColNameHeader","False")    
    
    ;All the fields will be defined as 'Char' for now, otherwise, the text driver parse strings
    ;like "0010" as numbers (convert to "10"). And I don't want that.
    for $i = 1 to $ORA_rst.Fields.Count
        IniWrite($schema,$filename ,"Col"&$i,'"' & $ORA_rst.Fields($i-1).Name & '" Char')
    Next
    
    ;Close the COM objects
    $ORA_rst.Close
    $ORA_dbc.Close
EndFunc

[/dev/null]

In the following script, I was expecting that using a variable as a temporary holder for my SQL statement was doing the trick (the $ORA_Query variable), and expected that using this variable as a parameter for the ADODB.RecordSet.Open method would have AutoIT parse the variables for me... but looks like it's not the case - I really have to do the dumb $ORA_Query = $ORA_Query instruction to have the script behave properly (that is, replace the AutoIT variables with the actual values)

That's strange, because the vars will get expanded whenever the string gets evaluated. See sample:

#include <File.au3>

$text = "REPLACED"

Opt("ExpandVarStrings",0)

$file = "test.txt"
FileWrite($file,"Hello World $text$")
$content = FileRead($file)
MsgBox(0,"",$content)


Opt("ExpandVarStrings",1)

$content = FileRead($file)
MsgBox(0,"",$content)

Maybe, there is a problem with the COM interface of AutoIT. You should double check if you really did not mess up something and then post the problem in the Bugs section with some simple and easy to verify samples, so you might have to find another COM call, where you can show that no variable expansion takes place. I guess the database sample is too complex to follow....

EDIT: See here: http://www.autoitscript.com/forum/index.php?showtopic=40957

Cheers

Kurt

Edited February 9, 2007 by /dev/null