Jump to content

speed up order by int DESC, text ASC;


Recommended Posts

I believe you try another path: derive modified versions of _SQLite_GetTable2d and _SQLite_FetchData into a single, streamlined function to perform the json formatting right from the output of SQLite. At that moment you can take care of sorting out correctly strings vs. numbers and keywords (null, true, false).

I recognize that current version of _SQLite_FetchData is deceptive as it formats everything in strings.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

... I was gonna use just what you said. That idea, is what brought _SQLite_FetchTypes() to life....

1 hour ago, jchd said:

I recognize that current version of _SQLite_FetchData is deceptive as it formats everything in strings.

..but then I thought that in the end, just like you said: "unsuspecting readers to fall into the trap", and that got me to search for more ( I could get me into a trap was one of my strong thoughts ).
...also found the use of json_object()

All this thanks to multiple tabs in the browser while testing different code on different PCs.
( I tend to act disorganized, but is all organized in my head while evaluating possibilities )

Since the JSON straight out of the query was fast, I disregarded modifying _SQLite_GetTable2d(), as adding more evaluation ( if, then , else, switch, etc. ) to the function would have made it slower.

So: speed vs. practical. I chose speed. But I guess I could put together the _SQLite_GetTableJson() and share it in the forum. But it would always return:
[{"name1":"value1"},{"name2":"value2"}]
as I can't imagine a more flexible output. I don't know. What do you think @jchd ?

Afterthought: 

; #FUNCTION# ====================================================================================================================
; Author ........: piccaso (Fida Florian), blink314
; Modified.......: jchd
; ===============================================================================================================================
Func _SQLite_GetTable2d( ...

...would you wanna have a go at it ?, ..if time permits, you are much more qualified than I am at this moment. You may take into consideration something I miss, like: should I take an INT type and return it as [{"name1":value1},{"name2":value2}] or [{"name1":"value1"},{"name2":"value2"}] ???, etc.

If you don't want to code it but feel it should exist, I'll code it, but again, you are more experienced.

Your move, what do you say :) 

Link to post
Share on other sites

I have a lot of pans on the fire, many urgent and time-killing. I also start to experience issues have Chinese-only electronic components delivered due to COVID19.

I'll try to code something when schedule allows.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites
  • 2 months later...

In the thread above a few people hit on the idea of synthesizing a separate column to be indexed, named like Date_and_Name-Sequence-Number. I see how this could improve the sort time on a large set of Selected records. The problem is the potential (read: likelihood) that the fake-indexed column will become wrong/out of date as compared with the real data. As in, you have introduced a new data-integrity nightmare.

That manual column now must be maintained across all rows having that Date / in that Sequence. Whenever the Date column is inserted or updated in any row having that Date, the entire set of records with that Date must be re-sequenced, or at least checked for the need to re-sequence. I can't believe there is no way to index the Date and Name columns to get the required sort performance, regardless of what database engine we're talking about.

However! I understand some bigger client-server SQL engines have this kind of feature built in. A 'virtual' column can be defined at the table level, where a synthesized column returns the result of an expression that evaluates other columns in that row. In this way, the column is kind of like a View, except it's at the Column level instead of at the Table level. This may or may not solve the slow-sort problem, but it would certainly avoid introducing a foreseeable data integrity problem.

Another exception would be the enterprise-level case where there are two sets of tables: one set optimized for normalization/efficient storage focused on integrity, and one set optimized for fast selecting and sorting for output purposes. One tradeoff is data staleness: your output is only as fresh as your periodic publish process, where the data is copied and de-normalized from the Storage database to the Reporting tables/database. From experience at this level of IT, we always had a third set of tables too, which were optimized for input: zero normalization, with intake processing to reject invalid data.
 

Link to post
Share on other sites

Maintaining extra indices (simple or complex) without jeopardizing data integrity is easily done with triggers. Or I might have misunderstood you.

Even SQLite offers indices on expression, partial indices, reindex table or a specific index, ...

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites
  • 2 weeks later...

Agree advanced indexing like you mention is first choice. For the rare cases that is not good enough, agree triggers can be a good solution. Disagree any of that is quite so easily done. If it were easy, then people would not be tempted to dream up their own (non-optimal) solutions. Cheers

Link to post
Share on other sites
  • 1 month later...

Thanks for the reminder!  Here you are (just baked, bugs lurking):

