Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

[AHK L] DBA 1.6 (OOP-SQL DataBase {SQLite, MySQL, ADO})


  • Please log in to reply
183 replies to this topic
kalik
  • Members
  • 53 posts
  • Last active: Sep 04 2015 07:56 AM
  • Joined: 09 Jun 2012

Hey guys, I'm using the example script to test out the functionality before writing my own implementation for my own needs and it works good with my database, but returns a 'A non-object value was improperly invoked. Specifically: 0' error when I try to get a record that does not exist. This is the request I use:

SELECT * FROM vulndetection WHERE vulncode = blablabla

The vulncode is a set of integers. Now, my question is - is can I change something to disable this warning and instead just show my own msgbox or something? My end goal is to print out the record on a GUI if it exists and if it isn't, do nothing. While I have no trouble with the gui and whatnot, I can't seem to disable that warning message. Do you guys got any clue?



itjustwerks
  • Members
  • 7 posts
  • Last active: Nov 13 2015 05:19 AM
  • Joined: 18 Dec 2009

Howdy!

 

I've been using this library for some time with SQLite, and recently had a need for an UPSERT operation. Of course, SQLite doesn't have an UPSERT statement, but you can do an "INSERT OR IGNORE", followed by an "UPDATE".

 

So I made a couple of changes to the "DataBaseSQLLite" class in the file "Lib\DataBaseSQLLite.ahk".

 

1) I changed the "INSERT INTO ..." statement in the "InsertMany" method to "INSERT OR IGNORE INTO...". This will prevent duplicate entries if the Primary Key or a UNIQUE field are already present in the table.

2) I added a new "UpsertMany" method. It's very similar to "InsertMany" in structure. Since the UPDATE statement requires an identifying key to match records on, I added a third argument to specify which field in the collection will be that key. When preparing the query, the identifying key needed to be pulled out of the "records" loop that was building the SQL statement and added to the end in the WHERE clause, while keeping the "?" reference index numbers sequential. Then the same sort of thing had to be done when binding the actual data to that template. Hope this helps someone!

 

Example usage:

col := new Collection()

; Could add records in a loop if adding more than one...
record := {}
record.theId := "1234567"
record.someProp := "Green"
record.another := "Eggs"
record.lastOne := "and Ham"
col.Add(record)

; Args: Records, Table, Field for Update lookup
myDb.UpsertMany(col, "TableName", "theId")

Here's the updated "Lib\DataBaseSQLLite.ahk" file:

; namespace DBA

class SQLite
{
	GetVersion(){
		return SQLite_LibVersion()
	}
	
	SQLiteExe(dbFile, commands, ByRef output){
		return SQLite_SQLiteExe(dbFile, commands, output)
	}
	
	__New(){
		throw Exception("This is a static Class. Don't create Instances from it!",-1)
	}
}

/*
	Represents a Connection to a SQLite Database
*/
class DataBaseSQLLite extends DBA.DataBase
{
	_handleDB := 0
	
	__New(handleDB){
		this._handleDB := handleDB
		if(!this.IsValid())
		{
			throw Exception("Can not create a DataBaseSQLLite instance, because the connection handle is not valid!")
		}
		ArchLogger.Log("New DataBaseSQLLite: Handle @" handleDB)
	}
	
	
	Close(){
		;ArchLogger.Log("DataBaseSQLLite: Close DB Handle @" handleDB)
		return SQLite_CloseDB(this._handleDB)
	}
	
	IsValid(){
		return (this._handleDB != 0)
	}
	
	GetLastError(){
		code := 0
		SQLite_ErrCode(this._handleDB, code)
		return code
	}
	
	GetLastErrorMsg(){
		msg := ""
		SQLite_ErrMsg(this._handleDB, msg)
		return msg
	}
	
	SetTimeout(timeout = 1000){
		return SQLite_SetTimeout(this._handleDB, timeout)
	}
	
	
   ErrMsg() {
      if (RC := DllCall("SQLite3\sqlite3_errmsg", "UInt", this._handleDB, "Cdecl UInt"))
         return StrGet(RC, "UTF-8")
      return ""
   }

   ErrCode() {
      return DllCall("SQLite3\sqlite3_errcode", "UInt", this._handleDB, "Cdecl UInt")
   }

