Jump to content
argumentum

speed up order by int DESC, text ASC;

Recommended Posts

Posted (edited)
$sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1";
    $rs = mysqli_query($conn, $sQueryUpdateTime);
    $row = mysqli_fetch_assoc($rs);

the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.

$sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows";
// takes 30 ms. on the indexed int

$sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows";
// takes 300 ms. due to "PatientName" been a text field, even as I did index it

So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.

..tho, I find that any 2nd argument in the ORDER BY is just slow.

Is there a way to query this in a way to have a faster result back ?

Thanks

PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE;  and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).

Edited by argumentum

Share this post


Link to post
Share on other sites

so, brain storming, I figure that the way, or only way with my experience, is to make a new column named "order_Dt_Na" and to the date add "001 to 999", where I'd query all names for that date ( that should be under 100, so no big deal ), sort and update the order_Dt_Na column. Every search will be ORDER BY StudyDate DESC, PatientName ASC , so might as well simplify by just ORDER BY order_Dt_Na .

If you have a better idea, or just know better, let me know.

Share this post


Link to post
Share on other sites
12 hours ago, argumentum said:

So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.

Not so. Whatever engine you use, the query planner must first apply your where clause to select the resulting subset. Then it must sort it as a whole (probably more than 20 rows); this can be helped by an index on (StudyDate, PatientName). Only then it has to honor the offset=N and skip the first N rows, finally output while counting up to 20.


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)

Share this post


Link to post
Share on other sites

If your only problem is the sorting being too slow then you should consider getting your data as it is and sort it by yourself. This can be a faster sort than how your db engine can perform.


When the words fail... music speaks

Share this post


Link to post
Share on other sites
Posted (edited)

I doubt it since AutoIt is interpreted while any SQL engine is fine-tuned C-like compiled code.

EDIT: BTW doing the sort at application level means doing exactly the same thing as how SQL works as I described in the post above.

You can see that by considering the following job to be done by hand (you're a processor now!):

You're given a deck of 52 regular cards in random order.
Cards fall in 4 colours: Spades, Hearts, Diamonds & Clubs.
Each color is associated to a multiplier: Clubs * 1, Diamonds * 2, Hearts * 3 and Spades * 4.
Say cards are valued like this:  numbered cards = their face value (1=1, ..., 7=7, 10=10), then Jack = 20, Queen = 30, King = 40, each multiplied by the coefficient of the card's colour.
Cards have a name: 1..10 = ace, two, three, ..., ten, jack, queen, king.
You must extract cards in Hearts or Clubs, have at least one 'n' in their name, sort them by name in ascending order and value by descending order, ignoring the first 4 and outputing the next 6.

It's easy to see by oneself that you just can't start sorting the cards and ignoring a number of them until you're done selecting which one is allowed. Yes you partly can but then you must interleave the selection (where clause) and the sort, something very inefficient when coded. In any case you can't starting ignoring cards until the selection/sort steps are done.

Now consider a table of 52 million rows and you give SQL big credit for doing the job for you. Of course creating the most efficient index for a given query greatly helps the query planner.

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)

Share this post


Link to post
Share on other sites
Posted (edited)

After so many years as DBA and database developer I think I can be a good processor. :muttley:

Who said anything about AutoIt? I mean of course AutoIt arrays won't be faster than database engines but sorting algorithms are pretty easy to be implemented in ASM. Anyway what OP asked is  regarding the two queries that sort data by one or two columns. For MS SQL Server for example this doesn't matter, a simple look at the execution plan will reveal you identical executions in the same amount of time. I don't know what database engine OP use but it's pretty obvious that something else is going on there. Maybe showing us the design of that table and your indexes will help us to give you better advices.

Edited by Andreik

When the words fail... music speaks

Share this post


Link to post
Share on other sites

That's why I proposed a compound index on both columns.

BTW sorting at app-level is still a bad idea: you need to obtain the whole subset of rows from the where clause and if the subset is large that implies exporting the data from the server to the client before the latter can sort, ignore and count. Better make good use of the server fine-tuned code and RAM caches.


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)

Share this post


Link to post
Share on other sites

That's true but he talked about 20 rows or something like that this is why I never think about at a big scale.