; returns the result of a select query as JSON string; @extended is row count
Func _SQLite_GetTableJSON($hDB, $sSQL)
    If __SQLite_hChk($hDB, 1) Then Return SetError(@error, 0, $SQLITE_MISUSE)
    Local $hQuery
    Local $r = _SQLite_Query($hDB, $sSQL, $hQuery)
    If @error Then Return SetError(2, @error, $r)
    If $r <> $SQLITE_OK Then
        __SQLite_ReportError($hDB, "_SQLite_GetTableJSON", $sSQL)
        _SQLite_QueryFinalize($hQuery)
        Return SetError(-1, 0, $r)
    EndIf
    Local $aDataNames
    $r = _SQLite_FetchNames($hQuery, $aDataNames)
    If @error Then
        $iError = @error
        _SQLite_QueryFinalize($hQuery)
        Return SetError(5, $iError, $r)
    EndIf
    $iColumns = UBound($aDataNames)
    If $iColumns <= 0 Then
        _SQLite_QueryFinalize($hQuery)
        Return SetError(-1, 0, $SQLITE_DONE)
    Else
        For $i = 0 To $iColumns - 1
            $aDataNames[$i] = _StringToJson($aDataNames[$i])
        Next
    EndIf
    Local $iRval_Step, $iError, $iRval_coltype, $sResult = '{"result":[', $Rval, $iRows
    While True
        $iRval_Step = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_step", "ptr", $hQuery)
        If @error Then
            $iError = @error
            _SQLite_QueryFinalize($hQuery)
            $sResult = ""
            Return SetError(3, $iError, $SQLITE_MISUSE) ; DllCall error
        EndIf
        Switch $iRval_Step[0]
            Case $SQLITE_ROW
                $iRows += 1
                $sResult &= "{"
                For $i = 0 To $iColumns - 1
                    $iRval_coltype = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_column_type", "ptr", $hQuery, "int", $i)
                    If @error Then Return SetError(4, @error, $SQLITE_MISUSE) ; DllCall error
                    $sResult &= $aDataNames[$i] & ':'
                    Switch $iRval_coltype[0]
                        Case $SQLITE_TYPE_INTEGER
                            $Rval = DllCall($__g_hDll_SQLite, "int64:cdecl", "sqlite3_column_int64", "ptr", $hQuery, "int", $i)
                            If @error Then Return SetError(3, @error, $SQLITE_MISUSE) ; DllCall error
                            $sResult &= $Rval[0]
                        Case $SQLITE_TYPE_TEXT
                            $Rval = DllCall($__g_hDll_SQLite, "wstr:cdecl", "sqlite3_column_text16", "ptr", $hQuery, "int", $i)
                            If @error Then Return SetError(3, @error, $SQLITE_MISUSE) ; DllCall error
                            $sResult &= _StringToJson($Rval[0])
                        Case $SQLITE_TYPE_FLOAT
                            $Rval = DllCall($__g_hDll_SQLite, "double:cdecl", "sqlite3_column_double", "ptr", $hQuery, "int", $i)
                            If @error Then Return SetError(3, @error, $SQLITE_MISUSE) ; DllCall error
                            $sResult &= $Rval[0]
                        Case $SQLITE_TYPE_NULL
                            $sResult &= "null"
                        Case Else       ; Blob or unknown datatype at date of writing: output hex as "0x0123456789ABCDEF..."
                            Local $vResult = DllCall($__g_hDll_SQLite, "ptr:cdecl", "sqlite3_column_blob", "ptr", $hQuery, "int", $i)
                            If @error Then Return SetError(6, @error, $SQLITE_MISUSE) ; DllCall error
                            Local $iColBytes = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_column_bytes", "ptr", $hQuery, "int", $i)
                            If @error Then Return SetError(5, @error, $SQLITE_MISUSE) ; DllCall error
                            Local $tResultStruct = DllStructCreate("byte[" & $iColBytes[0] & "]", $vResult[0])
                            $sResult &= '"' & Binary(DllStructGetData($tResultStruct, 1)) & '"'
                    EndSwitch
                    If $i < $iColumns - 1 Then $sResult &= ","
                Next
                $sResult &= "},"
            Case $SQLITE_DONE
                If $iRows Then $sResult = StringTrimRight($sResult, 1)
                $sResult &= "]}"
                ExitLoop
            Case Else
                _SQLite_QueryFinalize($hQuery)
                Return SetError(3, $iError, $iRval_Step[0])
        EndSwitch
    WEnd
    _SQLite_QueryFinalize($hQuery)
    Return SetError(0, $iRows, $sResult)
EndFunc   ;==>_SQLite_GetTableJSON


