[Class] SQLiteDB - Update on 2019-12-12

Post your working scripts, libraries and tools
Gh0sTG0
Posts: 20
Joined: 25 Jun 2018, 07:58

Re: [Class] SQLiteDB - Update on 2019-07-12

12 Nov 2019, 16:41

Nice thing. Thank you.
Maybe I'm abit blind and didn't find that info, but How can I insert random parced html page code? I have some errors trying to. (parcing it from chrome via chrome.ahk)

Code: Select all

	texthtml := PageInst.Evaluate("document.body.innerHTML;").Value ;I will mod this into correct parts later, but for testing...

	MsgBox, %texthtml% ;gives me correct text to msgbox.

	SQL := "INSERT INTO toCheck VALUES('" . texthtml . "', 0);"
	If !DB.Exec(SQL)
		MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
For example I used it on this page and it gives me:
Msg: near "s": syntax error
Code: 1
PS It will be nice if I can use that saved code back as html page later.
User avatar
kczx3
Posts: 1255
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-07-12

12 Nov 2019, 18:30

You need to escape any single quotes in the html. I’m guess there’s an apostrophe
Gh0sTG0
Posts: 20
Joined: 25 Jun 2018, 07:58

Re: [Class] SQLiteDB - Update on 2019-07-12

13 Nov 2019, 03:01

kczx3 wrote:
12 Nov 2019, 18:30
You need to escape any single quotes in the html. I’m guess there’s an apostrophe
That not works. =(
I added this:

Code: Select all

StringReplace, texthtml,texthtml, `',  ``', All
Spoiler
Upd3:
Looks like

Code: Select all

StringReplace, texthtml, texthtml, ', ' ', All ;without space between '
Works for me
robodesign
Posts: 745
Joined: 30 Sep 2017, 03:59
Facebook: marius.sucan
GitHub: mariussucan
Location: Romania
Contact:

Re: [Class] SQLiteDB - Update on 2019-07-12

11 Dec 2019, 11:39

Hello!

I am trying this Class and I found that if I use EscapeStr, it only returns a % whatever I feed it to, the return value is 1 [true]. Seems to be broken?...

I am trying to create a table with a large number of folders and file names.... and for some weirdly named files I get syntax error on Insert SQL exec.... therefore I assumed it is about escaping characters . I found the function in the class that ought to do it, but... something is broken. Can anyone try it also?

Thank you .

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.
just me
Posts: 7748
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2019-12-12

12 Dec 2019, 07:06

@robodesign : Fixed (hopefully)!
robodesign
Posts: 745
Joined: 30 Sep 2017, 03:59
Facebook: marius.sucan
GitHub: mariussucan
Location: Romania
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

12 Dec 2019, 16:55

just me wrote:
12 Dec 2019, 07:06
@robodesign : Fixed (hopefully)!
It works. Thank you.

Silly question... Can I create and work with tables with columns other than string data type? Like integer and other data types.. I need to set /retrieve such data types in my project.

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.
just me
Posts: 7748
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2019-12-12

13 Dec 2019, 09:57

SQLite basically supports 'typed' columns: What datatypes does SQLite support?

But:
Manifest typing

Most SQL database engines use static typing. A datatype is associated with each column in a table and only values of that particular datatype are allowed to be stored in that column. SQLite relaxes this restriction by using manifest typing. In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column. (There are some exceptions to this rule: An INTEGER PRIMARY KEY column may only store integers. And SQLite attempts to coerce values into the declared datatype of the column when it can.)

As far as we can tell, the SQL language specification allows the use of manifest typing. Nevertheless, most other SQL database engines are statically typed and so some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use, especially when used in combination with dynamically typed programming languages such as Tcl and Python.

Source
User avatar
hasantr
Posts: 781
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: [Class] SQLiteDB - Update on 2019-12-12

14 Oct 2020, 01:51

Hello. How can I get the result with an Object instead of Array? Or is it already a prose? Is there an array in the object?
Qriist
Posts: 41
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2019-12-12

15 Oct 2020, 12:59

@hasantr the array IS an object.

You can use the following code to visualize the data:

Code: Select all

DB := new SQLiteDB
DB.openDB(path_to_your_database)

DB.GetTable(your_sql_query, output_table)

;view entire result data including the "administrative" info
msgbox % st_printarr(output_table)

;view one row at a time
Loop, % table["RowCount"]		;iterates exactly as many times as there are rows
{
	table.next(row)		;grabs the next row of data
	msgbox % st_printarr(row)
}

st_printArr(array, depth=5, indentLevel="")
{
	for k,v in Array
	{
		list.= indentLevel "[" k "]"
		if (IsObject(v) && depth>1)
			list.="`n" st_printArr(v, depth-1, indentLevel . "    ")
		Else
			list.=" => " v
		list.="`n"
	}
	return rtrim(list)
}
User avatar
hasantr
Posts: 781
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: [Class] SQLiteDB - Update on 2019-12-12

16 Oct 2020, 01:00

Qriist wrote:
15 Oct 2020, 12:59
@hasantr the array IS an object.

You can use the following code to visualize the data:

Code: Select all

DB := new SQLiteDB
DB.openDB(path_to_your_database)

DB.GetTable(your_sql_query, output_table)

;view entire result data including the "administrative" info
msgbox % st_printarr(output_table)

;view one row at a time
Loop, % table["RowCount"]		;iterates exactly as many times as there are rows
{
	table.next(row)		;grabs the next row of data
	msgbox % st_printarr(row)
}

