Barry SQL Precompiler overview (2017)

[worksofliam]

While CEC 2017 was fantastic, I worked on a small project to have more practice with the DB2 CLI. While I have worked with the DB2 CLI when I created the DB2 module for LuaILE, I wanted to do more.

The DB2 CLI APIs are some of my favourite system APIs to use since they are integrated with the platform - just another great reason for DB2 for i.

This time around, I decided to try and write a shitty precompiler. Specificlly, an embedded SQL precompiler for RPG. The original idea came from Scott Forstie. I said to him I was going to create a precompiler, but he suggested I should have an end goal - and THEN he suggested that I make it thread safe since regular embedded SQL is not. So now, a thread safe precompiler is the long term goal. In order to do that, Scott suggested I use server mode to handle the SQL and I think it will do that at some point down the line.

For now, I just want something that works. I want the base of the logic so it's easy to manipulate later. Scott also suggested the name 'Barry SQL', so barrySQL it is! Note: this project is a complete joke, so be prepared for shitty code!

barrySQL

barrySQL will be referenced as BSQL.

1. **FREE RPGLE support only.
2. Compiles from the IFS only
3. Does not match regular ILE embedded SQL.
4. Does not type chech against fields provided
5. All BSQL statements begin with EXEC SQL and do not have an end-of-line delimiter.

Functionality

• DEFINE - required in all BSQL modules
  • HEADERS is used to define the prototypes and constants for the DB2 CLI.
  • FIELDS is used to define the required fields for the DB2 CLI. (e.g. env, conn, hdl, stmts)
• CONNECT - connect to the local/remote database
  • EXEC SQL CONNECT *LOCAL to connect to the local database.
• SELECT - Select records from a file
  • EXEC SQL SELECT ... to start a basic SELECT statement.
  • When using SELECT, you must also use CLOSE.
• FETCH - Fetch records and out data into fields
  • INTO field field field to fetch the next record from the previous SELECT into a set of defined fields.
• CLOSE - Close a statement cursor
  • CLOSE is used to close the most recently opened SELECT statement.
• UPDATE/DELETE/CREATE - Execute a UPDATE/DELETE/CREATE statement
  • EXEC SQL UPDATE
  • EXEC SQL DELETE
  • EXEC SQL CREATE
  • Used to execute statements. Does not require a CLOSE as does not return results.
• DISCONNECT - Disconnect from database
  • EXEC SQL DISCONNECT is used to disconnect from the current connection.
• EXECUTE - Execute an SQL statement from a string
  • EXEC SQL EXECUTE field to use a character variable as an SQL string.
  • Always requires CLOSE.

Example

The following snippet shows off some of the functionality within barrySQL.

**FREE
CTL-OPT DFTACTGRP(\*NO);

EXEC SQL DEFINE HEADERS
EXEC SQL DEFINE FIELDS

Dcl-S lProdID Packed(5);
Dcl-S lProdName Char(30);

//Connect to the local DB
EXEC SQL CONNECT *LOCAL

//Select two fields 
EXEC SQL SELECT PRID, PRNAME FROM PRODUCTSP

//Only run if there is no errors.
if (sqlreturn = 0);
  EXEC SQL FETCH INTO lProdID lProdName
  
  dow (sqlreturn = 0);
    DSPLY ('Name: ' + %Trim(lProdName) + ', ' + %Char(lProdID));
    
    EXEC SQL FETCH INTO lProdID lProdName
  enddo;
endif;

//Always close the cursor!!
EXEC SQL CLOSE 

//Disconnect from the DB
EXEC SQL DISCONNECT

*InLR = *On;
Return;

..Now let's take a look at the output of this program after running through CRTBRYSQL.

---

//BSQL
EXEC SQL CONNECT \*LOCAL

//DB2 CLI
SQLAllocEnv(%Addr(env));
SQLAllocConnect(env:%Addr(hdl));
sqlreturn = SQLConnect(hdl:'*LOCAL':SQL_NTS:usr:SQL_NTS:'':SQL_NTS);
SQLSetConnectAttr(hdl:SQL_ATTR_DBC_SYS_NAMING:%Addr(sqltrue):0);

The CONNECT statement allocates the environment space and connection space - then proceeds to connect and set the SQL naming convention to *SYS.

---

//BSQL
EXEC SQL SELECT PRID, PRNAME FROM PRODUCTSP

//DB2 CLI
SQLAllocStmt(hdl:%Addr(stmt(1)));
sqlreturn = SQLExecDirect(stmt(1):'SELECT PRID, PRNAME FROM PRODUCTSP':SQL_NTS);

This one is nice and simple. We are allocating the statement and then executing the statement directly, which is used when there is no need to prepare.

---

//BSQL
EXEC SQL FETCH INTO lProdID lProdName

//DB2 CLI
sqlreturn = SQLFetch(stmt(1));
SQLGetCol(stmt(1):1:SQL_DEFAULT:%Addr(lProdID):%Size(lProdID):%Addr(rlen));
SQLGetCol(stmt(1):2:SQL_DEFAULT:%Addr(lProdName):%Size(lProdName):%Addr(rlen));

Next is to fetch the next record available and then assign the columns to their chosen variables which is specified in the BSQL (lProdID and lProdName)

---

//BSQL
EXEC SQL CLOSE 
EXEC SQL DISCONNECT

//DB2 CLI
SQLCloseCursor(stmt(1));
SQLFreeStmt(stmt(1):SQL_CLOSE);

SQLDisconnect(hdl);
SQLFreeConnect(hdl);
SQLFreeEnv(env);

The CLOSE statement closes the cursor (if it's open) and then frees the allocated statement. Finally we disconnect from the database, and free the handles we had open.

End

And this is my latest project. The next post about BSQL will be about performance compared to regular Embedded SQL (CRTSQLRPGI).