When the words fail... music speaks

Share this post


Link to post
Share on other sites

Again, the 20 rows is the consequence of the limit 20 clause. If the selected subset (after processing the where clause) is 50 million rows, you still have to get your hand on all that many rows to sort them all, then ignore the X first of that sorted resultset (offset X clause) and output the Y next (limit Y clause).


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)

Share this post


Link to post
Share on other sites

I know my friend how it's work, I said that I had in my mind a small number of rows. This happens when you read the forum at 3 AM.


When the words fail... music speaks

Share this post


Link to post
Share on other sites
Posted (edited)
5 hours ago, Andreik said:

For MS SQL Server for example this doesn't matter

I'm using MySQL but is the MariaDB flavor that comes with XAMPP.
After searching with this TEXT column, I change it to CHAR(60) but to no avail. It took pretty much the same time.
Then I index the heck out of it but the single indexing of both columns did not show noticeable improvement.
Then I added a 21st column, with a int(11) with the date and a counter ( YYYYMMDDnn ) and that gave me the best result as is just sorting by one int. column.

But, and this is a big but. The DB engine. I'd love for the implementation of the SQLite ( the UDF ), to be faster than the MySQL that comes with XAMPP, but if MS SQL is faster and as maintenance free as MySQL then I'd make the jump to MS SQL. Not the full fledged one but a "personal" or express version. Again, if SQLite is on par with these other bigger DB engines, SQLite is portable AND, that makes it beautiful in my eyes 😍

Anyways, there are so many flip-flops in a CPU. Meaning, they all are optimized code with years of development, my choice would be based in my experience and I have little.

So we are biased by our own experiences and abilities. Would you care to tell me what way would you go in my case ?, no triggers or other "fancy" DB use. Just INSERT, UPDATE and SELECT. What say you ?  :) 

Edit: from https:// www.sqlite.org/whentouse.html 
SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.
SQLite does not compete with client/server databases. SQLite competes with fopen().

https://www.sqlservercentral.com/articles/single-user-performance-of-sqlite-v-sql-server is also a good read.

Edit #2: I'll import the DB to SQLite, and test it. That will decide what is actually better ;) 

Edited by argumentum

Share this post


Link to post
Share on other sites
6 hours ago, argumentum said:

Would you care to tell me what way would you go in my case ?, no triggers or other "fancy" DB use. Just INSERT, UPDATE and SELECT. What say you ?

Please tell what entities you're managing. From what I gather from the thread, you have:

Table tblstudies:
rowid integer (probably),
studydate char,
intUpdateTime char,
patientname char,
PatientBirthDate char,
probably more columns.

I'll try to make something up later today.


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)

Share this post


Link to post
Share on other sites

intUpdateTime is a bigInt(20) to fit "20200229033627", PatientBirthDate is int(11) as "19991231" fits, ..used Integers as much as I can, else, TEXT.
And yes, I have a "RowID" as primary index, as it was an import from SQLite. And there are other tables too.

All these started with SQLite and PHP in my brainstorming/developing days back in 2018, then when I got a better working understanding moved to MySQL and PHP, but now I know that I can do it all in AutoIt and have no need for PHP. So I wanna move it all to AutoIt since my discovery of SSL for AutoIt ( that I found by chance looking into Syncthing ).
HTML is HTML, no matter what generates the HTML. Same with SQL stuff. It all comes down to responsiveness since these web sites have no more than 20 users. Not even 1 user a minute so is not a highly/heavy loaded site(s).
The site is in the customer's server, as I don't find the need to have the site on another "cloud" provider. Already have a server :) 
The idea of going to AutoIt/SQLite, is due to the 
noseyness of the "server admin." in one company, so, I'd move the "magic" site to an encrypted folder in my user account and protect my work while coding in a language I'm familiar with.

Now, back to SQL. I figure that SQLite could run in :memory: and outperform any other DB engine due to the usage I'll give it.
Same with PHP. In AutoIt it will have the pages as $sStrings, all in memory.

This setup should kick the heck out of anything else, at least in my mind. But I will only know for sure once is done and tested.

Now. I have no schooling and very low experience compared with y'all, so ...., I post to ask for advice since my trend of thought, could be basically flawed, period!, and go nowhere fast ( actually painfully slow as I code everything to no avail ) 😅

