TechCoder

That's one of the 'dangers' of going directly to the IT guys (sadly, most have an attitude of power...) and exactly why I suggested some other paths. I'd try going through your manager - presuming you have permission and a requirement to gather it for your job, and permission from your boss to work on the program project - seems a more politically correct path ("It will help me in my job as well as X people in the group, etc."). You really need to have some 'hard numbers' on the time/work savings and for how many, etc. to get this access, even though it isn't asking much, IMHO.... Write up some numbers, make a small report, etc. and give it to your boss. Once the boss buys into the project and sees value, plus has a report in hand that shows how much the group will save (thus, handing him/her all the data they need to go further with no effort on their part and getting all the credit....... - yeah, becoming the boss' idea....), then IT has no option but to open a link for you. Also, a bit of 'power' for your end on the report - as you are not asking for anything other than the data already shown, the command you need is nothing other than the one being used now, so there should be less than 10 minutes work on the part of IT (i.e., no unreasonable burden). All you need is the sql command, or a program that does that and puts the data to an external flat file (if they bring up the argument of giving you access directly into the database, etc.) and it is the same data, just output in a different manner where you can then process it fast and give additional value to 'millions' (ok, maybe not, but quantify it!) Oh, and since you have already taken it to IT, you can use that info in your report - in a positive way, though! What were the IT guy's issues (probably feeling like it was a lot of extra work to them, etc.)? Make sure you address anything they said to you, whatever it was. That way, when the boss takes "his" idea to the IT guy, he already has the answers to those issues, written in a nice report..... (i.e., don't give IT any way to let the boss look bad - that backlashes pretty heavily!) Looking at your program, I think you might speed it up slightly going a different route, though not likely worth the effort to even evaluate different methods. The only way to 'significantly' speed it (which is what you need to make it worthy of doing the project at all) is to get direct access to the data. So, before you mess with the code any more, use your time to write a comprehensive report that the boss can take to the big boss - that is the best use of your time, and the way you will get the results.

If you are making such a valuable piece of software that you can use yourself, it seems that it will have value to others, and, (it seems to me) your company should be happy that you are building such a thing. Therefore, they should give you access to the main database (at least a 'friendly' "dip" to grab a snapshot of status - with the exact same data as you can see now {i.e., no 'secret info', etc.}) Has to be much faster than the copy/paste method and certainly better for your app. I'd suggest it to your manager, the IT manager and perhaps even a buddy you have in the IT dept.......... (politics in a corp environment is always tricky - you have to work it as you best see fit!) In different companies, I've done all those, just to get what I need done to help my job (stinks, but that is the way it works...) - typically, the best method involves it becoming 'their idea'.... - you get what you need, they get the 'glory' - a WIN-WIN!

For those, like me, that got excited about Pancakes, but didn't know there was a special day........ http://en.wikipedia.org/wiki/Shrove_Tuesday

I did not duplicate this error, though got it when clicking on the color-change buttons. This is because you have your variables declared Global in the wrong place. Try ; add Global to the TOP of the code Global $bg = 0x222222 Global $graph = 0xFFFFFF ; REMOVE the Global from inside the functions Func bg_color() $bg = _ChooseColor(2, $bg, 2) GUICtrlSetBkColor($box,$bg) EndFunc Func graph_color() $graph = _ChooseColor(2, $graph, 2) GUICtrlSetBkColor($marker,$graph) EndFunc That worked for me (btw, nice work - keep it up, you will get there!)

and, further to a 'complete' program, you would want to check ALL the IPAddress connections........ (I think I said that the .dll is a much simpler solution....!)

Simply checking the IP is one way of testing, though here's a scenario that I ran into that required me to learn (and do) more........ Connect to the internet through a router (wireless is typical, though LAN is just as good) Now, you will have an IP of 192.x.x.x (or perhaps 10.x.x.x - or you could have anything the router will allow....) Disconnect the Internet cable from the router (i.e., not the computer from the router) Check your IP - yep, it is still the same - though you are NOT connected to the internet. So, your StringInStr method is 'ok', though not complete enough. (and, we both need to change our code to reflect the other's local IP - I didn't run into the 169.x.x.x one, but it is certainly as valid a 'problem' as 127.x.x.x {and I don't recall what situation you can have 0.0.0.0 though not a bad thing to check for} - which is another reason the .dll is a 'better' solution for most applications)

