[Class] SQLiteDB - Update on 2016-03-28

Post your working scripts, libraries and tools
m3user
Posts: 107
Joined: 17 Jan 2014, 18:11

Re: [Class] SQLiteDB - Update on 2016-03-28

06 Sep 2017, 15:51

Hi, just found this interesting class - thanks just me!

Forgive me for this question, I have no sql knowledge. I have a MySQL file, which needs to be edited. To avoid repetitive task (thousands of records) I wonder if this class could help.
The file has two fields - user_code and user_amount. I would like to change the user_amount value for each user_code with the data from my csv file.

How could this be done? Thanks.
just me
Posts: 5515
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2016-03-28

06 Sep 2017, 16:06

m3user wrote:... I have a MySQL file, ...
Typo?
m3user
Posts: 107
Joined: 17 Jan 2014, 18:11

Re: [Class] SQLiteDB - Update on 2016-03-28

06 Sep 2017, 16:31

yes, there are many database files (tables), I would like to edit fields in one of them. Hope this makes sense. :-)
just me
Posts: 5515
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2016-03-28

07 Sep 2017, 02:11

:arrow: SQL As Understood By SQLite

You might try the following with a copy of your DB file:
  • Create a new instance of the class SQLiteDB:

    Code: Select all

    DB := New SQLiteDB
  • Open your DB file:

    Code: Select all

    If !DB.OpenDB(FullDBFilePath) {
       MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
    
  • Because you want to change many values, start a transaction:

    Code: Select all

    If !DB.Exec("BEGIN TRANSACTION;")
       MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
  • Read and parse your CSV file, within the parsing-loop try to use:

    Code: Select all

    SQL := "UPDATE table-name SET user_amount = " . CSV_amount . " WHERE user_name = '" . CSV_name . "';" ; replace table-name with your table name.
    If !DB.Exec(SQL)
       MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
  • After the parsing-loop has finished, commit the transaction to update the DB file:

    Code: Select all

    If !DB.Exec("COMMIT TRANSACTION;")
       MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
  • Close the DB file:

    Code: Select all

    If !DB.CloseDB()
       MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
  • Check the result.
m3user
Posts: 107
Joined: 17 Jan 2014, 18:11

Re: [Class] SQLiteDB - Update on 2016-03-28

09 Sep 2017, 14:13

Thanks, very kind. I'll try that.
User avatar
jNizM
Posts: 2329
Joined: 30 Sep 2013, 01:33
GitHub: jNizM
Contact:

Re: [Class] SQLiteDB - Update on 2016-03-28

06 Dec 2017, 06:50

Hey just me,

is it possible to add some password protection or does sqlite support something like this?
So the database is encrypted and without password you can not connect or read the database.

thx
jNizM
[AHK] 1.1.30.00 x64 Unicode | [WIN] 10 Pro (Version 1803) x64 | [GitHub] Profile
Donations are appreciated if I could help you
burque505
Posts: 585
Joined: 22 Jan 2017, 19:37

Re: [Class] SQLiteDB - Update on 2016-03-28

06 Dec 2017, 12:16

Hi jNizM, I bet you've found this already, but here are a couple of links anyway:
Botan, BSD licensed crypto lib
Botan SQLite3 encryption coded
I haven't tried Botan, though.
Regards,
burque505
iNerino
Posts: 4
Joined: 26 Dec 2017, 16:02

Re: [Class] SQLiteDB - Update on 2016-03-28

26 Dec 2017, 16:09

Hello guys. I need to decrypt and turn db to .txt file, without bytes like this
https://imgur.com/dvwR2B9

Can you help me please? <3
Last edited by iNerino on 27 Dec 2017, 04:31, edited 1 time in total.
User avatar
joedf
Posts: 6498
Joined: 29 Sep 2013, 17:08
Facebook: J0EDF
Google: +joedf
GitHub: joedf
Location: Canada, Quebec
Contact:

Re: [Class] SQLiteDB - Update on 2016-03-28

26 Dec 2017, 21:22

You mean DEcrypt?
Image Image Image Image Image
Windows 10 x64 Professional, Intel i5-8500 @ 3.00 GHz, 16GB DDR4 3200 MHz, NVIDIA GTX 1060 6GB | [About Me] | [ASPDM - StdLib Distribution]
[Populate the AHK MiniCity!] | [Qonsole - Quake-like console emulator] | [LibCon - Autohotkey Console Library] | [About the AHK Foundation]
SOTE
Posts: 162
Joined: 15 Jun 2015, 06:21

Re: [Class] SQLiteDB - Update on 2016-03-28

27 Dec 2017, 03:18

This is awesome. Thanks just me, great work!
iNerino
Posts: 4
Joined: 26 Dec 2017, 16:02

Re: [Class] SQLiteDB - Update on 2016-03-28

27 Dec 2017, 03:32

joedf wrote:You mean DEcrypt?
Yes, sorry)
User avatar
joedf
Posts: 6498
Joined: 29 Sep 2013, 17:08
Facebook: J0EDF
Google: +joedf
GitHub: joedf
Location: Canada, Quebec
Contact:

