Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parsing query strings 1

Status
Not open for further replies.

petermeachem

Programmer
Joined
Aug 26, 2000
Messages
2,270
Location
GB
This is a bit complicated (for me anyway). I start off with a select from any old database. I want to display the fields from the select in a list box so that the user can say what each field represents. I start off with something like
SELECT a , b , c , d FROM Table1
and end up with
SELECT a AS A, b AS B, c AS c, d AS D FROM Table1
where A B C and D are the users identifiers. See what I mean?

First go was to create a recordset and read the field properties off. This is fine for simple cases but where I have
SELECT tableA.a , TableA.b , TableB.c etc, it doesn't because I just get the abcd bit. I think the properties depend on the source type which I don't know.

I can just parse it looking for SELECT "," and FROM. Is there a better way?

Actual example below.

SELECT Members.MemberID,
Members.FirstName,
Members.LastName,
Members.HomeAddress,
Members.HomeCity,
Members.HomeStateOrProvince,
Members.HomePostalCode,
Members.HomeCountry,
CommitteeMembers.CommitteePosition,
Committees.CommitteeName
FROM (Committees
INNER JOIN (CommitteeMembers
INNER JOIN Members ON
Members.MemberID = CommitteeMembers.MemberID) ON
CommitteeMembers.CommitteeID = Committees.CommitteeID)
WHERE (Committees.CommitteeName = 'meals')

I need the 'full' field name because, for instance MemberID is obviously in more than one table.
Peter Meachem
peter @ accuflight.com

 
What you could do is to open the recordset to the table. Then within the Recordset you could access the names of all of the columns by looking at the .Fields collection within the recordset.

You can do this for each table and from that build you complete set of tablename.fieldname options.

Dim TheFields(0) as String

Set lRst_ThisRecSet = New ADODB.Recordset
lRst_ThisRecSet.Open <TableName>, ADO_Conn, adOpenKeyset, adLockPessimistic, adCmdTable

With lRst_ThisRecSet
ReDim Preserve TheFields(.Fields.Count)
For Idx = 0 to .Fields.Count - 1
TheFields(Idx) = <TableName> & &quot;.&quot; & .Fields(Idx)
Next Idx
End With

Now repeat this for the next table, but remember to adjust the array offset accordingly.

If this is on the right track, I'll workup some additional code for multiple tables Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
 
Sorry Peter - forget that crap above. The code below will build a collection of all of the databases and associated columns
Code:
Dim AllFields As New Collection
Dim RecSet As New ADODB.Recordset
Dim Idx1 As Integer
Dim Idx2 As Integer
Dim TheTables(2) As String

TheTables(0) = &quot;content&quot;
TheTables(1) = &quot;images&quot;
TheTables(2) = &quot;upload_files&quot;
   
For Idx1 = 0 To UBound(TheTables)
   RecSet.Open TheTables(Idx1), gADO_NETConn, adOpenKeyset, adLockPessimistic, adCmdTable
   For Idx2 = 0 To RecSet.Fields.Count - 1
      AllFields.Add TheTables(Idx1) & &quot;.&quot; & RecSet.Fields(Idx2).Name
   Next Idx2
   RecSet.Close
Next Idx1
Now the AllFields collection will contain all of the qualified column names for each of these three tables Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
 
That would give me the table names ok, but I'd then have two messy problems
Finding the tables from the select
Identifying the selected fields from the fields in your code. My original select could be

SELECT Members.MemberID,
FirstName, etc

Just straight parsing might be easier. The field list starts after the SELECT and ends before the FROM and is separated by commas.

I was wondering if there was some cunning adox thing (or similar) that would give me the answer more easily. Peter Meachem
peter @ accuflight.com

 
You mean you want to parse out the actual SQL statement for fields that were included in the SQL statement itself. Since this code uses the Split function, you'll need VB6.

Code:
Dim WorkStr As String
Dim SelStart As Integer
Dim FromStart As Integer
Dim FldNames() As String

SelStart = InStr(UCase(SQLStmt), &quot;SELECT &quot;)
If (SelStart > 0) Then
   WorkStr = Mid(SQLStmt, SelStart + 7)
   FromStart = InStr(UCase(WorkStr), &quot; FROM &quot;)
   If (FromStart > 0) Then
      WorkStr = Left(WorkStr, FromStart - 1)
      WorkStr = Replace(WorkStr, &quot; &quot;, vbNullString)
      FldNames = Split(WorkStr, &quot;,&quot;)
   End If
End If
Now FldNames will be an array which contains all of the fields included in the select. Those entries which contain a &quot;.&quot; within are table name qualified. Those that are not you can search the colleciton build in the previous post comparing the column name portions to determine the table name. You could also continue the search to find ambiguities in unqualifed column names if any exist. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
 
That's the biz. Spot on. Thanks. I was just typing up something similar. Fields that don't have a dot don't need one or the select would not work, which I know it does.

I'm going to have to put you on the list of credits you know. Peter Meachem
peter @ accuflight.com

 
Some providers will return the source field and source table names.

So, if the provider supports it, you could still use CajunCenturion first suggestion by retrieving the

field's source table (property under DAO is called SourceTable and under ADO BASETABLENAME)

and the

source field name (property under DAO is called SourceField and under ADO BASECOLUMNNAME)

via the Field's Properties property:

TheFields(Idx) = .Fields(Idx).Properties(&quot;BASETABLENAME&quot;) & &quot;.&quot; & .Fields(Idx).Properties(&quot;BASECOLUMNNAME&quot;)
[/b][/i][/u]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top