   Changes() {
      return DllCall("SQLite3\sqlite3_changes", "UInt", this._handleDB, "Cdecl UInt")
   }
	
	
	/*
		Querys the DB and returns a RecordSet
	*/
	OpenRecordSet(sql, editable = false){
		
		if( this.IsValid() )
		{
			rs := new DBA.RecordSetSqlLite(this, SQlite_Query(this._handleDB, sql))
			;myliters := new DBA.RecordSetSqlLite
			;myrs := new DBA.RecordSet
			
			;MsgBox % "DBA.RecordSetSqlLite params:`t" rs.TestRecordSet()
			;MsgBox % "DBA.RecordSetSqlLite:`t"myliters.TestRecordSet()
			;MsgBox % "DBA.RecordSet:`t" myrs.TestRecordSet()
			
			return rs
		}else
			throw Exception("NotSupportedException: There is no valid DB Connection, OpenRecordSet requires a valid connection.",-1)
	}
	
	/*
		Querys the DB and returns a ResultTable or true/false
	*/
	Query(sql){
		
		ret := null
		
			if (RegExMatch(sql, "i)^\s*SELECT\s")){ ; check if this is a selection query
				
				try
				{
					ret := this._GetTableObj(sql)
				} catch e
					throw Exception("Select Query failed.`n`n" sql "`n`nChild Exception:`n" e.What "`n" e.Message "`n" e.File "@" e.Line, -1)
			} else {
				 
				try
				{
					ret := SQLite_Exec(this._handleDB, sql)
				} catch e
					throw Exception("Non Selection Query failed.`n`n" sql "`n`nChild Exception:`n" e.What " `n" e.Message, -1)
			}
			
		return ret
	}
	
	EscapeString(str){
		StringReplace, str, str, ', '', All ; replace all single quotes with double single-quotes. pascal escape'
		return str
	}
	
	QuoteIdentifier(identifier) {
		; ` characters are actually valid. Technically everthing but a literal null U+0000.
		; Everything else is fair game: http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
		StringReplace, identifier, identifier, ``, ````, All
		return "``" identifier "``"
	}
	
	
	BeginTransaction(){
		this.Query("BEGIN TRANSACTION;")
	}
	
	EndTransaction(){
		this.Query("COMMIT TRANSACTION;")
	}
	
	Rollback(){
		this.Query("ROLLBACK TRANSACTION;")
	}
	
        ; ADDED "UpsertMany" METHOD TO ALLOW INSERT OR UPDATE TO EXISTING RECORDS - 9/21/2015
	UpsertMany(records, tableName, updateKey){
		if(!is(records, Collection) || records.IsEmpty())
			return false
		
		; Do all Insert or Ignore statements
		this.InsertMany(records, tableName)
		
		; Do all Update statements
		colString := ""
		valString := ""
		columns := {}
		
		sql := "UPDATE " this.QuoteIdentifier(tableName) "`nSET "
		colOffset = 0
		for column, value in records.First()
		{
			colIdx := A_Index - colOffset
			if (column = updateKey) {
				sqlUpdateKey := "WHERE " . updateKey . "=?"
				colOffset = 1
				continue
			}
			sql .= column . "=?, `n"
			columns[column] := colIdx
			updateKeyIdx := colIdx + 1
		}
		StringTrimRight, sql, sql, 3
		columns[updateKey] := updateKeyIdx
		sql .= "`n" . sqlUpdateKey
		
		types := []
		for i,row in this._GetTableObj("PRAGMA table_info(" this.QuoteIdentifier(tableName) ")").Rows
		{
			if (columns.HasKey(row.name)){
				;MsgBox,0,Error,  % "row name found: " row.name "`nTypes: " row.type  ; #DEBUG
				types[columns[row.name]] := row.types
			}
		}
		
		this.BeginTransaction()
		
		query := SQLite_Query(this._handleDB, sql) ;prepare the query
		if ErrorLevel
			msgbox % "Error Preparing the Query: " . errorlevel
		
		try
		{
			for i, record in records
			{
				for col, val in record
				{
					if (col = updateKey) {
						updateKeyVal := val
						continue
					}
					if (!columns.HasKey(col))
						throw Exception("Irregular params: Column not found: [" col "] in`nTable Columns:" this.printKeys(columns))
					fieldType := "auto"
					if(types.HasKey(columns[col]))
						fieldType := types[columns[col]]
					ret := SQLite_bind(query, columns[col], val, fieldType)
					;MsgBox % " bind returned " ret
				}
				ret := SQLite_bind(query, columns[updateKey], updateKeyVal, "auto")
				SQLite_Step(query)
				SQLite_Reset(query)
			}
		}
		catch e
		{
			this.Rollback()
			throw Exception("InsertMany failed.`n`nChild Exception:`n" e.What " `n" e.Message, -1)
		}
		SQLite_QueryFinalize(query)
		this.EndTransaction()
		return True
	}
	