...and that is the full story  :) 

Share this post


Link to post
Share on other sites

SQL Server it's pretty reliable and easy to work with. With express edition you are limited at 1 physical processor, 1 GB memory, and 10 GB storage but hey it's completely free. So if I understand well, it's for a website without too much traffic. If this is the case and you can work with these limitations you MS SQL is a powerful database engine.


When the words fail... music speaks

Share this post


Link to post
Share on other sites
1 hour ago, Andreik said:

If this is the case and you can work with these limitations you MS SQL is a powerful database engine.

In my case they are not limiting, as the site will never get to 1 GB, since 10 years of data takes 100 k.
But is it's performance better than MySQL ?. MySQL need not be "installed". It can run as service but as user too.
And for SQLite, it only takes to load a DLL.

I'll post my findings once I finish coding the "MySQL 2 SQLite DB importer", and query via an API i'll put together as the DB engine*.

*I'll use the HTTP server to run the SQLite. It'll add an extra 5 ms. to the total time of the query.

Share this post


Link to post
Share on other sites

When it comes to databases there are two serious giants: oracle and sql server so yes it's a pretty reliable and performant database engine with a lot of features. But of course this doesn't means it would be the best fit for every project. Since yours it's quite small and will not grow very much in time sqlite might be a better choice and will give you more portability. You just need to find the balance between the features, performance and your requirements.


When the words fail... music speaks

Share this post


Link to post
Share on other sites
On 3/5/2020 at 1:13 PM, argumentum said:

This setup should kick the heck out of anything else, at least in my mind. But I will only know for sure once is done and tested.

Update: Done and tested. It does not "kick the heck out of anything else".
Is about 1/3 rd slower ( ~200 ms. vs. ~300 ms. on a slow PC ). Not that bad for an all (almost all)  AutoIt solution (the HTTP is encapsulated by Apache to have SSL).

 

On an unrelated note:

I needed to return the query as JSON and the only solution I came up with is:

Local $iReturnJson = 2 ; ...this is part of a Func but this is the JSONfier loop
    Local $sJson = ""
    ; $aResult comes from _SQLite_GetTable2d()
    For $n1 = 1 To UBound($aResult) - 1
        $sJson &= '{'
        For $n2 = 0 To UBound($aResult, 2) - 1
            $sJson &= '"' & $aResult[0][$n2] & '":' & Json_Encode($aResult[$n1][$n2]) & ','
        Next
        $sJson = StringTrimRight($sJson, 1) & '},'
        If $iReturnJson > 1 Then $sJson &= @CRLF
    Next
    $sJson = StringTrimRight($sJson, ($iReturnJson > 1 ? 3 : 1))
;~  ConsoleWrite($sJson & @CRLF)
    Return $sJson

..tried to use json_group_array() but could not get it to work.
Do any of you know if our SQLite can return the data as JSON ?
Does it only work if CREATE TABLE json_tree() ?

There is no clear and concise examples of a regular SQLite db and getting the data as JSON that I can find and understand. 

Share this post


Link to post
Share on other sites

The SQLite JSON1 extension provides functions to check, navigate, parse, extract, update Json data; but it does offers only very limited functionality to produce general Json output from the result of a query.

It isn't completely impossible to achieve but this is very cumbersome and each query would need a long and complex ad-hoc formatting.


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)

Share this post


Link to post
Share on other sites
On 3/7/2020 at 3:41 PM, argumentum said:

Update: Done and tested. It does not "kick the heck out of anything else".

Update #2: It does "kick the heck out of anything else".
Is twice as fast as PHP with MySQL/MariaDB. Tested on 2 PCs.
This speed is due to getting the JSON I needed straight from SQLite with the DB in :memory:.

I wanted to share this, to not leave the post with the believe that coding a whole site in AutoIt is impractical, or slower :) 

