Vortex and double-quotes

[toshioh]

I have a script running in which I need to update a field with a string involving double-quotes:


<$DOCTITLE = "Summary of ""Available"" in Landfill">
.
.

<SQL MAX=1 "update tbldiscovery set DOCTITLE = DOCTITLE where DOCID = 'BBD0034874'">
</SQL>

What's the best way to get the quotes surrounding "Available" above to go into the table? I've tried escaping with a backslash, but that didn't seem to work-- the resulting text simply reads:

(Summary of ,Available, Volumes in Landfill)

Thank you

-toshioh

[mark]

Use single quote around the overall string:
<$DOCTITLE = 'Summary of "Available" in Landfill'>

[toshioh]

It resolves the initial problem if double quotes are the only special characters in the field. But if the field contains single quotes, it creates the similar problem. I need to retain all characters our clients have used. Is there any other solution?

Thank you for your help.

[John]

Where is the string coming from? Usually client's literals would not be appearing in your script. Two possible ways of doing the assignment are using <sum> or <capture>. e.g.

<sum %s "Here's a single " '"quote" (double quotes)'>
<$DOCTITLE=$ret>

or

<CAPTURE>Here's a single "quote" (double quotes)</CAPTURE>
<$DOCTITLE=$ret>

You shouldn't use these unless you really need to, as they will not perform as well as a straight assignment.

[toshioh]

Basically, what we're doing is taking data out of a SQL Server table, and putting it into a texis table via an ADO recordset.

We loop through the recordset and generate a vortex script from that. Unfortunately, the text in the recordset can contain almost any ascii character out there, including single and double quotes. Here's a sample of the VB code:

‘Create recordset
Set rs = New ADODB.Recordset

<snip>

------
‘Create text file for Vortex script
Open sBatchFile For Output As #iFile

Print #iFile, "<script language=vortex>"

<snip>

‘Read recordset and create Update statement
For i = 1 To iRecordCount
-----
'Set up Field Variable
For j = 1 To iUpdateFieldCount
sFldValue = Trim("" & rs.Fields(sFieldName(j)))
sFldValue = Replace(sFldValue, Chr(34), Chr(34) & Chr(34))
sFldValue = Replace(sFldValue, "$", "$$")
Print #iFile, vbTab & "<$" & sFieldName(j) & " = " & Chr(34) & sFldValue & Chr(34) & ">"
------
Next

'Create SQL Statement with field name & variable
sTSQL = "<SQL MAX=1 ""update " & sTexisTable & " set "

<snip>

Print #iFile, vbTab & "</SQL>"

Here's a sample script that comes out of the other end. Note that the actual script is much larger. If 1000 records need to be updated, there will be 1000 update statements.

<script language=vortex>
<db=f:\Database\CMD\tbldiscovery>
<TIMEOUT=-1></TIMEOUT>

<a name=main>
<$ret=" 1 - BBD0034874">
$ret
<$DOCTITLE = 'Summary of "Available" Volumes in Landfill1>
<SQL MAX=1 "update tbldiscovery set DOCTITLE = $DOCTITLE where DOCID = 'BBD0034874'">
</SQL>
</a>
</script>

So I'm just wondering if your suggestions still hold as to how best to deal with the quotes and single quotes, knowing what you do now about the process I'm following? Thanks again.

[mark]

The <capture></capture> method might work best for that way of doing things.

I would personally be inclined to export the data to a file that could then be read in with <timport>. Use a control character that never occurs in your text as a field delimiter and another as a record delimiter.

[toshioh]

Because data we are handling is extremely large, if only few fields need to be updated we want to avoid use of timport to minimize verification of large text file.

Can I create SQL statement to update main table from a temp table that contains DocID and fields that need to be updated? In SQL Server, I can create statement like:

update tbldoc set a.Comments, a.FullText, ... from tbldoc a, tbltemp b where a.DocID=b.DocID

I know this syntax is not valid for Texis TSQL, but is there any way to update one table from the other? This would be our solution if any.

Thank you for your help.

[John]

I think what was intended was a <timport> of just the field that needs updating with the field you want to update, so you would have:

<TIMPORT ROW $schema FROMFILE $infile>
<SQL "update tbldoc set DOCTITLE=$DOCTITLE where DOCID = $DOCID">
</SQL>
</TIMPORT>

You can use subqueries to update one table from another, but it does require care:

update tbldoc set a.Comments = (select Comments from tbltemp where tbltemp.DocID = tbldoc.DocID)
where a.DocId in (select DocID from tbltemp);

[toshioh]

Update with timport works beautifully.

Thank you for all your help.