The .dll call monitors the status of the internet connection as determined by Windows itself. How Windows determines if it is online (or not) is rather simple, though works very well. Basically, they constantly probe a given page on the Internet and if the return is correct (it is a page with very little in it so it keeps down bit transfer), your computer (and thus, the .dll quoted to you) will show 'online'. Here's a Microsoft doc about it (though, as with most of their docs, don't expect to much detail....) http://msdn.microsoft.com/en-us/library/ee264321(v=vs.85).aspx and, here's a link to a page where I learned a lot about the inner workings of this feature http://superuser.com/questions/277923/how-does-windows-know-whether-it-has-internet-access-or-if-a-wi-fi-connection-re I had a need (or was that a desire...?) for a 'better than' way to detect status and came up with two variations - both work to detect online status changes, and both much faster than the reporting done by the .dll, though do more 'work' (overhead, resources, etc.) than needed for most applications (the .dll method is the prefered one) This one is for notifying you if the IP address has changed - in my application it is critical to keep the same one or restart the app (there is no work-around I could find, which is one reason I have abandoned the technology used in that app and moved into more AutoIt creations). I ran this in the While loop and it works well - and very fast - reporting long before the 'offline' notification. Local $IPnow = @IPAddress1 If $IP <> $IPnow And $IP <> "127.0.0.1" And $showing <> 1 Then SplashTextOn("IP ADDRESS CHANGE",@CRLF & "The IP address for this computer has changed." & @CRLF & @CRLF & "Expecting " & $IP & @CRLF & "Current IP " & $IPnow & @CRLF & @CRLF & "Check Your Internet Connection. " & @CRLF & "You may have to restart the program. ") $showing = 1 EndIf If $showing = 1 And $IP <> $IPnow And $IP <> "127.0.0.1:4001" Then Sleep(100) ControlSetText("IP ADDRESS CHANGE", "", "Static1", @CRLF & "The IP address for this computer has changed." & @CRLF & @CRLF & "Expecting " & $IP & @CRLF & "Current IP " & $IPnow & @CRLF & @CRLF & "Check Your Internet Connection. " & @CRLF & "You may have to restart Karaoke Pro Tools. ") EndIf If $IP = $IPnow Then SplashOff() $showing = 0 EndIf A more to-the-point way of doing what Microsoft does (basically duplicating the internal workings of the online status indicator in your PC, but it is faster reporting if you keep it in a moderately tight {Sleep (500) is what I've used} loop....) as described in the above documents - and may be a way you are looking for (somewhat) as what it does is to directly check the active webpage on the Internet. Func _online_status() Local $status = 0 If @IPAddress1 <> "127.0.0.1" Then Local $oIE = _IECreate("http://www.msftncsi.com/ncsi.txt", 0, 0, 1, 0) Local $sText = _IEBodyReadText($oIE) If $sText = "Microsoft NCSI" Then $status = 1 EndIf _IEQuit($oIE) EndIf Return $status EndFunc ;==>_online_status Of course, you really should not use MS page for this - though, if it is an occasional test, I doubt their hit counters will go too wild...... If you are running this in a released-to-the-public program, you might want to consider making your own web page for it (and putting your own counter on it so you can see how much your program is used......) Again, though, the 'prefered' method of checking is the one you have been given (the .dll), though, now, you understand why/how it works!

in this case, though, the new variable was created in the Enum, so all I needed to do was reference to that, which Eval does as I needed, and works like I've always used $$string (where $string has always been created first in my use, though php would create a new one, if you wanted - that is a difference......) AutoIt doesn't create one Local Eval("thisnewone") = "works" ConsoleWrite($thisnewone & @crlf) gives

Worked a CHAMP! #include <Array.au3> Local $TOOLS[10] Global Enum $output1,$output2,$output3,$output4 $TOOLS[1] = "one" $TOOLS[2] = "two" $TOOLS[3] = "three" $TOOLS[4] = "four" for $x = 1 to UBound($TOOLS) Local $string = "output" & $x ConsoleWrite($TOOLS[Eval("output"&$x)] & @crlf) Next (still need to adjust things a bit, but the concept is there.....) Thanks!