Re: [Class] SQLiteDB - Update on 2016-03-28

27 Dec 2017, 12:01

Do you have the password?
Image Image Image Image Image
Windows 10 x64 Professional, Intel i5-8500 @ 3.00 GHz, 16GB DDR4 3200 MHz, NVIDIA GTX 1060 6GB | [About Me] | [ASPDM - StdLib Distribution]
[Populate the AHK MiniCity!] | [Qonsole - Quake-like console emulator] | [LibCon - Autohotkey Console Library] | [About the AHK Foundation]
iNerino
Posts: 4
Joined: 26 Dec 2017, 16:02

Re: [Class] SQLiteDB - Update on 2016-03-28

27 Dec 2017, 17:37

joedf wrote:Do you have the password?
What password? Why is it needed?
User avatar
joedf
Posts: 6498
Joined: 29 Sep 2013, 17:08
Facebook: J0EDF
Google: +joedf
GitHub: joedf
Location: Canada, Quebec
Contact:

Re: [Class] SQLiteDB - Update on 2016-03-28

27 Dec 2017, 17:53

You want to brute force it? Is this your database?
Encryption usually requires a key
Image Image Image Image Image
Windows 10 x64 Professional, Intel i5-8500 @ 3.00 GHz, 16GB DDR4 3200 MHz, NVIDIA GTX 1060 6GB | [About Me] | [ASPDM - StdLib Distribution]
[Populate the AHK MiniCity!] | [Qonsole - Quake-like console emulator] | [LibCon - Autohotkey Console Library] | [About the AHK Foundation]
DanielToward13
Posts: 63
Joined: 18 May 2017, 10:56

Re: [Class] SQLiteDB - Update on 2016-03-28

28 Dec 2017, 00:25

I am trying to fetch some data and then insert them into another table but I am getting the Msg: near "n": syntax error Code: 1 error. The process can insert 22 rows but get stuck in the 23th row which starts with ǝ bad´'n. I suspect that the ' character is causing the problem. My database has more than 1 million rows with all types of characters such as ' " : ; "" , ` ~ / < > # ^, etc. My question is how to prevent syntax conflicts in my case?

Code: Select all

#Persistent
#SingleInstance Force
#NoEnv  ;~ Recommended for performance and compatibility with future AutoHotkey releases.

#Include Lib\Class_SQLiteDB\Class_SQLiteDB.ahk

SendMode Input  ;~ Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ;~ Ensures a consistent starting directory.
SetTitleMatchMode, 3


