Generation of SQL Queries via C-language

Other Tech Topics
1

I have been working with SQL queries for a while. Usually through SQLite connection to the database and creating a database can be done by the following code:
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
int i;
for(i=0; i<argc; i++)
{
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}

int main(int argc, char* argv[])
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;

/* Open database */
rc = sqlite3_open("test.db", &db);
if( rc )
{
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
exit(0);
}
else
{
fprintf(stdout, "Opened database successfully\n");
}

/* Create SQL statement */
sql = "CREATE TABLE COMPANY("
"ID INT PRIMARY KEY NOT NULL,"
"NAME TEXT NOT NULL,"
"AGE INT NOT NULL,"
"ADDRESS CHAR(50),"
"SALARY REAL );";

/* Execute SQL statement */
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
if( rc != SQLITE_OK )
{
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
else
{
fprintf(stdout, "Table created successfully\n");
}
sqlite3_close(db);
return 0;
}

Without using SQLite, how can we create queries using pure C/C++?

2

In C++ .Net, you can use LINQ to create queries without writing any SQL.

3

Thanks for suggesting, can I have an example program..

4

google is your friend! I found this on the first try:

C#, LINQ and SQL

1 Like
6

Thank you sir for suggesting. I am doing my final year project to develop a tool for writing SQL queries in a language other than English. Can you please suggest, how to get with the CODING in C/C++ for generating queries in that particular language?

7

Wait. As far as I know SQL Server only accepts T-SQL for queries which is indeed in English. So even if you generated a query in Dutch, e.g.

KIES MijnGeld VAN MijnDB WAAR MijnDatum >= '1 Mei 2015';

SQL Server could not run it. I supposed there may be some RDBMS that can, but I'm not aware of any.

8

@gbritton

Thank you.
Sir, I have been searching many websites for reference for my project, but unable to know how SQL queries have been created initially. From wikipedia, I got to know that the query language was developed using ANSI-C but unable to know the coding used for development. I think it will help me create a tool for writing queries in a language other than English. Please suggest if any, it will be great help.

9

I'm still not sure what you are looking for.

  1. On the one hand, it sounds like you are looking to build a program that emits SQL that is ANSI SQL translated to another language. As I pointed out, I'm not sure where you would run that.

  2. On the other hand, it sounds like you want to build a program that reads SQL written in another language (like my previous example in Dutch) and translates it.

I'm not sure if you want to do 1 or 2. Perhaps if you post a few of the links you referred to, we can help you out a bit more.

Note that this is a SQL Server forum, not a C forum. However, the Postgres project is completely open source, as is MySql, MariaDB and others. If you are looking for examples of C programs that parse SQL, you can start there.

10

I am looking for option 2, where in a tool we can write SQL queries in other language,eg:Duch, and the query get parsed as an ordinary English Query.

11

Well, for starters, check this out: Postgresql developer site

12

Thanks for it, i will try there

13

As you said in above reply, as we write the 'Dutch' language query in the database editor tool will it be translated to 'English' SQL query and then get parsed? Will the result data be shown in same 'dutch' language?

14

The results depend on the query. If your query is returning rows from some table, and the table contains text, there is no built-in translator so it will be displayed as-is. However, SQL is locale-aware so will display dates, times and numbers in the locale-appropriate manner, if you SET LANGUAGE before running the query.

15

Thanks for your answers @gbritton. its really helpful for preparing document of my final project.

16

Assume that I have developed a tool and wrote a query to create a table in Dutch language, which then gets to the translator and converted into usual SQL query.
For example.,
CREATE TABLE emp
(
int emp_id
varchar emp_name (15)
varchar dept (15)
)

What is the background (pre-compiler) coding (in ANSI C or C or C++ only) for generating the table as output?
.
.
.
.
Note: consider the following Embedded SQL C code's snippet:
.
EXEC SQL begin declare section;
int cno;
varchar cname (20);
varchar address (20);
EXEC SQL end declare section;
.
. The pre compiler converts the above code line /varchar cname (20)/ as:
struct
{ unsigned short len;
unsigned char arr [20]
}cname;
.
and similar coding for other lines.
.
.
.
How will the pre-compiled code will be generated in SQL query's pre-compilation?

17

I'm not sure what you mean by this. What exactly are you sending to SQL Server?

18

I meant that SQL was written in ANSI C (as described in Postgres95 document, as well as for IBM's DB2), how was the coding written behind queries? I am unable to find any examples for these codes.

19

Again I'm not sure what you mean.

When I write a query, the "coding behind the queries" is what happens in my brain before I type it into the query editor. Not surprised you can't find any examples for that! Neurologists would love to have that code, too!

20

@gbritton

Sir, I meant as 'SQL was developed using ANSI C initially in 1970's', i was talking about that code in ANSI standard (if available).
If we write an SQL statement in the front-end tool, how will it be recognized by database in the back-end?
How will the interaction will go on in the back-end to generate output?
.
.
See this link Processing of SQL statement

The parsing and accessing (as in the above link) all goes on in the back-end.
.
.
Just as the above case, how could it connect to my project, as I need to develop a tool in which we can write queries in a language other than English?
(Hope you would understand this)

21

When you send a query to the database, it compiles the query into an internal format (depends on the database) then computes an execution plan for running the query. Then, it executes the plan and returns any results to the requester.

So, in your tool, you need to take the input in whatever (human) language you like, translate it to ANSI SQL, then pass it to the database for actual execution, then take the results, possibly translate them back into the (human) language of the tool and present the results.

FWIW I found the parser for Postgresql here: Parser

1 Like