DBQuery - Acquire Variable Name

[JRL]

DBQuery> allows us to use an optional parameter (fieldnames) that returns the field name as part of the arrayed variable name.

Does anyone know of a method to retrieve the field name from the variable name?

[adroege]

When I run the following code

Code: Select all

//Connect to Datasource
         
Let>str=DSN=PostgreSQL30;Server=192.168.3.253;Port=5432;Database=DemoDataAD;User=MH;Password=mh;Option=3;
DBConnect>str,dbH
 
//Perform SELECT query
Let>SQL=select * from entities limit 5
DBQuery>dbh,SQL,entities,numrecs,numfields,1
 
//Close database connection
DBClose>dbH

The watch list shows variables like this:

0: ENTITIES_2_PARTY6PCT=
0: ENTITIES_2_PARTY6IDTEXT=
0: ENTITIES_2_PARTY6ID=
0: ENTITIES_2_PARTY7ID=
0: ENTITIES_2_SORTCODE=

where the part after the last underscore is indeed
the names of columns from my table.

[JRL]

Thanks for your reply.

...where the part after the last underscore is indeed
the names of columns from my table.

That is correct. My question is how can you use those names programmatically? Basically, how can you acquire variable names? In most cases the programmer assigns names to variables and therefore knows the variable names and could, if he chose, add lines that write the variable names to a file or to the screen. In the case of using DBQuery> with the fieldnames parameter set to 1, the variable names are assigned for us and are effectively arbitrary. Therefore, the names are only useful in the editor for discovery or in the program when the author knows what names are available and uses those names explicitly.

I think I have a solution to my immediate problem by using a VBScript Marcus provided called SQLToCSV. The script writes the data and a header row So I can relate the data and the field/column names.

I'd still like to know if there is any way to acquire the names of variables. "No" is an acceptable answer.

Hmmm... just had a thought. perhaps one could parse the script's log file. On the other hand, if variable names can be written to a log file, why are they not available from within the script?

[Marcus Tettmar]

It's rather uncommon to be querying data from a database when you don't know what the column names are already. It's kind of a given that to work with a database you need to know what the tables and columns are called in the first place.

But, no, aside from the ideas you've already come up with, there's no direct way of acquiring the names of variables. If there were I'm not sure how you would identify the one you want to retrieve anyway - if you have nothing already to identify it by ..... bit chicken and egg isn't it?

[JRL]

It's rather uncommon to be querying data from a database when you don't know what the column names are already. It's kind of a given that to work with a database you need to know what the tables and columns are called in the first place... ... bit chicken and egg isn't it?

Exactly!!! That's why I'm attempting to create the egg machine.

Our ERP software has close to 900 tables with a total of 30,000 different fields. When I want to query a database to acquire a specific piece of information the hunt to locate the data location(s) can take hours.

Consequently I'm looking to make an egg machine... or is that a chicken machine? I want to feed my machine specific data then let it spit out the table and field names where said data was discovered. Hopefully, narrowing my search immensely. This requires that I acquire the field names, thus the request.

You might ask (I have) why is this data not provided? They provide a list of tables and a list of fields in each table. They also provide a tool that can look at all of the data in the database and via SQL, narrow the data visible in an excel like grid. They do not relate data presented or entered on dialogs via their software to the appropriate table(s)/field(s) location(s). Proprietary information? They enjoy the phone calls? I dunno. I'm hoping to present the script I've created to them and we can work cooperatively toward making it or something like it work.

Anyway, thanks for your response.

[jpuziano]

Hi JRL,

Here's some ideas... Since the Watch List shows us the table names (as part of the variable names created), why not see if you can scrape that text?

Another tactic and probably easier, would be to make use of a log file with a macro where you:

- dump all variables to the log file
- run the SQL that searches for your data... so now new variables would have been created
- dump all variables to the log file a second time
- now analyse the diff between dumps and you have your new variable names
- parse the variable names to get the names of the tables

Have fun and let us know if you find a method that works...

[obfusc88]

