1

I am writing a web app which uses SQL as input. This SQL must always be a CREATE TABLE statement, and what my app needs to do is getting the following attributes from it:

  • Field name
  • Type
  • Length (if available)
  • Binary (if available)
  • Allow NULL (if available)
  • Auto increment (if available)

Example SQL:

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

I have no idea where to start, and I'm not that good at writing such parsers. Are there any SQL parsers for JavaScript/jQuery that I can use, or any example code? Thanks.

Improve this question

3 Answers 3

Reset to default
2

This isn't a complete solution, but hopefully something to get you started.

You could check the following regex:

/^CREATE\s+TABLE\s+(\S*)\s*\((.*)\)$/

It's crude, but it should match.

You could then pull out the first matched group (assuming you need the name of the table) and then the second matched group for the field list

You can then split the field list on the comma to get the information for each field. Outside of that you could get the field name by matching the first group in /^(.)\s(.)$/ ... You'd have to get a bit more creative in parsing the second half of the statement (types, lengths, null/not null, default, identity(1,1) ) but if you can figure out some sort of pattern that would always apply, I'm sure it could be done. Note that the above is also assuming that whitespace is properly trimmed, but that is easy enough.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Parsing SQL is never that simple, hope you overcome all the issues along the way.
Correct, but it looks like he is already expecting some sort of semi-formatted input to begin with.
That's right. It's always a CREATE TABLE statement. The feature is not vital for my app but only speeds up its use.
1

there's an OpenSource project located on Google Projects called "TrimQuery", below is an example:

var selectStatement = queryLang.parseSQL("SELECT Customer.* FROM Customer");

This has great support for the general SQL Languages such as JOINS and SELECT item.*,relation.other FROM X

Hope this helps.

Improve this answer

2 Comments

Does it support CREATE TABLE (the only thing I need)? (:
Nope, it only supports 'SELECT', 'DESTROY', 'UPDATE', 'INSERT', but it was posted so that you can take a look and it would help you build your own.
0

I had a task to make a hint for the Notepad ++ editor for sql. Therefore, I made parsing of dumps of the database structure. Result of work: https://github.com/trdm/jn-npp-scripts/blob/master/includes/IntellSql.js

Use:

var vQa = new CSqlDumpAnalizer();
vQa.setText(textSql)
vQa.parse();
vQa.printTables();
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.