DBFileName := A_ScriptDir . "\db\prep.db"
; ============================================================================
; Use Class SQLiteDB : Initialize and get lib version
; ============================================================================
DB := new SQLiteDB
Sleep, 1000
Version := DB.Version
; ============================================================================
; Use Class SQLiteDB : Open database
; ============================================================================
If !DB.OpenDB(DBFileName) {
   MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
   ExitApp
}
Sleep, 1000
; ============================================================================
; Use Class SQLiteDB : Get Table
; ============================================================================
sql := "Select * from hwdeng;" ; Change the table name to match your table
DB.GetTable(sql, Result)
; ============================================================================
; Use Class SQLiteDB : Create Table
; ============================================================================
SQL := "CREATE TABLE MyPreparation (ID INT PRIMARY KEY NOT NULL, ENG TEXT NOT NULL, PER TEXT NOT NULL);"
If !DB.Exec(SQL)
   MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
Sleep, 1000
; ============================================================================
; Use Class SQLiteDB : Insert data
; ============================================================================
Start := A_TickCount
DB.Exec("BEGIN TRANSACTION;")
SQLStr := ""
_SQL := ""
ENG := ""
PER := ""
lpcounter := Result.RowCount
Loop %lpcounter% {
ENG := Result.Rows[A_Index, 1]
PER := Result.Rows[A_Index, 2]
;;; do some more word processing later
_SQL := "INSERT INTO MyPreparation VALUES('" . A_Index . "', '" . ENG . "', '" . PER . "');"
   SQLStr .= _SQL
}

If !DB.Exec(SQLStr)
   MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
DB.Exec("COMMIT TRANSACTION;")
SQLStr := ""
ExecutionTime := "Done in " . (A_TickCount - Start) . " ms"
MsgBox, % ExecutionTime
Sleep, 1000
; =============================================================================
db.CloseDB()
just me
Posts: 5515
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2016-03-28

28 Dec 2017, 04:07

The single quote ' has to be 'escaped' to '' in quoted values. I don't know if there are other characters which need to be escaped. You can call MyDB.EscapeStr() for each variable or try to use StrReplace(Var, "'", "''") on each variable which might be faster.
Winfried
Posts: 2
Joined: 19 Jan 2018, 22:49

Re: [Class] SQLiteDB - Update on 2016-03-28

19 Jan 2018, 23:01

Hello,

I'm sorry for the newbie question, but I'm only getting started with AHK.

The script does work to create a new database and insert basic values, but I don't know how to have it read data from variables:

Code: Select all

#Include Class_SQLiteDB.ahk

MyDB := New SQLiteDB
MyDB.OpenDB("dummy.sqlite")

MyDB.Exec("CREATE TABLE IF NOT EXISTS MyTable (id INTEGER, title TEXT, notes TEXT)")

Dummy := Test

;OK
MyDB.Exec("INSERT INTO MyTable VALUES (NULL, 'MyTitle', 'MyNotes');")
;NOT REPLACED
MyDB.Exec("INSERT INTO MyTable VALUES (NULL, '%Dummy%', NULL);")
;NOT REPLACED
MyDB.Exec("INSERT INTO MyTable VALUES (NULL, %Dummy%, NULL);")

MyDB.CloseDB()
Thank you.
just me
Posts: 5515
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2016-03-28

20 Jan 2018, 02:32

You cannot use variable references within strings.

Code: Select all

MyDB.Exec("INSERT INTO MyTable VALUES (NULL, '%Dummy%', NULL);")
;must be
MyDB.Exec("INSERT INTO MyTable VALUES (NULL, '" . Dummy . "', NULL);")
Winfried
Posts: 2
Joined: 19 Jan 2018, 22:49

Re: [Class] SQLiteDB - Update on 2016-03-28

21 Jan 2018, 11:53

Thank you.
ahklearner
Posts: 283
Joined: 23 Jan 2015, 01:49

Re: [Class] SQLiteDB - Update on 2016-03-28

25 Jan 2018, 05:02

Hi All,

I was wondering how to use the "mode html"
DB.Exec("MODE html") ?

and how to capture its recordset
DB.GetTable(SQL, Result) ?

Thanks in advance for your help and support.

Return to “Scripts and Functions”

Who is online

Users browsing this forum: nilsonrdg, swagfag, TAC109 and 25 guests