Some of your variables may be created in, or the result of queries. Can you use SQL commands to get a list of all stored queries, then parse the queries to get the names of those variables?

[Marcus Tettmar]

Some DBs also give you SQL commands to get a list of the tables and their column names etc. E.g. SHOW TABLES and SHOW COLUMNS in MySQL:

http://dev.mysql.com/doc/refman/5.5/en/show-tables.html

If yours has this capability you could probably devise a routine which, once you've found the data and you know the numeric index of the column it is in, you can query the columns, and knowing the index, get it's name using SHOW COLUMNS. Just a thought which may give you some ideas.

[JRL]

Thanks to all for your ideas.

I think I have a solution to my immediate problem by using a VBScript Marcus provided called SQLToCSV. The script writes the data and a header row So I can relate the data and the field/column names.

Here's a sample of what I chose to do. Note comments.

Code: Select all

GoSub>VBScript

DeleteFile>%Temp_Dir%FruitData.csv

//Next two lines substitute for the SQLToCSV VBScript as a demo for the forum
LabelToVar>Fruit,data
WriteLn>%Temp_Dir%FruitData.csv,wres,data

//Next commented lines are the method I'm really using to acquire my data
//Using the SQLToCSV VBScript (provided by Marcus) produces a CSV file that
//contains the requested data with column headers.
/*
Let>con=Your Connection String - See ConnectionStrings.com
Let>qry=select * from ProduceTable
Let>file=%temp_dir%FruitData.csv

VBRun>SQLToCSV,con,qry,file
*/

//The next three lines get the header from the file created by SQLToCSV.
//The header line is separated by commas and each header is saved as 
//an arrayed variable.
Let>Comma=,
ReadLn>%Temp_Dir%FruitData.csv,1,Header
Separate>Header,%Comma%,Header


//The next five lines get the data from the file created by SQLToCSV.
//And place the data into arrayed variables that are in sequence
//with the header arrayed variables.
Let>ConStr=Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=%temp_dir%;Extensions=asc,csv,tab,txt;
DBConnect>ConStr,dbH
Let>sql=Select * from FruitData.csv
DBQuery>dbH,sql,var,Numrec,Numfld
DBClose>dbH

//Since the variable arrays are synchronised we can display data and its header
//without the need to know the header names prior to starting the process.
//Or in the case of my need,  I would know the name of the column for finding future data.
Let>kk=0
Repeat>kk
  Add>kk,1
  Let>value=var_%kk%_2
  Mdl># %kk% %Header_1% %Header_2% = %value%
Until>kk=Header_Count

//Fake Data for sample
/*
Fruit:
Fruit,Color,Shape,Opinion
Apple,Red,Sphere,Like
Apple,Green,Sphere,Like
Plum,Purple,Sphere,Sour
Banana,Yellow,Cylinder,Like
*/

SRT>VBScript
VBSTART
  Sub SQLToCSV(connection_string,sql_string,output_file)
    Dim outFile, objConnection, objRecordSet, strSQL, intCount
    set outFile = CreateObject("Scripting.FileSystemObject").CreateTextFile(output_file)
    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.open connection_string
    Set objRecordSet = objConnection.Execute(sql_string)
    for intcount = 0 to objRecordSet.fields.count -1
        if intcount <> objRecordSet.fields.count-1 then
            outFile.write """" & objRecordSet.fields(intcount).name & ""","
        else
            outFile.write """" & objRecordSet.fields(intcount).name & """"
        end if
    next
    outFile.writeline ""
    do while not objRecordSet.eof
        for intcount = 0 to objRecordSet.fields.count - 1
            if intcount <> objRecordSet.fields.count - 1 then
                outFile.write """" & objRecordSet.fields(intcount).value & ""","
            else
                outFile.write """" & objRecordSet.fields(intcount).value & """"
            end if
        next
        outFile.writeline ""
        objRecordSet.movenext
    loop
    objRecordSet.Close
    set objRecordSet = Nothing
    objConnection.Close
    set objConnection = Nothing
    outFile.Close
    set outFile = Nothing
  End Sub
VBEND
END>VBScript

[adroege]