	InsertMany(records, tableName){
		if(!is(records, Collection) || records.IsEmpty())
			return false
		
		colString := ""
		valString := ""
		columns := {}
		
		for column, value in records.First()
		{
			colString .= "," this.QuoteIdentifier(column)
			valString .= ",?"
			columns[column] := A_Index
		}
                ; ADDED "OR IGNORE" TO INSERT STATEMENT - 9/21/2015
		sql := "INSERT OR IGNORE INTO " this.QuoteIdentifier(tableName) "`n(" SubStr(colstring, 2) ")`nVALUES`n(" SubStr(valString, 2) ")" 
		
		
		types := []
		for i,row in this._GetTableObj("PRAGMA table_info(" this.QuoteIdentifier(tableName) ")").Rows
		{
			if (columns.HasKey(row.name)){
				;MsgBox,0,Error,  % "row name found: " row.name "`nTypes: " row.type  ; #DEBUG
				types[columns[row.name]] := row.types
			}
		}
		
		
		this.BeginTransaction()
		
		query := SQLite_Query(this._handleDB, sql) ;prepare the query
		if ErrorLevel
			msgbox % errorlevel
		
		try
		{
			for i, record in records
			{
				for col, val in record
				{
					if (!columns.HasKey(col))
						throw Exception("Irregular params: Column not found: [" col "] in`nTable Columns:" this.printKeys(columns))
					fieldType := "auto"
					if(types.HasKey(columns[col]))
						fieldType := types[columns[col]]
					
					ret := SQLite_bind(query, columns[col], val, fieldType)
					;MsgBox % " bind returned " ret
				}
				SQLite_Step(query)
				SQLite_Reset(query)
			}
		}
		catch e
		{
			this.Rollback()
			throw Exception("InsertMany failed.`n`nChild Exception:`n" e.What " `n" e.Message, -1)
		}
		SQLite_QueryFinalize(query)
		this.EndTransaction()
		return True
	}
	
	printKeys(arr){
		str := ""
		for key, val in arr
		{
			str .= key ","
		}
		return str
	}
	
	Insert(record, tableName){
		col := new Collection()
		col.Add(record)
		return this.InsertMany(col, tableName)
	}
	
	/*
		deprecated
	*/
	_GetTableObj(sql, maxResult = -1) {
		
		err := 0, rc := 0, GetRows := 0
		i := 0
		rows := cols := 0
		names := new Collection()
		dbh := this._handleDB

		SQLite_LastError(" ")

		if(!_SQLite_CheckDB(dbh)) {
			SQLite_LastError("ERROR: Invalid database handle " . dbh)
			ErrorLevel := _SQLite_ReturnCode("SQLITE_ERROR")
			return False
		}

		if (maxResult < -1)
			maxResult := -1
		mytable := ""
		Err := 0
		
		_SQLite_StrToUTF8(SQL, UTF8)
		RC := DllCall("SQlite3\sqlite3_get_table", "Ptr", dbh, "Ptr", &UTF8, "Ptr*", mytable
				   , "Ptr*", rows, "Ptr*", cols, "Ptr*", err, "Cdecl Int")
				   
		If (ErrorLevel) {
		  SQLite_LastError("ERROR: DLLCall sqlite3_get_table failed!")
		  Return False
		}
		If (rc) {
		  SQLite_LastError(StrGet(err, "UTF-8"))
		  DllCall("SQLite3\sqlite3_free", "Ptr", err, "cdecl")
		  ErrorLevel := rc
		  return false
		}

		

	   if (maxResult = 0) {
		  DllCall("SQLite3\sqlite3_free_table", "Ptr", mytable, "Cdecl")   
		  If (ErrorLevel) {
			 SQLite_LastError("ERROR: DLLCall sqlite3_close failed!")
			 Return False
		  }
		  Return True
	   }
	   
	   if (maxResult = 1)
		  GetRows := 0
	   else if (maxResult > 1) && (maxResult < rows)
		  GetRows := MaxResult
	   else
		  GetRows := rows
	   Offset := 0
	   
	   Loop, % cols
	   {
		  names.Add(StrGet(NumGet(mytable+0, Offset), "UTF-8"))
		  Offset += A_PtrSize
	   }

		myRows := new Collection()
		Loop, %GetRows% {
			i := A_Index
			fields := new Collection()
			Loop, % Cols 
			{
				fields.Add(StrGet(NumGet(mytable+0, Offset), "UTF-8"))
				Offset += A_PtrSize
			}
			myRows.Add(new DBA.Row(Names, fields))
		}
		tbl := new DBA.Table(myRows, Names)
		
		; Free Results Memory
		DllCall("SQLite3\sqlite3_free_table", "Ptr", mytable, "Cdecl")   
		if (ErrorLevel) {
			SQLite_LastError("ERROR: DLLCall sqlite3_close failed!")
			return false
		}
		return tbl
	}
	

