SQLite in AU3 syntax

[Scriptonize]

Hi,

I've a question about how to use specific SQLite commands within AU3.

I'm trying to use this SQLite command within AU3:

  Quote

CREATE TABLE "HostData" (

"H_ID" INTEGER NOT NULL UNIQUE check(typeof("H_ID") = 'integer') ,

"H_Name" CHAR NOT NULL UNIQUE DEFAULT 'Birdy',

"H_IP" VARCHAR NOT NULL DEFAULT '127.0.0.1',

"H_MaxCon" INTEGER NOT NULL check(typeof("H_MaxCon") = 'integer') DEFAULT 35,

"H_CurrCon" INTEGER check(typeof("H_CurrCon") = 'integer') DEFAULT 0,

"H_MaxCon_Def" INTEGER NOT NULL check(typeof("H_MaxCon_Def") = 'integer') DEFAULT 35,

"H_DNS_Alias" CHAR NOT NULL DEFAULT BBDZ,

"H_DNS_RType" VARCHAR NOT NULL DEFAULT A,

"H_DNS_State" BOOL DEFAULT 0,

"H_Maint_Mode" BOOL NOT NULL DEFAULT 0,

"H_Remark" TEXT DEFAULT 'New host',

"H_Sched_DateOut" DATETIME DEFAULT CURRENT_DATE,

"H_Sched_Time_Out" DATETIME DEFAULT CURRENT_TIME,

"H_Sched_Date_In" DATETIME DEFAULT CURRENT_DATE,

"H_Sched_Time_In" DATETIME DEFAULT CURRENT_TIME,

"H_Sched_Recur" INTEGER check(typeof("H_Sched_Recur") = 'integer') DEFAULT 7,

"H_Sched_Last_Action" CHAR DEFAULT None,

"H_Sched_Action_Owner" CHAR DEFAULT Scheduler,

PRIMARY KEY ("H_ID", "H_Name", "H_IP")

)

I've tried 2 aproaches:

With _SQLite_Exec and with _SQL_Query

AU3 code with _SQL_Query()

Func _CreateNewDB() 
Local $MyQuery, $h_Query 

$MyQuery="CREATE TABLE IF NOT EXISTS HostData(" & _ 
"H_ID INTEGER NOT NULL UNIQUE, " & _ 
"H_Name CHAR NOT NULL UNIQUE DEFAULT 'Birdy', " & _
"H_IP VARCHAR NOT NULL DEFAULT '127.0.0.1', " & _ 
"H_MaxCon INTEGER NOT NULL DEFAULT 35, " & _ 
"H_CurrCon INTEGER DEFAULT 0, " & _ 
"H_MaxCon_Def INTEGER NOT NULL DEFAULT 35, " & _ 
"H_DNS_Alias CHAR NOT NULL DEFAULT 'BBDZ', " & _ 
"H_DNS_RType VARCHAR NOT NULL DEFAULT 'A', " & _ 
"H_DNS_State BOOL DEFAULT 0, " & _ 
"H_Maint_Mode BOOL NOT NULL DEFAULT 0, " & _ 
"H_Remark TEXT DEFAULT 'New host', " & _ 
"H_Sched_DateOut DATETIME DEFAULT CURRENT_DATE, " & _
"H_Sched_Time_Out DATETIME DEFAULT CURRENT_TIME, " & _ 
"H_Sched_Date_In DATETIME DEFAULT CURRENT_DATE, " & _ 
"H_Sched_Time_In DATETIME DEFAULT CURRENT_TIME, " & _ 
"H_Sched_Recur INTEGER DEFAULT 7, " & _ 
"H_Sched_Last_Action CHAR DEFAULT 'None', " & _
"H_Sched_Action_Owner CHAR DEFAULT 'Scheduler', " & _ 
"PRIMARY KEY (H_ID, H_Name, H_IP)" 

IF NOT _SQLite_Query($h_SqLiteDB,$MyQuery,$h_Query) = $SQLITE_OK Then _SQLite_QueryFinalize($h_Query) 

EndFunc

This approach fails with error:

