Parsing columns from a sql statement

Hi, How I could get the name of the columns from a string
variable that has a sql statement?

"select this, that, (to_date('02-02-04','mm-dd-yy')) from
table"

I want to obtain the column names "this", "that" and
(to_date('02-02-04','mm-dd-yy'))

Thank you
0
Joseph
5/21/2004 3:48:42 PM
sybase.powerbuilder.general 62418 articles. 17 followers. Follow

10 Replies
498 Views

Similar Articles

[PageSpeed] 29

I would suggest to use the POS and MID functions.

Joseph wrote:

> Hi, How I could get the name of the columns from a string
> variable that has a sql statement?
>
> "select this, that, (to_date('02-02-04','mm-dd-yy')) from
> table"
>
> I want to obtain the column names "this", "that" and
> (to_date('02-02-04','mm-dd-yy'))
>
> Thank you

0
Anthony
5/21/2004 4:11:55 PM
or if you don't want to do string parsing, do a
SyntaxFromSql, then create a datastore with a dataobject
from that syntax, then do a describe on column name :-)

> I would suggest to use the POS and MID functions.
>
> Joseph wrote:
>
> > Hi, How I could get the name of the columns from a
> > string variable that has a sql statement?
> >
> > "select this, that, (to_date('02-02-04','mm-dd-yy'))
> > from table"
> >
> > I want to obtain the column names "this", "that" and
> > (to_date('02-02-04','mm-dd-yy'))
> >
> > Thank you
>
0
Philip
5/21/2004 4:43:27 PM
Obviously, it's harder than the average parsing problem, because you
can't parse on every comma. What I did was to create of function that
determined of_PosNotParened(), which found the target, then made sure
that for every open paren before the target that it's matching close
paren was not after the target. If you can assume good syntax, once
you've found a matching close paren you don't have to check any other
open parens before it. Once you've got this function, a lot of the
algorithms you can get from PFC's SQL service work much better (e.g.
handle subqueries correctly). It's a challenging problem. (And, no, my
code isn't ready for sharing, but getting it there is on that
infinitely scrollable list of things to do. <g>)

Good luck,

Terry [TeamSybase] and Sequel the techno-kitten

On 21 May 2004 08:48:42 -0700, Joseph wrote:

>Hi, How I could get the name of the columns from a string
>variable that has a sql statement?
>
>"select this, that, (to_date('02-02-04','mm-dd-yy')) from
>table"
>
>I want to obtain the column names "this", "that" and
>(to_date('02-02-04','mm-dd-yy'))

Sequel's Sandbox: http://www.techno-kitten.com
Home of PBL Peeper, a free PowerBuilder Developer's Toolkit. 
Version 2.2.06 now available at the Sandbox
See the PB Troubleshooting Guide at the Sandbox
^ ^
o o
=*=
0
Terry
5/21/2004 5:42:18 PM
I think OP is looking for the column.DBName property. Column.Name may be
prefixed with the table name if the select refers to more than one table.

<Philip Salgannik> wrote in message
news:40ae31c5.3f45.1681692777@sybase.com...
> or if you don't want to do string parsing, do a
> SyntaxFromSql, then create a datastore with a dataobject
> from that syntax, then do a describe on column name :-)
>
> > I would suggest to use the POS and MID functions.
> >
> > Joseph wrote:
> >
> > > Hi, How I could get the name of the columns from a
> > > string variable that has a sql statement?
> > >
> > > "select this, that, (to_date('02-02-04','mm-dd-yy'))
> > > from table"
> > >
> > > I want to obtain the column names "this", "that" and
> > > (to_date('02-02-04','mm-dd-yy'))
> > >
> > > Thank you
> >


0
Jerry
5/21/2004 5:50:15 PM
It's all is going to be there (and he only mentioned one
table anyway :-) )

> I think OP is looking for the column.DBName property.
> Column.Name may be prefixed with the table name if the
> select refers to more than one table.
>
> <Philip Salgannik> wrote in message
> news:40ae31c5.3f45.1681692777@sybase.com...
> > or if you don't want to do string parsing, do a
> > SyntaxFromSql, then create a datastore with a dataobject
> > from that syntax, then do a describe on column name :-)
> >
> > > I would suggest to use the POS and MID functions.
> > >
> > > Joseph wrote:
> > >
> > > > Hi, How I could get the name of the columns from a
> > > > string variable that has a sql statement?
> > > >
> > > > "select this, that, (to_date('02-02-04','mm-dd-yy'))
> > > > from table"
> > > >
> > > > I want to obtain the column names "this", "that" and
> > > > (to_date('02-02-04','mm-dd-yy'))
> > > >
> > > > Thank you
> > >
>
>
0
Philip
5/21/2004 5:57:14 PM
But "(to_date('02-02-04','mm-dd-yy'))" isn't going to be in either one.
Parsing's the only way if he needs to get a computed field.

