parse parameters from sql statement

[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

 

[Ralph]

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

Lookup the Replace function in Help.

 

[rajesh nandwani]

Dear Rick
Check regular expressions REGEX class
rajesh

 

[Jack Jackson]

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.

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.)

 

[Ralph]

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

 

[Rick]

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

 

[Glenn]

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]

Thanks Glenn,

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

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;
}

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

 

[Guest]

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