st_printArr(array, depth=5, indentLevel="")
{
	for k,v in Array
	{
		list.= indentLevel "[" k "]"
		if (IsObject(v) && depth>1)
			list.="`n" st_printArr(v, depth-1, indentLevel . "    ")
		Else
			list.=" => " v
		list.="`n"
	}
	return rtrim(list)
}
Thank you Qriist.
User avatar
JnLlnd
Posts: 327
Joined: 29 Sep 2013, 21:29
GitHub: JnLlnd
Location: Montreal, Quebec, Canada
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

10 Nov 2020, 09:33

The SQLite doc about error mention the availability of extended error codes: https://www.sqlite.org/rescode.html#extrc

I found no references to these error codes in the SQLiteDB class code or in this thread. Is this somehow available? If not, could it be in a future version of the class? It could help find the cause for an error 10.
(10) SQLITE_IOERR
The SQLITE_IOERR result code says that the operation could not finish because the operating system reported an I/O error.

A full disk drive will normally give an SQLITE_FULL error rather than an SQLITE_IOERR error.

There are many different extended result codes for I/O errors that identify the specific I/O operation that failed.
https://www.sqlite.org/rescode.html#ioerr

Thanks.
:thumbup: Author of Quick Access Popup, the powerful Windows folders, apps and documents launcher!
:P Quick Access Popup Knowledge base
:ugeek: The Automator's Courses on AutoHotkey
just me
Posts: 7748
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2019-12-12

10 Nov 2020, 12:04

@JnLlnd: You might try to add the following method to the class:

Code: Select all

   ; ===================================================================================================================
   ; METHOD ExtErrCode     Gets the extended result code in case of errors.
   ; Parameters:           None.
   ; Return values:        On success  - Extended result code
   ;                       On failure  - 0
   ; Remarks:              Extended result code list -> https://www.sqlite.org/rescode.html#extrc
   ; ===================================================================================================================
   ExtErrCode() {
      If !(This._Handle)
         Return 0
      Return DllCall("SQLite3.dll\sqlite3_extended_errcode", "Ptr", This._Handle, "Cdecl Int")
   }
User avatar
JnLlnd
Posts: 327
Joined: 29 Sep 2013, 21:29
GitHub: JnLlnd
Location: Montreal, Quebec, Canada
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

10 Nov 2020, 15:43

Thanks just me for the quick reply.

The error occurred on the system of one of my client. I could not test this new code with a "real life" error right now. But I'll add it in a future release of my app and we'll see if user reporting the a similar error would have more info from the ExtErrCode() function.

For now, looking at other diag info I got from the client, it really looks like an error caused by a disk full.

Jean
:thumbup: Author of Quick Access Popup, the powerful Windows folders, apps and documents launcher!
:P Quick Access Popup Knowledge base
:ugeek: The Automator's Courses on AutoHotkey
robodesign
Posts: 745
Joined: 30 Sep 2017, 03:59
Facebook: marius.sucan
GitHub: mariussucan
Location: Romania
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

20 Nov 2020, 14:08

Hello!

I get a crash on line:

Code: Select all

         RC := DllCall("SQlite3.dll\sqlite3_finalize", "Ptr", This._Handle, "Cdecl Int")
When setting free records.

I am using multiple instances of the same script, all open the database using Read access only. Any ideas how to go about this? I am trying to collect tons of data from a given table, all in the same database, using multiple scripts [instances] ...

Thank you .

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.
User avatar
kczx3
Posts: 1255
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-12-12

20 Nov 2020, 15:58

robodesign wrote:
20 Nov 2020, 14:08
When setting free records.
Can you elaborate on what this means?
robodesign
Posts: 745
Joined: 30 Sep 2017, 03:59
Facebook: marius.sucan
GitHub: mariussucan
Location: Romania
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

20 Nov 2020, 16:01

kczx3 wrote:
20 Nov 2020, 15:58
robodesign wrote:
20 Nov 2020, 14:08
When setting free records.
Can you elaborate on what this means?
When I use Free() from _RecordSet() class like this: RecordSet.Free()... The various instances I spawn crash. The error message indicates the line of the already mentioned DLL Call.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.
User avatar
kczx3
Posts: 1255
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-12-12

20 Nov 2020, 19:52

It doesn't crash when just a single instance of your script is running?
robodesign
Posts: 745
Joined: 30 Sep 2017, 03:59
Facebook: marius.sucan
GitHub: mariussucan
Location: Romania
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

21 Nov 2020, 03:36

kczx3 wrote:
20 Nov 2020, 19:52
It doesn't crash when just a single instance of your script is running?
Yes. It doesn't crash.

I suspect it's when many concomitent query reads occur on the table.

Any suggestions?

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.
robodesign
Posts: 745
Joined: 30 Sep 2017, 03:59
Facebook: marius.sucan
GitHub: mariussucan
Location: Romania
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

21 Nov 2020, 11:06

This is of relevance https://sqlite.org/threadsafe.html

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.
User avatar
kczx3
Posts: 1255
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-12-12

21 Nov 2020, 12:03

I don’t think that applies. First, connections to the same database by different AHK processes is not the same as multiple connections across threads. Second, it looks as though SQLite is compiled in a thread safe manner by default.

Return to “Scripts and Functions”

Who is online

Users browsing this forum: No registered users and 22 guests