What type of SQL does Red Query Builder generate?

Question

I have been trying to use Red Query Builder visual query builder on a project, but we are finding that the SQL output is not consistent with the SQL our database (SQLite) expects. That makes me wonder what dialect of SQL is being generated and how can I change it?

Edit:

For example, this is a generated line of SQL.

"SELECT \"x0\".\"ID\", \"x0\".\"NAME\", \"x0\".\"COUNTRYCODE\", \"x0\".\"DISTRICT\", \"x0\".\"POPULATION\" FROM \"CITY\" \"x0\" INNER JOIN \"COUNTRY\" \"x1\" ON \"x0\".\"COUNTRYCODE\" = \"x1\".\"CODE\" WHERE (\"x1\".\"NAME\" = ?)"

From a cursory inspection, the x0 seems to be the alias for the table named CITY, but where is that connection being made?

Can you post some of the generated SQL and tell us what exact error you're seeing from SQLite? The demo site seems to generate ANSI-compliant SQL, and SQLite is usually pretty good about that... — Charles, May 25 '13 at 15:00
Posted the code sample from the site. My understanding is that the aliases need to be defined with AS keyword, but this code does not use that pattern, which is what made me suspicious. — picardo, May 25 '13 at 18:14
The [SQLite `SELECT` syntax guide](http://www.sqlite.org/lang_select.html) suggests that `AS` is optional. This is also true in MySQL and Postgres, so I'm going to assume that it's standard in others as well. SQLite also follows the ANSI standards and [understands double quoted strings as identifiers](http://www.sqlite.org/lang_keywords.html). That statement, once properly unescaped, should run just fine assuming that the table and columns exist... — Charles, May 25 '13 at 18:54

score 1 · Accepted Answer · answered Jul 04 '13 at 04:17

The query runs just fine for me in SQLite after creating the proper tables. Here's the fully query unescaped and formatted:

SELECT 
    "x0"."ID", 
    "x0"."NAME", 
    "x0"."COUNTRYCODE", 
    "x0"."DISTRICT", 
    "x0"."POPULATION" 
FROM 
    "CITY" "x0" 
            INNER JOIN 
    "COUNTRY" "x1" 
            ON 
        "x0"."COUNTRYCODE" = "x1"."CODE" 
WHERE ("x1"."NAME" = ?)

Here's DDL and inserts to test:

CREATE TABLE Country (CODE text, Name Text);
CREATE TABLE CITY (ID int, Name text, countrycode text, district text, population int);

INSERT INTO Country VALUES ('US', 'United States');
INSERT INTO Country VALUES ('AU', 'Australia');

INSERT INTO City VALUES (1, 'Albany', 'US', 'NY', 1000000);
INSERT INTO City VALUES (2, 'Atlanta', 'US', 'GA', 2000000);
INSERT INTO City VALUES (3, 'Washington', 'US', 'DC', 500000);
INSERT INTO City VALUES (4, 'Melborne', 'AU', 'A', 40000);
INSERT INTO City VALUES (5, 'Sydney', 'AU', 'B', 60000);

Full example (run in console, so query is not parameterized, but ? is accepted parameterization for SQLite).

C:\Windows\System32>sqlite3
SQLite version 3.7.6.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE Country (CODE text, Name Text);
sqlite> CREATE TABLE CITY (ID int, Name text, countrycode text, district text, population int);
sqlite> INSERT INTO Country VALUES ('US', 'United States');
sqlite> INSERT INTO Country VALUES ('AU', 'Australia');
sqlite> INSERT INTO City VALUES (1, 'Albany', 'US', 'NY', 1000000);
sqlite> INSERT INTO City VALUES (2, 'Atlanta', 'US', 'GA', 2000000);
sqlite> INSERT INTO City VALUES (3, 'Washington', 'US', 'DC', 500000);
sqlite> INSERT INTO City VALUES (4, 'Melborne', 'AU', 'A', 40000);
sqlite> INSERT INTO City VALUES (5, 'Sydney', 'AU', 'B', 60000);
sqlite> SELECT
   ...>         "x0"."ID",
   ...>         "x0"."NAME",
   ...>         "x0"."COUNTRYCODE",
   ...>         "x0"."DISTRICT",
   ...>         "x0"."POPULATION"
   ...> FROM
   ...>         "CITY" "x0"
   ...>                         INNER JOIN
   ...>         "COUNTRY" "x1"
   ...>                         ON
   ...>                 "x0"."COUNTRYCODE" = "x1"."CODE"
   ...> WHERE ("x1"."NAME" = 'United States');
1|Albany|US|NY|1000000
2|Atlanta|US|GA|2000000
3|Washington|US|DC|500000
sqlite>

What type of SQL does Red Query Builder generate?

1 Answers1