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

Post your working scripts, libraries and tools
just me
Posts: 5707
Joined: 02 Oct 2013, 08:51
Location: Germany

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

25 Jan 2018, 09:06

Do you mean the .mode command of SQLite3.exe? AFAIK it's not available within the C\C++ API.
ahklearner
Posts: 285
Joined: 23 Jan 2015, 01:49

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

29 Jan 2018, 03:23

just me wrote:Do you mean the .mode command of SQLite3.exe? AFAIK it's not available within the C\C++ API.
Do you know the other way to achieve this ?
just me
Posts: 5707
Joined: 02 Oct 2013, 08:51
Location: Germany

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

29 Jan 2018, 04:05

If you don't want to use SQLite3.exe, you have to add the HTML by yourself.
ahklearner
Posts: 285
Joined: 23 Jan 2015, 01:49

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

30 Jan 2018, 03:34

just me wrote:If you don't want to use SQLite3.exe, you have to add the HTML by yourself.
Thanks for your time.
AHKStudent
Posts: 332
Joined: 05 May 2018, 12:23

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

10 May 2018, 12:11

How would I go about storing a date in the database so later I can do queries from date A to date B

What data type do I use? How do I do this (obviously I can generate the current date using ahk formattime)
Qriist
Posts: 13
Joined: 11 Sep 2016, 04:02

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

01 Sep 2018, 13:28

Hello, thank your for your class.

I'm still kind of new to SQLite. I have made a script that is compiling a bunch of non-sql data into a single database. My question is, how do I queue a bunch of INSERTS so that it will operate on MANY rows at once? After some extreme performance issues (talking 3hours or so for a fraction of the inserts required) I opted to create a bulkload text that sqlite3.exe reads into the db - but I know this is suboptimal, even if it performs all of the inserts in a few seconds.
just me
Posts: 5707
Joined: 02 Oct 2013, 08:51
Location: Germany

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

02 Sep 2018, 03:06

Hi,

you might want to use a transaction

Code: Select all

DB.Exec("BEGIN TRANSACTION;")
... insert statements
... insert statements
... insert statements
DB.Exec("COMMIT TRANSACTION;")
and to concatenate several SQL statements as shown in SQLiteDB_sample.ahk
User avatar
Joe Glines
Posts: 574
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

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

21 Nov 2018, 12:03

We had a great webinar yesterday covering this topic. Jean Lalonde, author of Quick Access Popup, led us on an intro to using this class. Check out the videos & resources shared here.
hasantr
Posts: 95
Joined: 05 Apr 2016, 14:18

Re: [Class] SQLiteDB

09 Dec 2018, 12:16

Jeramy wrote:
27 Sep 2015, 06:46
As an added bonus, here's another snippet that enables you to look up all the tables on a database, so you could apply needed changes across any/all tables across the DB in one shot

Code: Select all

#NoEnv
#SingleInstance force
SetWorkingDir, %A_ScriptDir%
SetBatchLines, -1
#Include Class_SQLiteDB.ahk
db := new SQLiteDB
DBLoc = %A_Scriptdir%\SQLiteDB.sqlite ; Change to match the name of your database as needed
db.OpenDB(DBLoc)
TableLookup := "select name from sqlite_master where type='table';" ; This will produce a list of all tables in the Database
db.GetTable(TableLookup, results)
loop % Results.RowCount
{
I := a_index ; Set the row value for each row loop
     loop % Results.ColumnCount
          {
          msgbox % Results.Rows[I, a_index] ; show the name of each table in the database
          }
}
db.CloseDB()
Thanks. I could never understand the other examples.
hyaray
Posts: 8
Joined: 20 Jun 2015, 01:37

Re: [Class] SQLiteDB

29 Dec 2018, 23:38

arcticir wrote:
20 Dec 2013, 04:00
Thanks. I will transfer to V2.
I used to add two functions make it more easy to use:

Code: Select all

Class_SQLiteDB(){
	Return New SQLiteDB
}

SQLite_(v){
	Return InStr(v, "`'") ? StrReplace(v,"`'","`'`'") : v
}
Have you transfer it to v2? I'm looking for it, thank you!! :bravo: :bravo:
AHKStudent
Posts: 332
Joined: 05 May 2018, 12:23

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

07 Jan 2019, 04:30

How do I delete a specific row?

MyRow := "15"
I tried MyDB.Exec("Delete from Listings WHERE rowid = '" . MyRow . "';")
IMEime
Posts: 538
Joined: 20 Sep 2014, 06:15

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

07 Jan 2019, 08:01

Looks good.
I'll try.

Long time ago, I have tested with "IsNull"'s code.
It was good, I remember.

regards
AHKStudent
Posts: 332
Joined: 05 May 2018, 12:23

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

07 Jan 2019, 09:53

IMEime wrote:
07 Jan 2019, 08:01
Looks good.
I'll try.

Long time ago, I have tested with "IsNull"'s code.
It was good, I remember.

regards
not working for me
IMEime
Posts: 538
Joined: 20 Sep 2014, 06:15

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

07 Jan 2019, 10:04

@AHKStudent
I am a beginner too
I just got something.
Can you tell me what you did so far ?
IMEime
Posts: 538
Joined: 20 Sep 2014, 06:15

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

07 Jan 2019, 10:07

Dear, expert(s)

Can you tell me how can I use column name ?
I can iterate table with two indexes, but I know columns name exactly.
I do not want to use numbers.
IMEime
Posts: 538
Joined: 20 Sep 2014, 06:15

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

07 Jan 2019, 10:09

Code: Select all

For Each, x In myTable.ColumnNames         
	myResults .= x "`n"
id
guid
translation_memory_id
source_hash
source_segment
target_hash
target_segment
creation_date
creation_user
,,,
,,
,
so, I'd like to know the value of the 5th and 7th cell. (source_segment column and target_segment column)
I do not know how to do it.
I have limited my Rows just "1" (LIMIT command)
Last edited by IMEime on 07 Jan 2019, 10:22, edited 1 time in total.
AHKStudent
Posts: 332
Joined: 05 May 2018, 12:23

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

07 Jan 2019, 10:16

IMEime wrote:
07 Jan 2019, 10:04
@AHKStudent
I am a beginner too
I just got something.
Can you tell me what you did so far ?
Hi, I posted what I tried, hoping someone can share with us the syntax for deleting a row by its ROWID
IMEime
Posts: 538
Joined: 20 Sep 2014, 06:15

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

07 Jan 2019, 10:19

Code: Select all

Loop % myTable.RowCount    ;  This works fine but I want to make same result with "Name"
{
	i := A_Index 									
	Loop % myTable.ColumnCount
	{
		If (A_Index = 5  || A_Index = 7)
		myResults1 .= myTable.Rows[i, A_Index] "`n`n"
	}
}

For Each, x In myTable.ColumnNames        
{
	myResults2 .= x "`n"
	; I want to do something proper
	; myResults3 .=......
}
IMEime
Posts: 538
Joined: 20 Sep 2014, 06:15

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

07 Jan 2019, 10:21

@AHKStudent
Do you want to delete 15th row ?
Last edited by IMEime on 07 Jan 2019, 10:23, edited 1 time in total.

Return to “Scripts and Functions”

Who is online

Users browsing this forum: cocobanana, Google [Bot], ziyujohnson and 32 guests