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.
[Class] SQLiteDB - Update on 2022-10-04
Re: [Class] SQLiteDB - Update on 2016-03-28
Typo?m3user wrote:... I have a MySQL file, ...
Re: [Class] SQLiteDB - Update on 2016-03-28
yes, there are many database files (tables), I would like to edit fields in one of them. Hope this makes sense.
Re: [Class] SQLiteDB - Update on 2016-03-28
SQL As Understood By SQLite
You might try the following with a copy of your DB file:
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.
Re: [Class] SQLiteDB - Update on 2016-03-28
Thanks, very kind. I'll try that.
Re: [Class] SQLiteDB - Update on 2016-03-28
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
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] v2.0.5 | [WIN] 11 Pro (Version 22H2) | [GitHub] Profile
Re: [Class] SQLiteDB - Update on 2016-03-28
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
Botan, BSD licensed crypto lib
Botan SQLite3 encryption coded
I haven't tried Botan, though.
Regards,
burque505
Re: [Class] SQLiteDB - Update on 2016-03-28
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
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.
Re: [Class] SQLiteDB - Update on 2016-03-28
You mean DEcrypt?
Windows 10 x64 Professional, Intel i5-8500, NVIDIA GTX 1060 6GB, 2x16GB Kingston FURY Beast - DDR4 3200 MHz | [About Me] | [About the AHK Foundation] | [Courses on AutoHotkey]
[ASPDM - StdLib Distribution] | [Qonsole - Quake-like console emulator] | [LibCon - Autohotkey Console Library]
Re: [Class] SQLiteDB - Update on 2016-03-28
This is awesome. Thanks just me, great work!
Re: [Class] SQLiteDB - Update on 2016-03-28
Yes, sorry)joedf wrote:You mean DEcrypt?
Re: [Class] SQLiteDB - Update on 2016-03-28
Do you have the password?
Windows 10 x64 Professional, Intel i5-8500, NVIDIA GTX 1060 6GB, 2x16GB Kingston FURY Beast - DDR4 3200 MHz | [About Me] | [About the AHK Foundation] | [Courses on AutoHotkey]
[ASPDM - StdLib Distribution] | [Qonsole - Quake-like console emulator] | [LibCon - Autohotkey Console Library]
Re: [Class] SQLiteDB - Update on 2016-03-28
What password? Why is it needed?joedf wrote:Do you have the password?
Re: [Class] SQLiteDB - Update on 2016-03-28
You want to brute force it? Is this your database?
Encryption usually requires a key
Encryption usually requires a key
Windows 10 x64 Professional, Intel i5-8500, NVIDIA GTX 1060 6GB, 2x16GB Kingston FURY Beast - DDR4 3200 MHz | [About Me] | [About the AHK Foundation] | [Courses on AutoHotkey]
[ASPDM - StdLib Distribution] | [Qonsole - Quake-like console emulator] | [LibCon - Autohotkey Console Library]
-
- Posts: 74
- Joined: 18 May 2017, 10:56
Re: [Class] SQLiteDB - Update on 2016-03-28
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()
Re: [Class] SQLiteDB - Update on 2016-03-28
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.
Re: [Class] SQLiteDB - Update on 2016-03-28
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:
Thank you.
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()
Re: [Class] SQLiteDB - Update on 2016-03-28
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);")
Re: [Class] SQLiteDB - Update on 2016-03-28
Thank you.
-
- Posts: 313
- Joined: 23 Jan 2015, 01:49
Re: [Class] SQLiteDB - Update on 2016-03-28
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.
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 (v1)”
Who is online
Users browsing this forum: No registered users and 96 guests