[Class] SQLiteDB - Update on 2022-10-04

[just me]

Do you mean the .mode command of SQLite3.exe? AFAIK it's not available within the C\C++ API.

[ahklearner]

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]

If you don't want to use SQLite3.exe, you have to add the HTML by yourself.

[ahklearner]

If you don't want to use SQLite3.exe, you have to add the HTML by yourself.

Thanks for your time.

[AHKStudent]

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)

[just me]

Datatypes In SQLite Version 3 + Date And Time Functions

[Qriist]

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]

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

[Joe Glines]

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]

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]

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!!

[AHKStudent]

How do I delete a specific row?

MyRow := "15"
I tried MyDB.Exec("Delete from Listings WHERE rowid = '" . MyRow . "';")

[IMEime]

Looks good.
I'll try.

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

regards

[AHKStudent]

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]

@AHKStudent
I am a beginner too
I just got something.
Can you tell me what you did so far ?

[IMEime]

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]

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)

[AHKStudent]

@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]

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]

@AHKStudent
Do you want to delete 15th row ?