<getvar> and <SQL>-- do they mix?

[Mr. Bigglesworth]

I've got some variables defined like so:

<SQL "select * from mytable">
.
.

<READLN "e:\mytextfile">

<!--get array like so: value1,value2,value3-->
<split "," $ret>
<loop $ret>
<if $loop eq "0">
<$field_name = $ret>
<else if $loop eq "1">
<$field_display = $ret>
</if>
</loop>

<getvar $field_name>
$field_display equals: $ret <BR>

</READLN>
.
.
.


</SQL>



Basically, I have a comma delimited text file with a descriptive name of the SQL columns I'm selecting ($field_display) and the actual SQL column ($field_value)

When I actually write stuff out to the screen, I get this:

FIELD1 equals:
FIELD2 equals:


In other words, <getvar> isn't getting the value of the current SQL record's columns.

Am I doing something wrong here (quite likely), does <getvar> only work for CGI variables and not <SQL> defined ones (possible), and if so, is there a way to do what I'm trying-- keep the display order of the SQL columns in a text file, then use that text file to write out the records in the display order I choose?

[John]

<getvar> only works with CGI variables, and variables
that are used somewhere in the script. If you know the possible set of column names for the table you can define a dummy function that would display all the variables if called, that way Vortex will know about them, even if you never call the function. For example:

<A NAME=dummy>
$column1 $column2 $column3 ...
</A>

[Mr. Bigglesworth]

Thanks for the tip.

I'm working on that <dummy> function you suggested now, and the way I'm approaching it is I'm reading in my field list from that text file I mentioned, and listing the values out:

<a name=dummy>



<READLN "e:\mytextfile">

<!--get array like so: value1,value2,value3-->
<split "," $ret>
<loop $ret>
<if $loop eq "0">
$ret <BR><!--write out the SQL field names. Also tried <setvar> here-->
</if>
</loop>
</READLN>


</a>


What wind up doing there is writing out the actual field names like so:

TITLE
DATE
.
.

This still doesn't seem to be working in my main function, however.

I've tried using <setvar $ret "hello"> in my <dummy> function, to maybe seed the value of $TITLE and $DATE, so that they're available when the <SQL> function is called later on, but that doesn't seem to work.


I've tried using <getvar> in the SQL loop to display the actual values, but no luck:

<dummy>
<SQL "select * from mytable>

<READLN "e:\mytextfile">
. <!--do a split-->
. <!--loop thru the split, getting values like TITLE and DATE-->
$ret <BR> <!--prints out "TITLE" just fine-->
<getvar $ret>
$ret <BR> <!--no luck-->
</READLN>
</SQL>


In essence, then, what I need is a way to read in the SQL columns from a file and define the variables ahead of time, so that vortex knows about them. Then, when the SQL statement is running, I have to read those columns in again from a different section of the file (which tells me the order in which to list them on screen) and display them. If you can think of a way to do this, that would be great. Thanks again.

[John]

One way to do it is to have a dummy function as listed above with $column1 $column2 and so on in it, and then from your text file specify the fields you want, and an alias to column1, column2 etc, and use that in the SQL.
e.g. Field3 as column1, Field5 as column2 etc.

What exactly are you trying to achieve? It sounds as if you want to make a generic tool out of another generic tool?

[Mr. Bigglesworth]

Yes-- let me step back and explain what we're trying to do, and why that text file is important.

What we're trying to come up with is a way for a user to specify the order in which they want a database's columns to appear on the screen. The user (with a tool in hand) will create a text file which vortex will read in and decide which fields to display, and in what order.

The problem is that we're trying to remain backwards compatible with other code, and our SQL statement has to read "select * from..." with the * in there, so there's no way I can alias column names.

Because there are several different databases a user can choose from, each with a different field structure, I can't just do a dummy function with $TITLE $DATE and the other column names hard-coded in there. The text file is the only source we have for the column names.

Well, we also have the column names available in a SQL Server table, but vortex wouldn't be able to get to that data, thus the text file idea. Generate the text file from SQL Server and ASP, then let vortex take it from there-- that's the idea.

If you can think of any way we can create the needed variables from that text file and make them available in the <SQL> loop, that would be fantastic. Thanks again for all the help.

[Kai]

If you can't alias column names, you need to declare every possible field name somewhere in the script. Short of that, you could possibly use <adminsql> and <CAPTURE> and parse its output, but that's difficult.

If you can alias columns, then you can do the following. You can get the column names from SYSCOLUMNS:

<SQL "select NAME from SYSCOLUMNS where TBNAME=$tbname"
</SQL>

If you want them in the order they appear in the actual table, add " order by ORDINAL_POSITION" in there. You can also get their types from that table too, as a hint on how to display them.

Once you have the column names you want in the order you want them printed (either from a text file or wherever), create a SQL statement that selects those columns, but renames each to a dummy variable (in the SELECT statement). By renaming in SQL, Vortex only sees the finite set of dummy vars from SQL. These dummy variables are finite and known so that <getvar> works, and so that you're always printing the dummy variables in the same order (it's the column-to-dummy-var rename that changes). Something like this might work (I'm ignoring your first/last line skip issues for now):

<$stmt = >
<readln row $myfile>
<!-- Add this var to the SELECT list, but rename it to sqlvarN: -->
<strfmt "%s, %s sqlvar%d" $stmt $ret $loop>
<$stmt = $ret>
</readln>
<substr $stmt 2 -1> <!-- eliminate the leading ", " -->
<strfmt "select %s from $$mytable" $ret> <!-- note double-$$ -->
<$stmt = $ret>

<SQL ROW $stmt> <!-- now SELECT from $mytable -->
<strfmt "sqlvar%d" $loop> <!-- get in-order dummy var name -->
<getvar $ret> <!-- get its value -->
[$ret] <!-- display it however -->
</SQL>

So no matter what order or how many real column names are given in the text file, you're always printing $sqlvar0, $sqlvar1, $sqlvar2 etc. in that order in the SQL loop.

Then in a dummy private function, just refer to the dummy vars explicitly so Vortex knows they exist (otherwise <getvar>/<setvar> won't work):

<A NAME=dummy PRIVATE>
$sqlvar0 $sqlvar1 $sqlvar2 ...
</A>

You need as many dummy vars as the max number of columns you'll select from a table.

[Mr. Bigglesworth]

Wow-- who knew it would be this hard?

I appreciate the help guys, but I just can't use aliases in the SQL statement-- we use the actual column names all throughout our scripts, and changing all that code just isn't feasible. My whole goal here is to data-drive some of this code of ours as a holding action until we can get a new database architecture in place which will really make things smoother.

Thus, what I'm doing now is using an .asp page to physically write out every distinct column name we have in our various databases, and pasting that into the dummy function. Gnarly, but quick and easy.

My only question is: I've got 1200 such dummy variables at the moment, and it works, but does vortex have some sort of upper limit on this kind of thing before it decides it's had enough? By the time this code is ready to be gotten rid of, it's not impossible that we'll have up to 2000 such dummy variables. Thanks again.

[Kai]

There is no hard limit to the number of variables in Vortex, though you may run into practical limits at some point (size of script, time to run it, memory etc.)