; escape characters in strings w.r.t all relevant RFCs. Check https://jsonformatter.curiousconcept.com/
Func _StringToJson($s)
    $s = StringRegExpReplace($s, '([\\/])', '\\$1') ; escape \ and / first
    $s = StringReplace($s, '"', '\""')              ; escape "
    $s = StringReplace($s, Chr(0x08), '\b')         ; escape backspace
    $s = StringReplace($s, Chr(0x0C), '\f')         ; escape formfeed
    $s = StringReplace($s, @TAB, '\t')              ; escape @TAB
    $s = StringReplace($s, @LF, '\n')               ; escape @LF
    $s = StringReplace($s, @CR, '\r')               ; escape @CR
    $s = Execute('"' & StringRegExpReplace($s, '([\x00-\x1F\x7F-\x9F])', '" & "\\u" & Hex(AscW("$1"), 4) & "') & '"')   ; escape other ctrl chars
    Return '"' & $s & '"'
EndFunc   ;==>_StringToJson

Example use (cw is an advanced consolewrite for UTF8 and more):

#include <SQLite.au3>

Const $SQLITE_DLL = "C:\SQLite\bin\sqlite3.dll" ;<-- Change to the location of your sqlite dll
;~ Const $SQLITE_DLL = "C:\SQLite\bin\system.data.sqlite.dll" ;<-- Change to the location of your sqlite dll

; Init sqlite
_SQLite_Startup($SQLITE_DLL, False, 1)
If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
ConsoleWrite("SQlite version " & _SQLite_LibVersion() & @LF & @LF)

Local $hDB = _SQLite_Open()
Local $sOut

_SQLite_Exec($hDB, "create table T (Id integer primary key, FirstName text, LastName text, Birth text, Death text)")
_SQLite_Exec($hDB, "insert into T values (10, 'Bohr', 'Niels', '1885/10/07', '1962/11/18')")
_SQLite_Exec($hDB, "insert into T values (11, 'Fermi', 'Enrico', '1901/09/29', '1954/11/28')")
_SQLite_Exec($hDB, "insert into T values (15, 'Albert', 'Einstein', '1879/03/14', '1955/04/18')")
_SQLite_Exec($hDB, "insert into T values (19, 'Rovelli', 'Carlo', '1956/05/03', null)")
_SQLite_Exec($hDB, "insert into T values (7, 'Schrödinger', 'Erwin', '1887/08/12', '1961/01/04')")
_SQLite_Exec($hDB, "insert into T values (39, 'Higgs', 'Peter', '1929/05/29', null)")


$sOut = _SQLite_GetTableJSON($hDB, "SELECT * from T")
cw(@extended & " row" & (@extended ? "s" : ""))
cw($sOut & @LF)

$sOut = _SQLite_GetTableJSON($hDB, "SELECT +555555555555555 col_int, null col_null, '' col_str0, 3.1415926 col_float, -0.00031415926e117 col_floatexp, cast('xyz' as blob) col_blob, 'Árvíztűrőñ tükörfúrógépçô ŵƂǚȨǽϋϔӪӢӂ' col_str")
cw(@extended & " row" & (@extended ? "s" : ""))
cw($sOut & @LF)

$sOut = _SQLite_GetTableJSON($hDB, "SELECT 'abc\" & Chr(0x0B) & "def'")
cw(@extended & " row" & (@extended ? "s" : ""))
cw($sOut & @LF)

$sOut = _SQLite_GetTableJSON($hDB, "SELECT null, 0, 'aa""bb' [abc" & @TAB & "def]")
cw(@extended & " row" & (@extended ? "s" : ""))
cw($sOut & @LF)

$sOut = _SQLite_GetTableJSON($hDB, "SELECT * from T where id = 0")
cw(@extended & " row" & (@extended ? "s" : ""))
cw($sOut)

_SQLite_Close($hDB)
_SQLite_Shutdown()

Giving:

SQlite version 3.32.3

6 rows
{"result":[{"Id":7,"FirstName":"Schrödinger","LastName":"Erwin","Birth":"1887\/08\/12","Death":"1961\/01\/04"},{"Id":10,"FirstName":"Bohr","LastName":"Niels","Birth":"1885\/10\/07","Death":"1962\/11\/18"},{"Id":11,"FirstName":"Fermi","LastName":"Enrico","Birth":"1901\/09\/29","Death":"1954\/11\/28"},{"Id":15,"FirstName":"Albert","LastName":"Einstein","Birth":"1879\/03\/14","Death":"1955\/04\/18"},{"Id":19,"FirstName":"Rovelli","LastName":"Carlo","Birth":"1956\/05\/03","Death":null},{"Id":39,"FirstName":"Higgs","LastName":"Peter","Birth":"1929\/05\/29","Death":null}]}

1 rows
{"result":[{"col_int":555555555555555,"col_null":null,"col_str0":"","col_float":3.1415926,"col_floatexp":-3.1415926e+113,"col_blob":"0x78797A","col_str":"Árvíztűrőñ tükörfúrógépçô ŵƂǚȨǽϋϔӪӢӂ"}]}

1 rows
{"result":[{"'abc\\\u000Bdef'":"abc\\\u000Bdef"}]}

1 rows
{"result":[{"null":null,"0":0,"abc\tdef":"aa\"bb"}]}

0 row
{"result":[]}

JSON doesn't support binary values; if fetched from a table, they are output as strings, e.g. "0x0123456789ABCDEF..."
OTOH, SQLite has no boolean datatype, so json true and false are never used.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

It's directly pruned from _SQLiteGetTable2d without the useless bells & whistles. I just forgot to handle an empty resultset properly, or at least didn't test that case even if I was thinking it was dealt with. So I have fixed both code, example and its result.

Barring gross mistake that should now work in all cases.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

Edit: the cloudflare thing for this forum went toast before I could finish updating my code yesterday.  Sorry again for posting unpolished code.

It should now be complete and json-compliant.  I refer to https://www.json.org/json-en.html and https://jsonformatter.curiousconcept.com/

Note that this version is more respectful of DB datatypes than is _SQLiteGetTable and friends.

Last point: test it extensively and report. It could be a useful addon to the SQLite UDF.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

Here's  complementary code to unescape JSON strings:

; unescape escape sequences in JSON strings
Func _JsonTextToString($s)
    $s = StringTrimLeft(StringTrimRight($s, 1), 1)
    $s = StringReplace($s, '\"', '"')               ; unescape "
    $s = StringReplace($s, '\b', Chr(0x08))         ; unescape backspace
    $s = StringReplace($s, '\f', Chr(0x0C))         ; unescape formfeed
    $s = StringReplace($s, '\t', @TAB)              ; unescape @TAB
    $s = StringReplace($s, '\n', @LF)               ; unescape @LF
    $s = StringReplace($s, '\r', @CR)               ; unescape @CR
    $s = Execute("'" & StringRegExpReplace($s, "\\u([[:xdigit:]]{4})", "' & ChrW(0x$1) & '") & "'") ; unescape other chars in hex form \uXXXX
    $s = StringRegExpReplace($s, '\\([\\/])', '$1') ; unescape \ and / last
    Return $s
EndFunc   ;==>_JsonTextToString


; escape characters in strings w.r.t all relevant RFCs. Check https://jsonformatter.curiousconcept.com/
Func _StringToJsonText($s)
    $s = StringRegExpReplace($s, '([\\/])', '\\$1') ; escape \ and / first
    $s = StringReplace($s, '"', '\""')              ; escape "
    $s = StringReplace($s, Chr(0x08), '\b')         ; escape backspace
    $s = StringReplace($s, Chr(0x0C), '\f')         ; escape formfeed
    $s = StringReplace($s, @TAB, '\t')              ; escape @TAB
    $s = StringReplace($s, @LF, '\n')               ; escape @LF
    $s = StringReplace($s, @CR, '\r')               ; escape @CR
    $s = Execute('"' & StringRegExpReplace($s, '([\x00-\x1F\x7F-\x9F])', '" & "\\u" & Hex(AscW("$1"), 4) & "') & '"')   ; escape other ctrl chars
    Return '"' & $s & '"'
EndFunc   ;==>_StringToJson

; unescape escape sequences in JSON strings
Func _JsonTextToString($s)
    $s = StringTrimLeft(StringTrimRight($s, 1), 1)
    $s = StringReplace($s, '\"', '"')               ; unescape "
    $s = StringReplace($s, '\b', Chr(0x08))         ; unescape backspace
    $s = StringReplace($s, '\f', Chr(0x0C))         ; unescape formfeed
    $s = StringReplace($s, '\t', @TAB)              ; unescape @TAB
    $s = StringReplace($s, '\n', @LF)               ; unescape @LF
    $s = StringReplace($s, '\r', @CR)               ; unescape @CR
    $s = Execute("'" & StringRegExpReplace($s, "\\u([[:xdigit:]]{4})", "' & ChrW(0x$1) & '") & "'") ; unescape other chars in hex form \uXXXX
    $s = StringRegExpReplace($s, '\\([\\/])', '$1') ; unescape \ and / last
    Return $s
EndFunc   ;==>_JsonTextToString

Escaping function named changed for symetry. Control chars appear in console if using a "good" font (e.g. DejaVu sans mono) in UTF8 mode.

Please test and report. Might end up in less confidential thread.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

@jchd impressive as always :)

Will these SQLiteJSON functions make it into the standard UDF? Should we keep these separate?