_SQLite_LibVersion=3.6.22 ! SQLite.au3 Error
 --> Function: _SQLite_Query 
 --> Query: CREATE TABLE IF NOT EXISTS HostData(H_ID INTEGER NOT NULL UNIQUE, H_Name CHAR NOT NULL UNIQUE DEFAULT 'Birdy', H_IP VARCHAR NOT NULL DEFAULT '127.0.0.1', H_MaxCon INTEGER NOT NULL DEFAULT 35, H_CurrCon INTEGER DEFAULT 0, H_MaxCon_Def INTEGER NOT NULL DEFAULT 35, H_DNS_Alias CHAR NOT NULL DEFAULT 'BBDZ', H_DNS_RType VARCHAR NOT NULL DEFAULT 'A', H_DNS_State BOOL DEFAULT 0, H_Maint_Mode BOOL NOT NULL DEFAULT 0, H_Remark TEXT DEFAULT 'New host', H_Sched_DateOut DATETIME DEFAULT CURRENT_DATE, H_Sched_Time_Out DATETIME DEFAULT CURRENT_TIME, H_Sched_Date_In DATETIME DEFAULT CURRENT_DATE, H_Sched_Time_In DATETIME DEFAULT CURRENT_TIME, H_Sched_Recur INTEGER DEFAULT 7, H_Sched_Last_Action CHAR DEFAULT 'None', H_Sched_Action_Owner CHAR DEFAULT 'Scheduler', PRIMARY KEY H_ID,H_Name,H_IP
 --> Error: near "H_ID": syntax error

AU3 code with _SQL_Exec()

Func _CreateNewDB() 
_SQLite_Exec($h_SqLiteDB,"CREATE TABLE IF NOT EXISTS " & _ 
 "CREATE TABLE HostData (" & _ "H_ID INTEGER NOT NULL UNIQUE check(typeof(H_ID) = 'integer') ," & _ 
 "H_Name CHAR NOT NULL UNIQUE DEFAULT 'Birdy'," & _ 
 "H_IP VARCHAR NOT NULL DEFAULT '127.0.0.1'," & _
 "H_MaxCon INTEGER NOT NULL check(typeof(H_MaxCon) = 'integer') DEFAULT 35," & _
 "H_CurrCon INTEGER check(typeof(H_CurrCon) = 'integer') DEFAULT 0," & _
 "H_MaxCon_Def INTEGER NOT NULL check(typeof(H_MaxCon_Def) = 'integer') DEFAULT 35," & _
 "H_DNS_Alias CHAR NOT NULL DEFAULT 'NEW DNS HOST'," & _
 "H_DNS_RType VARCHAR NOT NULL DEFAULT 'A'," & _
 "H_DNS_State BOOL DEFAULT 0," & _
 "H_Maint_Mode BOOL NOT NULL DEFAULT 0," & _ 
 "H_Remark TEXT DEFAULT 'New host'," & _ 
 "H_Sched_DateOut DATETIME DEFAULT CURRENT_DATE," & _ 
 "H_Sched_Time_Out DATETIME DEFAULT CURRENT_TIME," & _
 "H_Sched_Date_In DATETIME DEFAULT CURRENT_DATE," & _
 "H_Sched_Time_In DATETIME DEFAULT CURRENT_TIME," & _
 "H_Sched_Recur INTEGER check(typeof(H_Sched_Recur) = 'integer') DEFAULT 7," & _
 "H_Sched_Last_Action CHAR DEFAULT None," & _
 "H_Sched_Action_Owner CHAR DEFAULT Scheduler," & _ 
 "PRIMARY KEY (H_ID, H_Name, H_IP)")
EndFunc

This approach ends with nearly the same error:

_SQLite_LibVersion=3.6.22 ! 
 SQLite.au3 Error 
--> Function: _SQLite_Exec 
--> Query: CREATE TABLE IF NOT EXISTS CREATE TABLE HostData (H_ID INTEGER NOT NULL UNIQUE check(typeof(H_ID) = 'integer') ,H_Name CHAR NOT NULL UNIQUE DEFAULT 'Birdy',H_IP VARCHAR NOT NULL DEFAULT '127.0.0.1',H_MaxCon INTEGER NOT NULL check(typeof(H_MaxCon) = 'integer') DEFAULT 35,H_CurrCon INTEGER check(typeof(H_CurrCon) = 'integer') DEFAULT 0,H_MaxCon_Def INTEGER NOT NULL check(typeof(H_MaxCon_Def) = 'integer') DEFAULT 35,H_DNS_Alias CHAR NOT NULL DEFAULT 'NEW DNS HOST',H_DNS_RType VARCHAR NOT NULL DEFAULT 'A',H_DNS_State BOOL DEFAULT 0,H_Maint_Mode BOOL NOT NULL DEFAULT 0,H_Remark TEXT DEFAULT 'New host',H_Sched_DateOut DATETIME DEFAULT CURRENT_DATE,H_Sched_Time_Out DATETIME DEFAULT CURRENT_TIME,H_Sched_Date_In DATETIME DEFAULT CURRENT_DATE,H_Sched_Time_In DATETIME DEFAULT CURRENT_TIME,H_Sched_Recur INTEGER check(typeof(H_Sched_Recur) = 'integer') DEFAULT 7,H_Sched_Last_Action CHAR DEFAULT None,H_Sched_Action_Owner CHAR DEFAULT Scheduler,PRIMARY KEY (H_ID, H_Name, H_IP) 
--> Error: near "CREATE": syntax error

Q1: What am I missing??

Q2: Which of the 2 methods is the best?

Edited September 29, 2010 by Scriptonize

[KaFu]

This works for me:

#include <sqlite.au3>
#include <sqlite.dll.au3>

Local $hQuery, $aRow
_SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open()

_SQLite_Exec(-1, "CREATE TABLE HostData (" & _
        "H_ID INTEGER NOT NULL UNIQUE check(typeof(H_ID) = 'integer') ," & _
        "H_Name CHAR NOT NULL UNIQUE DEFAULT 'Birdy'," & _
        "H_IP VARCHAR NOT NULL DEFAULT '127.0.0.1'," & _
        "H_MaxCon INTEGER NOT NULL check(typeof(H_MaxCon) = 'integer') DEFAULT 35," & _
        "H_CurrCon INTEGER check(typeof(H_CurrCon) = 'integer') DEFAULT 0," & _
        "H_MaxCon_Def INTEGER NOT NULL check(typeof(H_MaxCon_Def) = 'integer') DEFAULT 35," & _
        "H_DNS_Alias CHAR NOT NULL DEFAULT 'NEW DNS HOST'," & _
        "H_DNS_RType VARCHAR NOT NULL DEFAULT 'A'," & _
        "H_DNS_State BOOL DEFAULT 0," & _
        "H_Maint_Mode BOOL NOT NULL DEFAULT 0," & _
        "H_Remark TEXT DEFAULT 'New host'," & _
        "H_Sched_DateOut DATETIME DEFAULT CURRENT_DATE," & _
        "H_Sched_Time_Out DATETIME DEFAULT CURRENT_TIME," & _
        "H_Sched_Date_In DATETIME DEFAULT CURRENT_DATE," & _
        "H_Sched_Time_In DATETIME DEFAULT CURRENT_TIME," & _
        "H_Sched_Recur INTEGER check(typeof(H_Sched_Recur) = 'integer') DEFAULT 7," & _
        "H_Sched_Last_Action CHAR DEFAULT None," & _
        "H_Sched_Action_Owner CHAR DEFAULT Scheduler," & _
        "PRIMARY KEY (H_ID, H_Name, H_IP));")

_SQLite_Close()
_SQLite_Shutdown()

You missed a closing bracket and semicolon and the end of the syntax. To check if a table already exists I usually use something like this:

"SELECT sql FROM sqlite_master WHERE tbl_name = 'HostData' AND type = 'table';""

If this returns a result the table already exists.

[Scriptonize]

Aaaahhh, Thank you very much!!!

I knew it couldn't be a big issue, but I failed to find the typo.

Again, thanx.