Tutorial :Parsing SQL text



Question:

Does anyone know how to parse SQL Text with VB.NET?

Ex: I got a sql file "CREATE TABLE..." i want to get an array of columns and an array of data types.


Solution:1

Expanding on @Tomalak's post: once you have the table built you can use a DataReader to select just 1 line if you only need the schema or your actual data, then do something like this:

Dim myReader As DataReader  Dim myTable As DataTable    Dim myColumns As New Collection    myReader =  //' get your data    If myReader.HasRows Then      myTable.Load(myReader)      For Each col As DataColumn In myTable.Columns          myColumns.Add(col.DataType.ToString, col.ColumnName)      Next  End If  

The collection myColumns will now have a Key of the column's name and the Value is the columns datatype. You can modify this to make 2 separate collections if you need.

Parsing a string on the other hand will involve significantly more debugging and offer lots of room for error.


Solution:2

It may be the easiest approach to feed that statement to an SQL Server and actually create that table in a temp database.

After that, finding out about the table structure would be easy.

All you'd have to parse out of the statement string would be the name of the table. Even better, you could simply replace it and have a known table name from the start.

Additionally, you would get the info if the statement is even valid SQL.


Solution:3

Please check this demo to help you Analyzing DDL statement

Input SQL:

create table new_employees  (      employee_id  number primary key,      first_name  varchar2(15) null,      last_name varchar2(15) check(last_name>10),      hire_date date default sysdate,      dept_id number,      dept_name varchar2(100),      start_date timestamp(7) references scott.dept(start_date),      end_date timestamp(7)   references dept.end_date on delete cascade,      check (start_date>end_date),      constraint c_name unique(first_name,last_name),      foreign key(dept_id,dept_name) references dept(id,name)  );   

Generated output:

Table Name:new_employees  Columns:  name:employee_id  datetype:number  inline constraints:      primary key    name:first_name  datetype:varchar2(15)  null: yes    name:last_name  datetype:varchar2(15)  inline constraints:      check:last_name>10    name:hire_date  datetype:date  default:sysdate    name:dept_id  datetype:number    name:dept_name  datetype:varchar2(100)    name:start_date  datetype:timestamp(7)  inline constraints:      foreign key      referenced table:scott.dept      referenced columns:start_date    name:end_date  datetype:timestamp(7)  inline constraints:      foreign key      referenced table:dept.end_date    outline constraints:      check:start_date>end_date        constraint name:c_name      unique key      columns:first_name,last_name        foreign key      columns:dept_id,dept_name      referenced table:dept      referenced columns:id,name  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »