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

[IMEime]

I spent several hours
Most of them are related with "Callback Function" of this post

I guess, this is different from so called "Function".. Right ??

the Callback Function's return should be "0"
If not, it ruins everything . All are finished at that moment.
This is not the function should be, I guess.

[just me]

the Callback Function's return should be always "0"
If not, it ruins everything . All are finished at that moment.

That's exactly the documented behaviour:

If an sqlite3_exec() callback returns non-zero, the sqlite3_exec() routine returns SQLITE_ABORT without invoking the callback again and without running any subsequent SQL statements.

[IMEime]

@just me
Hi
Really Really Good to see you !
I appreciated your efforts.

The above comment is not bad talking.
Just write again my understanding. I have read it from your definition.

[IMEime]

1. REGEXP is not working, at least to me
2. REGEXP is good but, I guess, my final goal should be custom function
Can I know about these things ?

[IMEime]

I am sorry to post this kind of comment though (it is C#) and its language (it is Korean).
This is one of kind I aimed;

Code: Select all

쿼리 = "CREATE TABLE 메모리.임시(색인 INT, 소스 TEXT, 타겟 TEXT, 경로 TEXT, 소스축약 TEXT);";
쿼리 += "INSERT INTO 메모리.임시(색인, 소스, 타겟) SELECT id, 알맹이만(source_segment), 알맹이만(target_segment) FROM '" + 티엠이름만 + "'.translation_units;";
쿼리 += "UPDATE 임시 SET 경로 = '" + 티엠풀패쓰 + "';";
쿼리 += "UPDATE 임시 SET 소스축약 = 다날려라1(소스);";
쿼리 += "CREATE TABLE IF NOT EXISTS 누적(색인 INT, 소스 TEXT, 타겟 TEXT, 경로 TEXT, 소스축약 TEXT);";
쿼리 += "INSERT INTO 누적 SELECT * FROM 임시";
new SQLiteCommand(쿼리, 커넥션).ExecuteNonQuery();

I'd like to make it in AHK

[IMEime]

and then process this step;

Code: Select all

"SELECT * FROM 누적 WHERE 소스축약 IN (SELECT 소스축약 FROM 누적 GROUP BY 소스축약 HAVING COUNT(*) > 1) ORDER BY 소스축약 COLLATE NOCASE, 타겟 COLLATE NOCASE";

And some more minor stuffs waiting.
Then done.

So.. I think If I could manage my function, it will be possible.
Of course, there should be unexpected walls and traps are waiting for me...

[kczx3]

Can you maybe open a different thread in the Ask for Help forum? This thread is becoming quite cluttered with all your posts, and frankly, they don’t seem to have much purpose.

[IMEime]

@kcxz3
Who are you talking to ?
The owener this post ?
Me ?
Somebody else ?

All of I mentioned have their own purpose a lot and clearly.
You seem to have not much purpose.

[kczx3]

You

[IMEime]

You are not smart and importantly, Rude/Dirty.

I have already posted a question at Help section with the same contents here of mine.
Considering attitude problem member(s) just like YOU.
But, nobody answered and matters including the owner of this post and moderator(s).
After that post of Help. The owner of this post continued to answer to my question.
So, I thought I have made double posts and deleted one in the Help section.

If you do not have anything to answer my question.
Just keep quiet and see what happens here.

My question is very simple and natural for this kind of section and post.

I would like to know How to use custom functions.

OK ?

[AHKStudent]

Is it possible to read blob data that is stored using DES encryption?

Does anyone have a sample of this?

[just me]

Hi!

@AHKStudent:
There's a BLOB_sample.ahk script on GitHub, shoing how you can write and read BLOBs. It will store and read encrypted binary data. I don't know how to use DES de/encryption on the data.

@IMEime:
I don't think it's feasible to create own SQLite functions with AHK.

@All:
It's not the purpose of this thread to answer 'Ask For Help' topics (even if I do it quite often).

[AHKStudent]

I tried that sample but in return I got this square box as the results? If I get the data I can decrypt it, its just getting the data.

I added to that sample the line

TheBlob := Row[A_Index].Blob

Code: Select all

 Loop, % RecordSet.ColumnCount {
      If IsObject(Row[A_Index]) {
         Size := Row[A_Index].Size
         Addr := Row[A_Index].GetAddress("Blob")
		 TheBlob := Row[A_Index].Blob
         If !(Addr) || !(Size) {
            MsgBox, 0, Error, BlobAddr = %Addr% - BlobSize = %Size%
         } Else {
            VarSetCapacity(MyBLOBVar, Size) ; added
            DllCall("Kernel32.dll\RtlMoveMemory", "Ptr", &MyBLOBVar, "Ptr", Addr, "Ptr", Size) ; added
         }
      }
   }
}