Ken

<Philip Salgannik> wrote in message
news:40ae4310.409b.1681692777@sybase.com...
> It's all is going to be there (and he only mentioned one
> table anyway :-) )
>
> > I think OP is looking for the column.DBName property.
> > Column.Name may be prefixed with the table name if the
> > select refers to more than one table.
> >
> > <Philip Salgannik> wrote in message
> > news:40ae31c5.3f45.1681692777@sybase.com...
> > > or if you don't want to do string parsing, do a
> > > SyntaxFromSql, then create a datastore with a dataobject
> > > from that syntax, then do a describe on column name :-)
> > >
> > > > I would suggest to use the POS and MID functions.
> > > >
> > > > Joseph wrote:
> > > >
> > > > > Hi, How I could get the name of the columns from a
> > > > > string variable that has a sql statement?
> > > > >
> > > > > "select this, that, (to_date('02-02-04','mm-dd-yy'))
> > > > > from table"
> > > > >
> > > > > I want to obtain the column names "this", "that" and
> > > > > (to_date('02-02-04','mm-dd-yy'))
> > > > >
> > > > > Thank you
> > > >
> >
> >


0
Ken
5/21/2004 6:09:38 PM
<pedantry>computed column</pedantry>
True. And let's not forget embedded views [Oracle 8+] and aliases. But maybe
the set of comma delimited tokens between "select" [+ white space] and
[white space +] "from" is all OP needs, which makes it a very basic exercise
in parsing.

"Ken Balakrishnan" <kNOeSPAMn@cps92.com> wrote in message
news:40ae45e2$1@forums-1-dub...
> But "(to_date('02-02-04','mm-dd-yy'))" isn't going to be in either one.
> Parsing's the only way if he needs to get a computed field.
>
> Ken
>
> <Philip Salgannik> wrote in message
> news:40ae4310.409b.1681692777@sybase.com...
> > It's all is going to be there (and he only mentioned one
> > table anyway :-) )
> >
> > > I think OP is looking for the column.DBName property.
> > > Column.Name may be prefixed with the table name if the
> > > select refers to more than one table.
> > >
> > > <Philip Salgannik> wrote in message
> > > news:40ae31c5.3f45.1681692777@sybase.com...
> > > > or if you don't want to do string parsing, do a
> > > > SyntaxFromSql, then create a datastore with a dataobject
> > > > from that syntax, then do a describe on column name :-)
> > > >
> > > > > I would suggest to use the POS and MID functions.
> > > > >
> > > > > Joseph wrote:
> > > > >
> > > > > > Hi, How I could get the name of the columns from a
> > > > > > string variable that has a sql statement?
> > > > > >
> > > > > > "select this, that, (to_date('02-02-04','mm-dd-yy'))
> > > > > > from table"
> > > > > >
> > > > > > I want to obtain the column names "this", "that" and
> > > > > > (to_date('02-02-04','mm-dd-yy'))
> > > > > >
> > > > > > Thank you
> > > > >
> > >
> > >
>
>


0
Jerry
5/21/2004 6:55:39 PM
"Jerry Siegel" <jerrys@data-sci.com.nospam> wrote in message news:40ae50c1$1@forums-2-dub...
> <pedantry>computed column</pedantry>
> True. And let's not forget embedded views [Oracle 8+] and aliases. But maybe
> the set of comma delimited tokens between "select" [+ white space] and
> [white space +] "from" is all OP needs, which makes it a very basic exercise
> in parsing.
>

Very basic??

Select
  (Select foo from bar
   where foo = (Select yin from yang )) as col1
from
  foobar
    LEFT OUTER JOIN
      (Select bar from foo ) as barfoo
      on barfoo.bar = col1
where
  foobar.pk = (Select yang from yin ) ;

Granted, a completely useless query but semantically correct...  Writing an SQL parser is hardly "very basic".