Haven't come across this 'feature (or is it a trick?)' in the forum and not sure how to go about making it work in AutoIt..... I'm trying to 'build' the variable string dynamically before referencing it. #include <Array.au3> Local $TOOLS[10] Global Enum $ID, $output1,$output2,$output3,$output4 $TOOLS[1] = "one" $TOOLS[2] = "two" $TOOLS[3] = "three" $TOOLS[4] = "four" Local $inout = "out" For $x = 1 to UBound($TOOLS) Local $string = $inout & "put" & $x ConsoleWrite($TOOLS[$($string)] & @crlf) Next (this was 'latest attempt' - tried several ways to build the $$string, always get a syntax error) What I want to do is use a loop to go through several (more than shown) variables and not have to type out every one. The way I've done it in php is just to build the $string and then put a $ in front of it (i.e. $$string). In AutoIt I have achieved the result using AssocArray* functions. For $i = 1 To 9 $toolbox = $inout & "put" & $i Local $type = AssocArrayGet($TOOLS, $toolbox) Switch $type . . While I find AssocArray* 'easy', I have found them to be causing some slowdowns that I would rather avoid, so I'm trying to replace all those functions and this is the last one I have to do. I'm just learning about Enum variables and see there is a lot of value in using them, though I haven't yet learned perhaps the 'best' way to use them..... If it is possible to 'build' the $$string type variable, I'll be able to replace the last bits of the AssocArray* functions so I can retest for slowdowns. Thanks for input on this.

actually, I was just putting a video together to show you (privately) the differences and was going to ask you for the same confidentiality. PM away!

ok on the thin wrap. I do have a feature for working duplicates - in my php code....... That is one of the things I haven't gotten to in AutoIt version yet - been slowed (no pun intended) on this particular thing for a couple days now. Can't really say I have a plan of implementation for that yet as I haven't studied the docs to see what might can happen. I did run across something several days ago about duplicates, but passed on it as I was doing other stuff. What recommendations do you have?

