parse parameters from sql statement

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

Is there some generic way to parse the parameters from a sql statement?

If I start with a sql in this format:

Insert into myTable
(field1, field2, field3)
values
(@field1, @field2, @field3)

I want to pull out all the words that start with "@".

Right now I am stepping through the whole statement char by char and testing
for char = "@".

It seems like there should be some clever code to do this.

Rick
 
Rick said:
Is there some generic way to parse the parameters from a sql statement?

If I start with a sql in this format:

Insert into myTable
(field1, field2, field3)
values
(@field1, @field2, @field3)

I want to pull out all the words that start with "@".

Right now I am stepping through the whole statement char by char and testing
for char = "@".

It seems like there should be some clever code to do this.

Rick
Click to expand...

Lookup the Replace function in Help.
 
Is there some generic way to parse the parameters from a sql statement?

If I start with a sql in this format:

Insert into myTable
(field1, field2, field3)
values
(@field1, @field2, @field3)

I want to pull out all the words that start with "@".

Right now I am stepping through the whole statement char by char and testing
for char = "@".

It seems like there should be some clever code to do this.
Click to expand...

This is very difficult to do correctly for any valid SQL statement. If
you know that there are no @ characters in text strings or in
comments, then the easiest way would probably be to search for each
occurence of @. To find the end of each parameter you could search
starting at each @ to look for all possible delimiters (comma, space,
right paren, etc.)
 
Oops! Boy did I blow it.

Do what Jack said.

If you know for a fact that the fields would be on one line...
(@field1, @field2, @field3)
Then a simple seek to find the line and then replace "(" and ", ", followed
by a Split and Trim. But I can't see that as any more helpful than just
drilling down char by char.

A Regular Expression could likely be built, and might look simpler, but
would likely be slower than char by char.

Do what Jack said. <g>

-ralph
 
Thanks everyone.

I ended up with string.Replace for "(", ")", crlf and "," and then split
into an array and checked each value to see if it begins with "@". I am in
charge of the sql statements, so I know the format in advance.

I'm sure the reged would be a more elegant solution, however I don't want to
spend a day researching the format.

Rick
 
For what it's worth here's the implementation I used for Oracle
parameterised queries

/// <summary>
/// Parses a SQL statement and returns a dictionary with keys
matching the name of each parameter
/// </summary>
///<remarks>CodeDom generated</remarks>
[System.Diagnostics.DebuggerNonUserCodeAttribute()]
protected virtual System.Collections.Generic.List<string>
ParameterisedSqlParser(string sql)
{
System.Collections.Generic.List<string> parameterNamesList = new
System.Collections.Generic.List<string>();
char[] splitCharacters = " =,()".ToCharArray();
string[] tokenisedSqlString = sql.Split(splitCharacters);
for (int i = 0; (i < tokenisedSqlString.Length); i++)
{
if ((!tokenisedSqlString.Contains( "'" ) &&
tokenisedSqlString.Contains( ":" )))
{
parameterNamesList.Add(tokenisedSqlString.TrimStart(':'));
}
}
return parameterNamesList;
}
 
Thanks Glenn,

I forgot about the ToCharArray method which makes my solution a bit simpler.

Rick

Glenn said:
For what it's worth here's the implementation I used for Oracle
parameterised queries

/// <summary>
/// Parses a SQL statement and returns a dictionary with keys
matching the name of each parameter
/// </summary>
///<remarks>CodeDom generated</remarks>
[System.Diagnostics.DebuggerNonUserCodeAttribute()]
protected virtual System.Collections.Generic.List<string>
ParameterisedSqlParser(string sql)
{
System.Collections.Generic.List<string> parameterNamesList =
new System.Collections.Generic.List<string>();
char[] splitCharacters = " =,()".ToCharArray();
string[] tokenisedSqlString = sql.Split(splitCharacters);
for (int i = 0; (i < tokenisedSqlString.Length); i++)
{
if ((!tokenisedSqlString.Contains( "'" ) &&
tokenisedSqlString.Contains( ":" )))
{

parameterNamesList.Add(tokenisedSqlString.TrimStart(':'));
}
}
return parameterNamesList;
}
Rick said:
Is there some generic way to parse the parameters from a sql statement?

If I start with a sql in this format:

Insert into myTable
(field1, field2, field3)
values
(@field1, @field2, @field3)

I want to pull out all the words that start with "@".

Right now I am stepping through the whole statement char by char and
testing for char = "@".

It seems like there should be some clever code to do this.

Rick
Click to expand...
Click to expand...
 
Hi
Looking to do same, but have found a SQL parser component.
Just search web for General SQL parser.

I wonder how the Query Builder in VS2005 dataset designer does it when you
add a SELECT query with parameters

Rgds
John
 
Back
Top