You really should make a SQLite sample code page and put all these there for ease of reference.

Thanks again

Skysnake

Skysnake

Why is the snake in the sky?

Link to post
Share on other sites

OK, then please vote for the "96h/day" bill next month, applicable to France.

Yes I think it'll be a valuable addon to the existing UDF.  Also it would be useful to plan a revised and faster GetData2D function which would deal with actual datatypes (NULLs), but without the extra features of the current functions (size limit, pivoting), which I doubt many people use.

I'd also wish I can update unifuzz to current Unicode and add/fix some functions there.

All of this needs time, sorry.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      This is modifed version of _sql.au3 UDF.
       
      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH
       
      This is first public release , and still is as BETA
       
       
      DOWNLOAD LINK (in download section): 
       
       
      Have fun,
      mLipok
       
       
      EDIT: 2016-06-03
      Below some interesting topics about databases:
       
       
      EDIT 2016/07/04:
      For more info about ADO look here:
      https://www.autoitscript.com/wiki/ADO
      FOR EXAMPLE DATABASE use AdventureWorksDW2016_EXT.bak from:
      https://github.com/microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2016_EXT.bak
      I will relay on this database in my examples.
       
      Here is link to post which shows how "ODBC Data Source Administrator" looks like.
       
    • By shino54
      Hello,
      I would like a query to know if an entry exists
      Thank you in advance.
      $sQuery = "SELECT Alger FROM garage where auto='BL1879'" $result1 = $result.Fields("Alger").Value if $result1="" Then MsgBox(0, "ERROR", "BAD not exist") Else MsgBox(0, "Success!", "OK exist") EndIf Exit  
    • By Nas
      Hi everyone,
      I am trying to make a script that runs a query and show it to me to see if everything is right and then decide if I finish it or not so I made a little script as below :
      #include <ADO.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> _ADO_EVENTS_SetUp(True) _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler) Local $sDriver = 'SQL Server' Local $sDatabase = 'DataBase' ; change this string to YourDatabaseName Local $sServer = 'Localhost' ; change this string to YourServerLocation Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & ';PWD=' & ';' ;~ Global $Query = _ ;~ "BEGIN TRAN" & @CRLF & _ ;~ "UPDATE Table" & @CRLF & _ ;~ "SET HOUR = 4" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "SELECT * FROM Table" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "ROLLBACK TRAN" Global $Query = _ "BEGIN TRAN" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" _Query_Display($sConnectionString, $Query) Func _Query_Display($sConnectionString, $sQUERY) ; Create connection object Local $oConnection = _ADO_Connection_Create() ; Open connection with $sConnectionString _ADO_Connection_OpenConString($oConnection, $sConnectionString) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Executing some query directly to Array of Arrays (instead to $oRecordset) Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True) ; Clean Up _ADO_Connection_Close($oConnection) $oConnection = Null ; Display Array Content with column names as headers _ADO_Recordset_Display($aRecordset, 'Query Result') EndFunc ;==> _Query_Display When I ran this script it works great, but when I run the query below :
      Global $Query = _ "BEGIN TRAN" & @CRLF & _ "UPDATE Table" & @CRLF & _ "SET HOUR = 4" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" It doesn't show anything, when I take those begin and rollback it does what it should but still not showing me anything at all, is there a way around it that you know of?
      Thank you.
    • By Nas
      Hello Guys,
       
      I am running the Ado example script, if I run this line, it runs without errors :
      Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';' But when I try the windows auth one , it failed everytime :
      Func _Example_MSSQL_WindowsAuthorization() Local $sDatabase = 'AdventureWorks2016' Local $sServer = 'SQLSRV' _Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from Person.Address") EndFunc here is the error :
       
      ADO.au3 v.2.1.16 BETA (1204) : ==> COM Error intercepted !
      $oADO_Error.description is:     Provider cannot be found. It may not be properly installed.
      $oADO_Error.windescription:     Exception occurred.
      $oADO_Error.number is:     80020009
      $oADO_Error.lastdllerror is:     0
      $oADO_Error.scriptline is:     1204
      $oADO_Error.source is:     ADODB.Connection
      $oADO_Error.helpfile is:     C:\Windows\HELP\ADO270.CHM
      $oADO_Error.helpcontext is:     1240655
      By the way, I have 64x windows server 2016 with SQL Server , I already installed the odbc my sql connector, any thought on this will be greatly appreciate it.
    • By argumentum
      I was thinking but I don't have the experience, so you may have the experience.
      I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ?
      The DB is now in MySQL. I wanna do all this chopping and use SQLite. 
      Thanks
      [solved]
×
×
  • Create New...