{"id":1111,"date":"2012-11-20T20:37:57","date_gmt":"2012-11-20T20:37:57","guid":{"rendered":"https:\/\/clarionsharp.com\/blog\/?p=1111"},"modified":"2013-07-09T21:54:28","modified_gmt":"2013-07-09T21:54:28","slug":"sqlite-and-the-new-abc-sqlexecutor","status":"publish","type":"post","link":"https:\/\/clarionsharp.com\/blog\/sqlite-and-the-new-abc-sqlexecutor\/","title":{"rendered":"SQLite and the new ABC SQLexecutor"},"content":{"rendered":"

Clarion 9 introduces a new driver for working with SQLite databases.\u00a0 SQLite is one of the most widely deployed databases in use today. SQLite databases are widely used on both iOS and Android tablets and cell phones.\u00a0 SQLite databases are useful in any situations where simplicity of administration and easy maintenance are more important than scalability.<\/p>\n

The driver introduces a new prop; PROP:CreateDB. PROP:CreateDB is a command property that tells the SQL driver to create the SQLite physical database file that is specified in the OWNER attribute of the file. If the database file already exists, this command does nothing.\u00a0 The existing database file will not be destroyed.<\/p>\n

The CREATE statement converts Clarion data types to SQLite Data types using the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Clarion Data Type<\/td>\nSQLite Data Type<\/td>\n<\/tr>\n
STRING<\/td>\nCHAR<\/td>\n<\/tr>\n
CSTRING<\/td>\nVARCHAR<\/td>\n<\/tr>\n
STRING(8);GROUP OVER(STRING);DATE;TIME<\/td>\nDATETIME<\/td>\n<\/tr>\n
DATE<\/td>\nDATE<\/td>\n<\/tr>\n
PDECIMAL<\/td>\nNUMBER<\/td>\n<\/tr>\n
DECIMAL<\/td>\nNUMBER<\/td>\n<\/tr>\n
BYTE<\/td>\nTINYINT<\/td>\n<\/tr>\n
SHORT<\/td>\nSMALLINT<\/td>\n<\/tr>\n
LONG<\/td>\nINTEGER<\/td>\n<\/tr>\n
SREAL<\/td>\nFLOAT<\/td>\n<\/tr>\n
REAL<\/td>\nREAL<\/td>\n<\/tr>\n
BLOB<\/td>\nCLOB<\/td>\n<\/tr>\n
BLOB,BINARY<\/td>\nBLOB<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

However Clarion 9 introduces new ABC support so that you can easily deploy and execute SQL scripts. For example a create script like below;
\nCREATE TABLE Courses(
\nNumber INTEGER,
\nDescription CHAR(40),
\nCompleteDescription CLOB,
\nCONSTRAINT KeyNumber PRIMARY KEY (Number));
\nCREATE INDEX KeyDescription ON Courses(Description);<\/p>\n

The new class executes SQL code as a series of commands separated by an end of statement marker which you specify.\u00a0 Any errors encountered executing the script are reported using the passed error handler. In short, you provide an external file that contains your SQL script and its parsed and executed.\u00a0 The SQL code isn’t limited to just Create and Alter database scenarios, you can execute any valid SQL code. And of course the new ABC SQL script support is applicable across any Driver and SQL backend.<\/p>\n","protected":false},"excerpt":{"rendered":"

Clarion 9 introduces a new driver for working with SQLite databases.\u00a0 SQLite is one of the most widely deployed databases in use today. SQLite databases are widely used on both iOS and Android tablets and cell phones.\u00a0 SQLite databases are useful in any situations where simplicity of administration and easy maintenance are more important than … Continue reading SQLite and the new ABC SQLexecutor<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,5],"tags":[],"class_list":["post-1111","post","type-post","status-publish","format-standard","hentry","category-clarion9","category-clarionnews"],"_links":{"self":[{"href":"https:\/\/clarionsharp.com\/blog\/wp-json\/wp\/v2\/posts\/1111","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/clarionsharp.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/clarionsharp.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/clarionsharp.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/clarionsharp.com\/blog\/wp-json\/wp\/v2\/comments?post=1111"}],"version-history":[{"count":10,"href":"https:\/\/clarionsharp.com\/blog\/wp-json\/wp\/v2\/posts\/1111\/revisions"}],"predecessor-version":[{"id":1186,"href":"https:\/\/clarionsharp.com\/blog\/wp-json\/wp\/v2\/posts\/1111\/revisions\/1186"}],"wp:attachment":[{"href":"https:\/\/clarionsharp.com\/blog\/wp-json\/wp\/v2\/media?parent=1111"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/clarionsharp.com\/blog\/wp-json\/wp\/v2\/categories?post=1111"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/clarionsharp.com\/blog\/wp-json\/wp\/v2\/tags?post=1111"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}