Share this post


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 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]
    • By buymeapc
      Hello,
      I was looking for a way to use the CLR functions to check a SQL 2016 database state, but I've been unable to find an example that I can get to work. Any help would be appreciated. Here's what I have, which always errors when I try to set the connection string and I'm not sure why. Thanks!
      #include ".\Includes\CLR.au3" #include ".\Includes\CLR Constants.au3" JustATest() Func JustATest() Local $oAssembly = _CLR_LoadLibrary("System.Data") ConsoleWrite("$oAssembly: " & IsObj($oAssembly) & @CRLF) Local $oSQLConn = _CLR_CreateObject($oAssembly, "System.Data.SqlClient.SqlConnection") ConsoleWrite("$oSQLConn: " & IsObj($oSQLConn) & @CRLF) $oSQLConn.ConnectionString = "Server=serverip;Database=dbname;UID=user;PWD=aaaa;" $oSQLConn.Open EndFunc Edit: I am using ptrex's CLR UDF from here:
    • By nooneclose
      Good morning, I am trying to figure out if it is possible to check if these invoice lines exist or not. 
      Here are the table and column name: INVOICELINE.INVOICELINENUM (a required field if created)
      Here is a picture of what I am talking about. (Do not worry about security. the picture is from a demo test site so all information is fake)
       
      is there any way to check if these fields exist or not? (they do not exist unless the user clicks on "New Row")
      Example: line 11 does not exist right now. How would I go about to see if it did or not? 
      This is what I have so far:
      SELECT DISTINCT iv.invoicenum, iv.description FROM invoice AS iv JOIN invoiceline AS ivl ON iv.invoicenum = ivl.invoicenum AND iv.siteid = ivl.siteid /* = 'nothing' and yet somehow not null? */ WHERE iv.invoicenum NOT IN (SELECT invoicelinenum FROM invoiceline WHERE invoicelinenum IS NOT NULL) I get 0 results where I should get more than 0. 
    • By TheSaint
      Only early days at this point, but I have been pondering such a program for a while.
      As good as calibre is (thank you Kovid Goyal), which is a great and wonderful ebook suite of tools and a fair database, it does have its limitations. One of which, is how it deals with multiple libraries, another is the views you get. CalibBrowser will seek to address those.
      What CalibBrowser is not going to be, is an editor for existing calibre libraries. That will be left up to calibre, which is very much needed still, and covers many aspects I will never look at. Unlike calibre, which is quite a complex program, CalibBrowser also seeks to be simple. It is mainly a viewer, at this point, but will later be able to create its own libraries. However, it does not and will not export them to calibre, especially as calibre employs a far different method and structure to what CalibBrowser will employ.
      When CalibBrowser starts, it looks for calibre executables and the main Calibre Library. Whatever isn't found, you get prompted for with a browse option.
      A calibre library, is a set of ebook folders (Author\Ebooks) and a database file, always named metadata.db, and which causes an issue when it comes to multiple libraries, but makes life a bit easier when reconstructing any corrupted libraries. However, there are better ways to deal with that, as my program will show.
      The metadata.db file is an SQL database, so I am having a learning curve right now, as I have only ever dealt with an SQL database previously, codewise, when I created my INItoSQL program some time last year, as an exercise to prove a point.
      At the moment, things are pretty basic, and not everything works 100%. Here is a screenshot, to give an idea of it, but keep in mind, I intend to expand the current GUI for other stuff I will be adding.

      Older Screenshots
      Gawd, I just noticed the '3|7' in the Book Input field (original screenshot). I was using that during troubleshooting for the multiple images scrolling and forgot to disable it ... not that it impacts anything. When it comes to maths, I struggle a bit, especially when tired. Right scrolling was easy, with a continuous loop, was easy to implement. Left scrolling was significantly harder for my poor brain ... until I realized I needed to see them as Min and Max.
      As you can see the program is usable, and all the buttons, aside from the Program Information one, work. You can even load different calibre libraries, and even reload after making changes to one with calibre. The calibre program does not need to be running, even to view an ebook in the Calibre Reader. The combo selector for a library and the ADD button are only temporarily placed where they are, until I expand the GUI.
      My intention at this point, is to add another five thumbnail images, directly below existing. Currently they aren't clickable, but I may add that.
      Here is another screenshot, of what you see when you click the larger Cover image.

      If you want to have a play with the program as is, then you will need to also get the 'sqlite3.dll' file from some online source. When CalibBrowser starts successfully with the selected calibre library, it copies its metadata.db file to a sub-folder of the program called 'Backups'. It also creates a sub-folder in that, based on the library name, to house it. That copied file, is the one the program uses, though it does not even edit that, and file modification is checked every time the program starts with a particular library, or when you Reload or select a library. If the original source file has been modified, then the program copy is overwritten. The Reload Database button does nothing, if there is no change detected, and reports such.
      Place the required 'sqlite3.dll' file in the CalibBrowser root folder. Download includes source files (sqlite3.dll excepted).
      Also required of course, is an install of calibre, plus some ebooks in a created library - Calibre Library is the default when you first add ebooks to calibre. The Mobile Read Forums, is a great source for all things ebook, and calibre can be found there in the E-Book Software section.
      CalibBrowser.zip  - Upload 4  (previous downloads: 1 + 12 + 5)
      CalibBrowser (new).zip
      My apologies for the program being created in AutoIt v3.3.0.0. It is the first one I have done in a while, with an older AutoIt version. Basically my Win 7 Netbook, which has a current version of AutoIt, was busy and is always busy doing something, and not suitable for doing big projects for several reasons. My older but more powerful Win XP Laptop, has a better programming environment, better computer chair (most important for my knees etc), better external monitor (wider and newish), full size external keyboard & mouse, and a great suite of setup tools to assist me. I run several older versions of AutoIt already on my Laptop, but haven't yet determined what I need to adjust to add a current version of AutoIt to the mix. This also applies to my hugely beneficial Toolbar For Any program (one of those tools), which I constantly use with SciTE. At some point, when finished, I may update the program to the current AutoIt version ... especially as I believe I am now proficient enough to do so, having become quite familiar with it in the last year or so, making many programs with it.
      Enjoy!
       
    • By Blois
      Hi Guys,
      Fine?
      I have this code and I use it to perform the query, however when I change the query to INSERT it is not working return error.
       
      #include <GUIConstants.au3> #include <MsgBoxConstants.au3> #include <Array.au3> Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") Example() Func Example()     Local $dbname = FileOpenDialog("Choose Access Database", @ScriptDir, "Access files (*.accdb)", 1)     If @error then Return SetError(@error, @extended, 0)     $adoCon = ObjCreate("ADODB.Connection")     $adoCon.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & $dbname & ";Uid=;Pwd=;")     $adoRs = ObjCreate("ADODB.Recordset")     GUICreate("listview items", 550, 250, 100, 200, -1, $WS_EX_ACCEPTFILES)     Local $idListview = GUICtrlCreateListView("Codigo    |Nome         |Valor  ", 10, 10, 520, 150) ;,$LVS_SORTDESCENDING     $queryInsert = INSERT INTO TABLENAME VALUES (''aaaaa'', ''bbbbbb'', ''cccccc'')     Local $aResult     With $adoRs         .CursorType = 2         .LockType = 3         .Open($queryInsert, $adoCon)         If @error Then             ; deal with Probable SQL error             Return SetError(1)         EndIf         If Not .EOF Then $aResult = .GetRows()         .Close()     EndWith     $adoRs = 0     _ArrayDisplay($aResult, 'UBound($aResult)=' & UBound($aResult))     For $iRow_idx = 0 To UBound($aResult) - 1         GUICtrlCreateListViewItem($aResult[$iRow_idx][0], $idListview)     Next     $adoCon.Close     GUISetState()     ; Loop until the user exits.     While 1         Switch GUIGetMsg()             Case $GUI_EVENT_CLOSE                 ExitLoop ;~             Case $idButton ;~                 MsgBox($MB_SYSTEMMODAL, "listview item", GUICtrlRead(GUICtrlRead($idListview)), 2)             Case $idListview                 MsgBox($MB_SYSTEMMODAL, "listview", "clicked=" & GUICtrlGetState($idListview), 2)         EndSwitch     WEnd EndFunc   ;==>Example ; User's COM error function. Will be called if COM error occurs Func _ErrFunc($oError)     ; Do anything here.     ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _             @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _             @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _             @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _             @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _             @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _             @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _             @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _             @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _             @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF) EndFunc   ;==>_ErrFunc Help, Please!
       
×
×
  • Create New...