-- 
Paul Horan[TeamSybase]


0
Paul
5/21/2004 7:27:30 PM
You are right, but OP did say the code for a computed column was what he
wanted.

"Paul Horan[TeamSybase]" <paulhATvcisolutionsDOTcom> wrote in message
news:40ae5822$1@forums-1-dub...
> "Jerry Siegel" <jerrys@data-sci.com.nospam> wrote in message
news:40ae50c1$1@forums-2-dub...
> > <pedantry>computed column</pedantry>
> > True. And let's not forget embedded views [Oracle 8+] and aliases. But
maybe
> > the set of comma delimited tokens between "select" [+ white space] and
> > [white space +] "from" is all OP needs, which makes it a very basic
exercise
> > in parsing.
> >
>
> Very basic??
>
> Select
>   (Select foo from bar
>    where foo = (Select yin from yang )) as col1
> from
>   foobar
>     LEFT OUTER JOIN
>       (Select bar from foo ) as barfoo
>       on barfoo.bar = col1
> where
>   foobar.pk = (Select yang from yin ) ;
>
> Granted, a completely useless query but semantically correct...  Writing
an SQL parser is hardly "very basic".
>
> -- 
> Paul Horan[TeamSybase]
>
>


0
Jerry
5/21/2004 9:05:52 PM
The commas within the column definition (e.g. between function
arguments) will cause false results (in his example, four columns,
including >(to_date('02-02-04'< and >'mm-dd-yy'))<, neither of which
mean much of anything. <g>). Work around in another message of mine,
elsewhere in the thread.

Good luck,

Terry [TeamSybase] and Sequel the techno-kitten

On 21 May 2004 11:55:39 -0700, "Jerry Siegel"
<jerrys@data-sci.com.nospam> wrote:

><pedantry>computed column</pedantry>
>True. And let's not forget embedded views [Oracle 8+] and aliases. But maybe
>the set of comma delimited tokens between "select" [+ white space] and
>[white space +] "from" is all OP needs, which makes it a very basic exercise
>in parsing.
>
>"Ken Balakrishnan" <kNOeSPAMn@cps92.com> wrote in message
>news:40ae45e2$1@forums-1-dub...
>> But "(to_date('02-02-04','mm-dd-yy'))" isn't going to be in either one.
>> Parsing's the only way if he needs to get a computed field.
>>
>> Ken
>>
>> <Philip Salgannik> wrote in message
>> news:40ae4310.409b.1681692777@sybase.com...
>> > It's all is going to be there (and he only mentioned one
>> > table anyway :-) )
>> >
>> > > I think OP is looking for the column.DBName property.
>> > > Column.Name may be prefixed with the table name if the
>> > > select refers to more than one table.
>> > >
>> > > <Philip Salgannik> wrote in message
>> > > news:40ae31c5.3f45.1681692777@sybase.com...
>> > > > or if you don't want to do string parsing, do a
>> > > > SyntaxFromSql, then create a datastore with a dataobject
>> > > > from that syntax, then do a describe on column name :-)
>> > > >
>> > > > > I would suggest to use the POS and MID functions.
>> > > > >
>> > > > > Joseph wrote:
>> > > > >
>> > > > > > Hi, How I could get the name of the columns from a
>> > > > > > string variable that has a sql statement?
>> > > > > >
>> > > > > > "select this, that, (to_date('02-02-04','mm-dd-yy'))
>> > > > > > from table"
>> > > > > >
>> > > > > > I want to obtain the column names "this", "that" and
>> > > > > > (to_date('02-02-04','mm-dd-yy'))

Sequel's Sandbox: http://www.techno-kitten.com
Home of PBL Peeper, a free PowerBuilder Developer's Toolkit. 
Version 2.2.06 now available at the Sandbox
See the PB Troubleshooting Guide at the Sandbox
^ ^
o o
=*=
0
Terry
5/21/2004 9:22:20 PM
Reply:

Similar Artilces:

RE: SQL::Statement cannot parse valid Postgresql statement
> From: Terrence Brannon [mailto:metaperl@mac.com] > > On Monday, December 10, 2001, at 09:16 AM, Jeff Zucker wrote: > > > Terrence Brannon wrote: > >> > >> The following SQL is not parseable by SQL::Statement: > >> > >> INSERT > >> INTO thot_log (thot_fk,thot_type_fk,thot_temporality_fk,date) > >> VALUES (?,?,?,CURRENT_TIMESTAMP) > > What I have is a program which reads a SQL statement from a file. > It parses it with SQL::Statement, then it prepares this > statement, then does an ...