Well, that is good to know, in one way, though sad in another, since my php program will do twice the process (input and output functions) for 30K records in the time I'm doing 3000 here....... But, there are benefits of having the program here (GUI look, simpler to create the user interface) and security issues with the other method, so I guess I'll move off trying to make this faster and get back to feature implementation - I thought (and was hoping) it was just my newness to the program. Processing taking < 1 minute each round is 'acceptable', so for really small needs, it works just fine. The input side on my 31K records takes ~5 minutes (there are a lot of unmatched records, which reflects on my Exception tracking, which I will look into) and I wonder about clients with super-large numbers of files (from the calculations shown before, and taking that 'average' number to what I think is my 'biggie size' client, 250 K rows of 30 column with average 200 characters. 250 K * 30 * 200 * 2 = 3 G - which takes it out of the memory size of most PCs and I have to revert back to the disk based db anyway (have been thinking about an algorithm to look at available memory size, number of files, etc. and figure out if we should take the memory or disk based route - shouldn't be too hard to come up with, if that becomes the best answer)

Speed difference by replacing _SQLite_Escape with "'" on each side (not recommended for 'unknown' data!) 'Typical' run (as described above) processed in 0 min., 23.8 sec. processed in 0 min., 24.1 sec. processed in 0 min., 23.6 sec. processed in 0 min., 24.2 sec. After changing 5 elements processed in 0 min., 22.1 sec. processed in 0 min., 24.4 sec. processed in 0 min., 23.2 sec. processed in 0 min., 24.4 sec. I must say I was surprised - I thought there would be at least a tiny measurable difference, but the numbers don't show it......

changing the progress bar update time did not significantly change the overall processing (hardly noticed - did not print out a timer or anything as it is just minimal) Though, for those looking for a method to update the ProgressSet and not have a 'runaway' look (showing every file count does slow the time, but it is pretty irritating to watch the flying numbers.....) Local $point = $counter / Round($size[1] / ($size[1] / ($size[1] / 100))) If $point = Round($point) Then ProgressSet($point, "Parced " & $counter & " of " & $size[1] & " files in " & Int($dif / 60000) & " min., " & Round((($dif / 60000) - Int($dif / 60000)) * 60, 1) & " sec.", "Processing database input records") ElseIf $point>= 100 Then ProgressSet($point, "Parced " & $size[1] & " of " & $size[1] & " files in " & Int($dif / 60000) & " min., " & Round((($dif / 60000) - Int($dif / 60000)) * 60, 1) & " sec.", "Processing database input records") EndIf where (in this case); $counter is counting the 'each' you want to look for (files) $size comes from DirGetSize $dif = TimerDiff() from a timer you set at the start of where you want to check time a bit shorter code and (somewhat) simpler than I found at '?do=embed' frameborder='0' data-embedContent>> though I'm sure the math could be done in a less complex way (seems I've stayed away from creating mathmatical formulas for time - not one of the things I do often....), but this works, which is what is important....

$sql = "CREATE TABLE '" & $CT_active_tool & "crunch' (full_filename text NOT NULL PRIMARY KEY, file_name text NOT NULL, file_ext text NULL, dir_name text NULL, filename text NULL, DiscID text NULL, Artist text NULL, Track integer DEFAULT '0', Title text NULL, Custom text NULL, output_filename text NULL, output_folder text NULL, output_DiscID text NULL, output_Track integer NULL, output_Title text NULL, output_Artist text NULL, output_Custom text NULL);" (SQLite Expert can't open memory databases - at least, I couldn't figure out a way to do it....... )

More data.................. Looking ONLY in the grab-filename-put-into-database loop on the INPUT side LEGEND: End of Setup (shown after some small parsing after _PathSplit and one _ArrayBinarySearch) Start Exception Processing (did a StringRegExp just before) End Exception Processing (all my exceptions checks are done) Start Add To Active List (if the file passes, this starts the db dump) End Add To Active List (end of db write) OR Start Add to Reverse Lookup End Add To Reverse Lookup Starting the ProgressSet (once in awhile we update the progress meter) Time to process this file (ProgressSet is done and total time of processing is here) NOW, TO THE DATA............. A file that doesn't match original Exceptions (fastest processed file type) I'll further break down the Exception processing section to see if I can find anything that is 'wasteful'....... A file that doesn't match Exceptions though hits ProgressSet counter (next fastest processed file type) You can see from this that calling ProgressSet does eat up some time (though we have to call it because the users want it... A file that matches 'first run' things but not the main StringPregExp (goes to the 'Reverse Lookup' table in the database) Yes, we found the time bandit............ The only thing in this section is the sql build-it string and the _SQLite_Exec() - this is to the MEMORY database One thing 'of note' is that I am using _SQLite_Escape() on all the data pieces - I'm sure that is eating up time here. This is, I believe, a good habit to get into (I used a similar function in the php that helped to protect against database injection - this is the only function I could find similar in AutoIt/SQLite, though, with this particular data/situation, I believe I could get away with just 's around it). I'll do some tests changing _SQLite_Escape($databit) to "'" & $databit & "'" and see what that does. a file that matches our StringPregExp and passes all the Exception checking and, finally, the LONGEST time = a 'good' file that also hits the ProgressSet Conclusion: There are three locations to check further for 'waste'; - Exception processing - any/all function calls in/around the sql build (primarily _SQLite_Escape, though in the 'Active' write, there are also some calls to AssocArray, yet looking at the times above, they are not 'horrible', yet do exist...) - look at calling ProgressSet as little as possible (maybe half as often as I'm calling it now)

basically, 1. loop through the files in the chosen directory(ies) 2. parse them according to the chosen rules (using StringRegExp, _PathSplit, StringRight, StringLeft,etc.) 3. verify they should be on the list to process (i.e., kick out various exceptions that might have made it through StringRegExp - perhaps by user selected option, my own 'bounce' rules, etc.) 4. take all the 'bits' (full filename as well as all the broken down pieces) and save to the database I believe I can get some speed by not keeping all these bits floating around and just break it apart again later (though I wonder if it will be anything really significant and worth the effort - it is only perhaps 200 characters per filename...) All this code is simply using the same logic/etc. from the php code - I took the comments from that code and ported the functions into AutoIt (as a new AutoIt user, doing the best I could with what I could find to make that section work...), so I'm sure there are many 'better ways' to get things faster - and certainly several things to change in the flow to be better code overall. Directly on the speed testing; I just tried using BEGIN/COMMIT with the memory db and the results are so similar I can't judge one is better than the other (both around 20 seconds for the input side). As I'm only using the disk based db for quick dips and single commands, there is no sense (that I can think of) to use that - and, it is quite fast anyway. So, to directly answer your question (I believe) - I have tested it both ways ("all filenames in one transaction" with BEGIN/COMMIT and 'chunks' as one-at-a-time saves) and it seems no different.

I'm now using _SQLite_Open() along with _SQLite_Open($DBName) to run the two databases. I need one to retain settings the user needs, the other is the 'bulk' one that stores info from reading filenames, works the crunching of data, then outputs the results. So far, I don't see a need to load the settings db into memory (it only contains about 50 tiny bits of data that is updated with every 'process this' run or when the user wants to save it and loading/saving is not noticeable at all - not sure if we could even measure a difference). However, using the tip to run things in memory for the bulk one PLUS the function from @AZJIO (see '?do=embed' frameborder='0' data-embedContent>>) has sped things up another 30% on the filename-to-memory-database side (what I call my 'input' side). ~20 seconds on 3K+ files - down from ~30 seconds before (it is also possible AutoIt upgrade helped - no exact data on which thing was best - I'll try to keep better results records for the output and 'overall' tweaks) - I'll do some runs on the 31K+ list later today. Still no comparison to the ~27 seconds processing the same data through not only the 'input' side, but also the 'output' side (i.e., all the way through to the report stage) using the same flow of logic in php + MySQL (I totally understand that its apples and oranges systems, though user experience/perception isn't different), but getting better all the time.......

found it! http://www.sqlite.org/2013/sqlite-dll-win32-x86-3080200.zip I did have the older dll in the scriptdir, but the new AutoIt version check stopped it. Installing this file now gets me back on track - SQL0 0.29 sec SQL1 0.29 sec SQL2 0.29 sec SQL3 0.29 sec disk based database open 0.29 sec diskbased db DEFAULT set 0.34 sec 'new' settings complete 0.34 sec ready for GUISetState 0.35 sec GUISetState done 0.39 sec Yeah! Now, I can get back to work (and back on the topic directly!)

I've got dozens of versions (different sizes/dates) all over my computer, but after the upgrade, not one in the scriptdir or @SystemDir.......... (guess I had put it there before when I first started using SQLite - don't recall, but I am sure I had it in my scriptdir before - that is why I was loading it from there....) I tried using the scripts in the new help file; ; SQLite.dll version must match returns a temp directory ; no version reference so file must exist in @SystemDir, @WindowsDir, @ScriptDir or @WorkingDir says I don't have the file ; open SQLite with a specific local file says can't be loaded ; Force download from www.autoitscript.com loads to a tmp directory uncommenting the ;~ #RequireAdmin ; needed if storing in @systemdir is wanted gives the 'unknown publisher' error from MS - saying Yes again only loads a temp file (after a very long time...) Any place for me to get a compatible, latest version file directly?

Running the program several times to get some average startup - got this message This was after getting return times (I didn't subtract the .26 seconds of other stuff, this is just the 'SQL1' line time) 6.37 5.91 5.9 5.9 6.01 5.92 5.91 39.08 5.94 6.04 I'm going to try re-installing AutoIt........

Been doing a lot of testing and some tweaking on my own before bugging you more (you gave me a lot of ideas!) New thread at

Continuation of topic started I have been testing various times and configurations after reading the tips given and things are a bit better, though now a new issue has come up - after installing the latest version of AutoIt (3.3.10.2) _SQLite_Startup() is taking a LOT of time to load. Odd thing is, it is not consistent (I hate that....) - though times vary from 5 seconds (which is 'long', IMHO) to OVER 60 seconds! ConsoleWrite("various states disabled/hidden " & Round(TimerDiff($timer) / 1000, 2) & ' sec' & @crlf) ; ************************************ read from / update the database FileInstall("sqlite3.dll", @ScriptDir & "", 1) ConsoleWrite("SQL0 " & Round(TimerDiff($timer) / 1000, 2) & ' sec' & @crlf) _SQLite_Startup() If @error Then MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded!") Exit -1 EndIf ConsoleWrite("SQL1 " & Round(TimerDiff($timer) / 1000, 2) & ' sec' & @crlf) Console output from one test (not 'typical', but certainly 'common' to be very long.....) Prior to loading 3.3.10.2 times were 'fast' (never noticed any lag enough to care to time it), though now when I start the program it is very noticably delayed. I want to address various other tweaks and such as discussed by @jchd and certainly the differences and tweaks for working with disk based or memory based databases (in this project, I need one disk based and it may be that I go with memory based for the other, more temporary), though now, this is my #1 priority! It doesn't matter if it is disk or memory based at this point, I'm just doing the Startup and with such L O N G times, it has my project stalled (I can't put something out that takes over a minute to start!). BTW (not sure how important it might be), the #include list (which seems to grow constantly...) is now I will be eliminating some of those, I'm sure as I tweak this program for speed (one of them being "AssocArrays" - I have found it to be (sadly, as I am used to named array elements...) causing some of the slowness in the program. I have elimanted most of the AssocArray calls in the main loop and increased times about 20% (calling it nearly 40 times in the loop, with 30K+ files, it made a difference!) Anyway, more on that stuff later - right now, I need to understand what to do about _SQLite_Startup()! Ideas/suggestions?

спасибо - что функция работает - и очень быстро Great feature - and super-fast. It read all the drive in just over 18 seconds. I will be including this to handle the search I need. And, it didn't require the upgrade on AutoIt (perhaps good for someone that has a lot of scripts that might need rework before upgrading - this looks to be a good way to go). огромное спасибо (Russian is my second 'spoken' language - I read it well enough, too, though typing it has never been a practice)