[just me]

If everything went right when you stored the BLOB the query will return an AHK object with the keys Size and Blob. Size contains the length of the BLOB data in bytes. BLOB contains the raw binary data, encrypted in this case. So you find the data in Row[A_Index].Blob. Because they are raw binary data, you cannot access them directly with most of the AHK commands.

[AHKStudent]

"Because they are raw binary data, you cannot access them directly with most of the AHK commands."

Now everything makes sense, thank you

[IMEime]

> @IMEime:
> I don't think it's feasible to create own SQLite functions with AHK.

Big Thanks for the reply.
Then, In my case, AHK_SQLite is useless.
Regards

[just me]

Then, In my case, AHK_SQLite is useless.

Interesting! Why do you think that you need user-defined SQLite functions?

[IMEime]

Then, In my case, AHK_SQLite is useless.

Interesting! Why do you think that you need user-defined SQLite functions?

I have a simple,
very simple reason.
"Regular Expressions"
I have to use RegExps.

I do not need a "Computer Language/Tool" which is incapable of RegExp.

Regards

[just me]

@IMEime:
I don't think it's feasible to create own SQLite functions with AHK.

but seemingly I was wrong.

Necessary steps:

1. Write the user function as a normal AHK function:

   Code: Select all

   SQLite3_RegExp(Context, ArgC, Values) {
      Static Count := 0
      Count++
      ToolTip, Count
      DllCall("SQLite3.dll\sqlite3_result_int", "Ptr", Context, "Int", Count & 1, "Cdecl") ; 0 = false, 1 = true
   }

2. Get a callback pointer for this function:

   Code: Select all

   RegExp := RegisterCallback("SQLite3_RegExp")

3. Open a database:

   Code: Select all

   DBFileName := "Test.db"
   DB := New SQLiteDB
   If !DB.OpenDB(DBFileName) {
      MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
      ExitApp
   }

4. Register the user function:

   Code: Select all

   SQLite3_Create_Function(DB, "regexp", 2, RegExp)

   Required additional function:

   Code: Select all

   SQLite3_Create_Function(DB, Name, Args, Func, Enc := 0x0801, Param := 0) {
      ; SQLITE_DETERMINISTIC = 0x0800 - the function will always return the same result given the same inputs within a single SQL statement
      ; SQLITE_UTF8 = 0x0001
      Return DllCall("SQLite3.dll\sqlite3_create_function", "Ptr", DB._Handle, "AStr", Name, "Int", Args, "Int", Enc
                                                          , "Ptr", Param, "Ptr", Func, "Ptr", 0, "Ptr", 0, "Cdecl Int")
   }

5. Now you can use the function in SQL statements. In case of the 'regexp' function:

   Code: Select all

   SQL := "SELECT * FROM test WHERE name REGEXP '^A';"

If you find that this approach is working flawlessly, please let me know. I'll consider to add the function to the class, then.

[IMEime]

Woooo.. Thanks a LOT !!

I will check it out for sure.

Before that.
Can you tell me two things ?
-I am sorry, I am not good at this kind of 'very difficult jobs' .

1. Is this a 'Custom Functions' or just 'Regular Expression Functions' ?
2. If it is the latter case (I prefer to the former though), which is 'Haystack' and which is 'NeedleRegEx' ?
(2'. If it is the former case, How can I use a field(s) of DB as an argument(s) of my function ?)

I really appreciated your time and efforts.

Regards