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

[Gh0sTG0]

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.

[kczx3]

You need to escape any single quotes in the html. I’m guess there’s an apostrophe

[Gh0sTG0]

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

It still errors.
Now in msgbox I can see this:

`"sometext`"....`"somemoretext`"

But then another msgbox appears with:

Msg: near "s": syntax error
Code: 1

PS It can be %s..., but I can't stringreplace it, script don't start with

Code: Select all

StringReplace, texthtml,texthtml, `%,  ``%, All

UPD: I changed that code from full page to parts that I need. Now I have this:

Msg: near "https": syntax error
Code: 1

I set to clipboard := that what I try to send. That is what I have around any https part:

Code: Select all

url=`"https`:`/`/site.com

What's not correct in this..?

Upd2: find place where was not correct with that https part.
Now have another problem:

I`'ve tested

Why it shows me error on "syntax error near "ve"? Isn't that ' near ve escaped?
PS no ' in text works...

Code: Select all

StringReplace, texthtml,texthtml, `',  _, All

But why it not escaped?

Upd3:
Looks like

Code: Select all

StringReplace, texthtml, texthtml, ', ' ', All ;without space between '

Works for me

[robodesign]

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.

[just me]

@robodesign : Fixed (hopefully)!

[robodesign]

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

[just me]

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

[hasantr]

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]

@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)
}

[hasantr]

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

[JnLlnd]

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.

[just me]

@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")
   }

[JnLlnd]

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

[robodesign]

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.

[kczx3]

When setting free records.

Can you elaborate on what this means?

[robodesign]

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.

[kczx3]

It doesn't crash when just a single instance of your script is running?

[robodesign]

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.

[robodesign]

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

Best regards, Marius.

[kczx3]

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.