Reuse derived columns in same SQL Select statement under SQL Server 2005
Hello, Our agency is in the process of migrating our Sybase Adaptive Server Anywhere 7.0 databases to Microsoft SQL Server 2005. We are currently using PowerBuilder 10.5. We have several datawindows that reuse SQL computed fields (derived columns) in the same SQL Select statement. Here is an example: select institution_code, incident_number, sequence, final_review_date, ts_status_code, idoc_number, (CASE WHEN coalesce(s.recommended_verbal,'N') <> coalesce(s.final_verbal,'N') then 1 else 0 end) as c_verbal, (CASE WHEN coalesce(s.recomme...

parsing SQL statement
Anyone having suggestion parsing SQL statement? It should able to parse: BLA BALA BALA... VALUES( 'abcd efg',,999, 'some \"STRING\" and \'STR2\' STR3',,,, 'abcd, def, fghi' ) I'm using tr and then split by "," but it will fall when seeing comma inside the single quote. Thanks. --budhi On Fri, 2008-05-30 at 19:24 +0700, beast wrote: > Anyone having suggestion parsing SQL statement? > > It should able to parse: > > BLA BALA BALA... > > VALUES( > 'abcd efg',,999, 'so...

General SQL statement?
Once I have a table in SQLAnywhere 5.5.04, is there any way to generate a SQL statement that would have created it? Someone else needs to recreate the structure of my table and I'd like to send them a SQL statement that they could use to do so. One way to do it is to (for example, you need foo_table TABLE): 1) unload database (can be done from Sybase Central) into foo.sql 2) copy "CREATE TABLE foo_table" command where you need it... Petar Rick Charnes wrote: > > Once I have a table in SQLAnywhere 5.5.04, is there any way to generate > a SQL stateme...

sql statement parsing
hi guys, how can i parse the where clause in an SQL::Statement object instance? $statement = SQL::Statement->new("select 1,2,3 from table where x=y"); now when i do this: $where = $stmt->where(); it gives me error: Can't locate object method "where" via package "SQL::Statement" (perhaps you for got to load "SQL::Statement"?) at blah line 34. any idea? thanks -- Hytham Shehab At 07:34 PM 8/10/02 +0300, Hytham Shehab wrote: > how can i parse the where clause in an SQL::Statement object instance? >$statement = S...

Parsing SQL Statements
Has anyone run across any code or utilities for getting information from complex SQL statements? I want to be able to collect the table names and column names from SQL statements with complex sub-queries. Any help or pointers would be appreciated. joe white joe@lemma.net Joe -- Check out n_cst_sql in the PFC. It has a function that will break up a sql command into individual components (select,from,where,order,...) and another that will recombine the components into a single command. One caveat about this. When I used the 5.0.3 version, it had problems where nested sele...

Inserting BLOB Type column data with other columns data(normal Text)
In below code, SQL Statement is used to Insert BLOB Type data using Serialization. It is required to insert other (normal text) with BLOB Type data as well. Like to get below SQL statement amended so that all the columns(ID,name, blob) can be inserted. Just now below code working fine Inserting BLOB Type data. I think it is not stright forward way to insert all the columns(normal text) with BLOB?  1 protected void button2_Click(object sender, EventArgs e) 2 { 3 //Connection string 4 string oradb = datasource= mysource;userid="xyz" password="passwo...

SQL statement and If statement
I've problem, it's in sqlcommand it's in "where" condition, which if it's exist in sql the "ELSE" not work, and if the condition not in sqlcommand the "ELSE" appear, could someone help  it's in selectcommand.CommandText = "SELECT Weekno FROM Weeks where Weekno='" + Weekno.Text + "'" if i delete the condition the " ELSE " appear. otherwise it's not appear whole code Dim myconnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\football.mdf;Integrated S...

wrong parsing SQL statements
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C2A5ED.5D254130 Content-Type: text/plain; charset="big5" Content-Transfer-Encoding: quoted-printable I have a complex SQL Statements like this: SELECT pms_list_temp.lot_no, pms_list_temp.wo_no, =20 pms_list_temp.seq, =20 pms_list_temp.ord_seq2, pms_list_temp.m_itemno, pms_list_temp.delivery_name,=20 pms_list_temp.dep_no, pms_list_temp.wo_qty, pms_list_temp.dship, pms_list_temp.work_center, =20 pms_list_temp.work_group, pms_list_temp.catch_date, pms_l...

Showing parsed SQL statement?
In classic ASP, I used to be able to do a response.write(SQL) to view a parsed SQL statement. Can I do this in .NET? I'm using a SQLDataSource container and want to see what I'm passing through in my INSERT statement. All I can seem to get are @parameters.Thanks!Stace Add OnInserting="SqlDataSource1_Inserting"  to your SqlDataSource and check the InsertCommand and parameter values as shown below. Another way is to use SQL Profiler from SSMS (SQL Server Management Studio) to trace what has been passed to SQL Server.  protected void SqlDataSource1_Inserting(objec...

SQL statement of autoincremental column
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type"> <title></title> </head> <body bgcolor="#ffffff" text="#000000"> Hi<br> We use PowerDesigner version 9.5.2.873.&nbsp; We have a&nbsp; physical model where many tables have an incremental data type assigned to the primary key. The physical model was generated for an IBM DB2 UDB ver 7 database. We do not want that the SQL st...

PARSE SQL STATEMENT (Changing Where part)
Dear All, I wonder if there is a way to change the Where statement of a Sql statement withouth writing a custom code? For instance I need to change the "select * from employees where serial>100 order by serial" to "select * from employees where EmploymentDate is not null order by serial" Thanks a lot Aref K. Hi, Have that portion of "where ...." in a variable and then you can concatenate it to the query string. Like as follows:- Dim strWhrCon as string // then you can assign the where condition to this variable. strsql =...

Parsing a textbox for use in a SQL statement
How can I take the value of a textbox and parse out the indivdual words to use in a WHERE statement without having to result to having the user placing a special charater in between word?  I'm pulling the text out and doing the data pull in my code behind VB.net page. Thanks, Ty  you can use the string.split method and specify your own delimiter. http://msdn.microsoft.com/en-us/library/system.string.split.aspx or you can use the string.replace method and replace spaces with whatever character you want to delimit the words in the textbox Do you mean something like, the us...

Parsing complex datawindow SQL statements
Hello, I need to modify the where clause of a datawindow SQL expression at runtime. This cannot be done by using the using retrieval arguments, so I need to modify the TABLE.SELECT of the datawindow. In PFC there is a user object that does this, but it doens't handle the nested queries in the SELECT or WHERE clause. I have been looking around on the internet for hints that point me in the right direction, but I haven't found anything yet. Dipesh Panchal has posted a reply back in december 98 stating that he has done this, but there is no sample code nor details about the way...

Web resources about - Parsing columns from a sql statement - sybase.powerbuilder.general

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from June 2006 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

Police Statement Ansell
ABC Home Open Sites menu ABC Home News iview TV Radio Kids Shop More Search Australia Weather News Home Just In Australia National Australian ...

Peyton Manning Issues Statement Denying Doping Allegations - Bleacher Report
Denver Broncos quarterback Peyton Manning has issued a statement strongly denying that he received human growth hormone from an anti-aging clinic ...

Charlie Sly recants statements about Peyton Manning - Business Insider Deutschland
Peyton Manning and other pro athletes have been named in an explosive Al Jazeera investigation into doping in pro sports.

Chicago police statement on double fatal police shooting - Chicago Tribune
Chicago police emailed the following statement to the media at about 9:45 the morning of Dec. 26, 2015, after an early morning police-involved ...

Super Mario Bros World 1-1 Gets Remade to Make Bold Statement About Accessibility
To be honest, I’m not totally sure what to think about this little video , but its heart seems to be in the right place. The creators of the ...

China firm loses 5 years of financial statements
China Animal Healthcare has lost five years of financial statements midway through a forensic audit ordered by regulators.

Does Draymond feel Warriors have chance to make statement vs Cavs?
... Giants A's Sharks Warriors Kings 49ers Raiders Quakes Insiders More Tickets Shop Watch Does Draymond feel Warriors have chance to make statement ...

UK Gov’t Issues Statement On Possibly Banning Trump From Britain
UK Gov’t Issues Statement On Possibly Banning Trump From Britain

Resources last updated: 1/5/2016 4:21:46 PM