Here is a more simple solution for you which doesn't involve reading and parsing files. The VBScript does it all. Probably could change the field separator to a pipe or something and then let Macro Scheduler create an array from the result (if needed.)

Code: Select all

VBSTART
Function DBAccess (connection_string,sql)

  Dim result, set1, rowcount, rc, row1
  set db = CreateObject("ADODB.Connection")

  db.Open connection_string
  set rs = CreateObject("ADODB.Recordset")

  rs.CursorType = 3
  rs.CursorLocation = 3

  rs.Open sql,db,3,3

  set1 = ""
  rowcount = 0

' if the result set was created, continue
' (without this test, rs.EOF returns an error)
  if (rs.State = 1) Then
     if rs.EOF then
       'result = MsgBox ("Your query returned no results", 65, "Warning")
     else
       rc = rs.RecordCount
       do until rs.EOF
          rowcount = rowcount + 1
          row1 = ""
          for i = 0 To rs.Fields.Count - 1
            row1 = row1 & rs.Fields(i).Name & "=" & trim(rs.Fields(i).Value)
            if i < rs.Fields.Count - 1 then
               row1 = row1 & ","
            end If
          next
          set1 = set1 & row1
          if rowcount < rc then
            set1 = set1 & vbCrLf
          end if
          rs.MoveNext
       loop
     end if
  end if

  DBAccess = set1
  set db=nothing
  set rs=nothing
End Function

VBEND

Let>connection_string=Provider=Advantage OLE DB Provider;Data source=J:\Advan\Apps\Adcpp2\PerfPrac\Dba;ServerType=ADS_LOCAL_SERVER|ADS_REMOTE_SERVER;TableType=ADS_ADT
Let>sql=select top 5 * from glink;
VBEval>DBAccess("%connection_string%","%sql%"),result
MessageModal>result

[JRL]

Cool!

Changed the "=" in the vbscript to a "~#~" and the "," to "~|~". Also changed the "top 5" in the sql string to "top 1". A couple of separates and I end up with a list of header names.

Though its not necessary, I have it writing out to a text file just for verification purposes.

Thanks,
I like it!

Code: Select all

VBSTART
Function DBAccess (connection_string,sql)

  Dim result, set1, rowcount, rc, row1
  set db = CreateObject("ADODB.Connection")

  db.Open connection_string
  set rs = CreateObject("ADODB.Recordset")

  rs.CursorType = 3
  rs.CursorLocation = 3

  rs.Open sql,db,3,3

  set1 = ""
  rowcount = 0

' if the result set was created, continue
' (without this test, rs.EOF returns an error)
  if (rs.State = 1) Then
     if rs.EOF then
       'result = MsgBox ("Your query returned no results", 65, "Warning")
     else
       rc = rs.RecordCount
       do until rs.EOF
          rowcount = rowcount + 1
          row1 = ""
          for i = 0 To rs.Fields.Count - 1
            row1 = row1 & rs.Fields(i).Name & "~#~" & trim(rs.Fields(i).Value)
            if i < rs.Fields.Count - 1 then
               row1 = row1 & "~|~"
            end If
          next
          set1 = set1 & row1
          if rowcount < rc then
            set1 = set1 & vbCrLf
          end if
          rs.MoveNext
       loop
     end if
  end if

  DBAccess = set1
  set db=nothing
  set rs=nothing
End Function

VBEND


Let>connection_string=Provider=Advantage OLE DB Provider;Data source=J:\Advan\Apps\Adcpp2\PerfPrac\Dba;ServerType=ADS_LOCAL_SERVER|ADS_REMOTE_SERVER;TableType=ADS_ADT
Let>sql=select top 1 * from glink;
VBEval>DBAccess("%connection_string%","%sql%"),result
MessageModal>result

Separate>result,~|~,item
Let>kk=0
Repeat>kk
  Add>kk,1
  Let>value=item_%kk%
  Separate>value,~#~,value
  Let>header_%kk%=value_1
  WriteLn>%temp_dir%adroege.txt,wres,value_1
Until>kk=item_Count