   ReturnCode(RC) {
      static RCODE := {SQLITE_OK: 0          ; Successful result
                     , SQLITE_ERROR: 1       ; SQL error or missing database
                     , SQLITE_INTERNAL: 2    ; NOT USED. Internal logic error in SQLite
                     , SQLITE_PERM: 3        ; Access permission denied
                     , SQLITE_ABORT: 4       ; Callback routine requested an abort
                     , SQLITE_BUSY: 5        ; The database file is locked
                     , SQLITE_LOCKED: 6      ; A table in the database is locked
                     , SQLITE_NOMEM: 7       ; A malloc() failed
                     , SQLITE_READONLY: 8    ; Attempt to write a readonly database
                     , SQLITE_INTERRUPT: 9   ; Operation terminated by sqlite3_interrupt()
                     , SQLITE_IOERR: 10      ; Some kind of disk I/O error occurred
                     , SQLITE_CORRUPT: 11    ; The database disk image is malformed
                     , SQLITE_NOTFOUND: 12   ; NOT USED. Table or record not found
                     , SQLITE_FULL: 13       ; Insertion failed because database is full
                     , SQLITE_CANTOPEN: 14   ; Unable to open the database file
                     , SQLITE_PROTOCOL: 15   ; NOT USED. Database lock protocol error
                     , SQLITE_EMPTY: 16      ; Database is empty
                     , SQLITE_SCHEMA: 17     ; The database schema changed
                     , SQLITE_TOOBIG: 18     ; String or BLOB exceeds size limit
                     , SQLITE_CONSTRAINT: 19 ; Abort due to constraint violation
                     , SQLITE_MISMATCH: 20   ; Data type mismatch
                     , SQLITE_MISUSE: 21     ; Library used incorrectly
                     , SQLITE_NOLFS: 22      ; Uses OS features not supported on host
                     , SQLITE_AUTH: 23       ; Authorization denied
                     , SQLITE_FORMAT: 24     ; Auxiliary database format error
                     , SQLITE_RANGE: 25      ; 2nd parameter to sqlite3_bind out of range
                     , SQLITE_NOTADB: 26     ; File opened that is not a database file
                     , SQLITE_ROW: 100       ; sqlite3_step() has another row ready
                     , SQLITE_DONE: 101}     ; sqlite3_step() has finished executing
      return RCODE.HasKey(RC) ? RCODE[RC] : ""
   }	
}



huythach83
  • Members
  • 3 posts
  • Last active: Oct 23 2015 09:22 AM
  • Joined: 31 May 2007

Been use this lib for a while now. Everything work fine but today I encountered a problem: I have a table with some row content in Vietnamese. When I made query for the content, it turn all Vietnamese charater into ???

 

Example

 

Row content: Mới tải về máy chưa biết thế nào cho 5*sao

 

The result after query using QueryValue and display using MsgBox: M??i ta?i vê? ma?y ch?a biê?t thê? na?o cho 5*sao
 
Maybe the lib not fully support Unicode. Anyone can help me with this problem?
 
Thanks in advance


singladur
  • Members
  • 1 posts
  • Last active:
  • Joined: 30 Oct 2015

Dear IsNull,

 

I am using AHK to develop a small app that make use of Databases. I have choosen ".accdb" files to work with.

I am, some how, used to the oop approach, so I like you lib and, of course, I am using it.

So, first of all, thank you for the great lib!!

Second, I have a problem. I want my app to be able to create a new database and to create new tables in a database.

Right now I am able to read and write rows in tables that are already created.

I have not seen a "CreateNewTable" method so I suppose it is not implemented yet. So I have perused your code and see that you use the "ComObjCreate(ADODB.Connection)" or "ComObjCreate(ADODB.Recordset)". I have no idea about ADO, so I have been also perusing ADO help.  But my knowledge does not allow me to dig dipper into the correct use of ADO.

 

The point is, ¿is it possible to "insert a new table in a existing database" using ADO and AHK?¿Is it possible to do so with the two objects mentioned above?¿Should be use the "ComObjCreate(ADODB.Command)" object  instead?

 

I have tried some ideas but they have not worked. For example:

 

 

connectionString := "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" A_ScriptDir "\Prueba.accdb"
currentDB := DBA.DataBaseFactory.OpenDataBase("ADO", connectionString)
 

currentDB.Open()
currentDB.Execute("CREATE TABLE TblNew(Name text(40), SurName text(40))")
CurrentDB.Close()

 

Have you any idea of how to obtain it