Jump to content

AutoIt and Databases.


Recommended Posts

That is perfectly fine with me. I am sorry I was to general in my question :) I will make sure there is more detail involved such as that quote I brought up from the help file. No offense taken. I understand completely where you are coming from.

Thanks for all the help you provide here... I will be back I am sure with some more stuff this is the biggest script I've had to write yet.

JS

AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Link to comment
Share on other sites

Okay I added the $i... my code looks like this.

$OpenAccess = "Open Access"
If WinExists($OpenAccess) Then Exit; It's already running
AutoItWinSetTitle($OpenAccess)

Dim $sql = "SELECT Family.MemberID, Family.MemberPassword FROM Family";SQL Statement
Dim $db = "FCA Database";Database connection setup in control panel.
Dim $file, $line, $linewrite;Just variables used several times.
Dim $file1 = "sql.txt";SQL Log File
Dim $file2 = "unapw.txt";Username and Password File
Dim $i = 9;Line to start on

If Not(FileExists($file1)) Then
   Run('C:/Tools/sqlcmd.exe /db "' & $db & '" /silent /log "' & $file1 & '" /command "' & $sql & '"','',@SW_MINIMIZE)
   Sleep(500)
EndIf

$file = FileOpen($file1, 0)
If $file = -1 Then Exit

While 1
   $line = FileReadLine($file, $i)
   If @error = -1 Then ExitLoop
   $linewrite = StringReplace($line,"' '","|")
   FileWriteLine($file2, $linewrite)
   $i = $i + 1
WEnd

It now takes over 200 times as long. I think I might want to leave the 8 lines unless you can let me know what I am doing so wrong to make it sooo slow. :)

It used to take approximately 10-12 seconds... it just took 288.285 seconds. (Thanks to SciTE)

JS

AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Link to comment
Share on other sites

Okay I added the $i... my code looks like this.

$OpenAccess = "Open Access"
If WinExists($OpenAccess) Then Exit; It's already running
AutoItWinSetTitle($OpenAccess)

Dim $sql = "SELECT Family.MemberID, Family.MemberPassword FROM Family";SQL Statement
Dim $db = "FCA Database";Database connection setup in control panel.
Dim $file, $line, $linewrite;Just variables used several times.
Dim $file1 = "sql.txt";SQL Log File
Dim $file2 = "unapw.txt";Username and Password File
Dim $i = 9;Line to start on

If Not(FileExists($file1)) Then
   Run('C:/Tools/sqlcmd.exe /db "' & $db & '" /silent /log "' & $file1 & '" /command "' & $sql & '"','',@SW_MINIMIZE)
   Sleep(500)
EndIf

$file = FileOpen($file1, 0)
If $file = -1 Then Exit

While 1
   $line = FileReadLine($file, $i)
   If @error = -1 Then ExitLoop
   $linewrite = StringReplace($line,"' '","|")
   FileWriteLine($file2, $linewrite)
   $i = $i + 1
WEnd

It now takes over 200 times as long. I think I might want to leave the 8 lines unless you can let me know what I am doing so wrong to make it sooo slow.  :)

It used to take approximately 10-12 seconds... it just took 288.285 seconds. (Thanks to SciTE)

JS

<{POST_SNAPBACK}>

The last part of your code:

$file = FileOpen($file1, 0)
If $file = -1 Then Exit

While 1
   $line = FileReadLine($file, $i)
   If @error = -1 Then ExitLoop
   $linewrite = StringReplace($line,"' '","|")
   FileWriteLine($file2, $linewrite)
   $i = $i + 1
WEnd

is not causing the delay. I can prove this because I copied your sample file from the previous page and ran it through:

Dim $file1 = "sql.txt";SQL Log File
Dim $file2 = "unapw.txt";Username and Password File
Dim $i

$file = FileOpen($file1, 0)
If $file = -1 Then Exit
$i = 9

While 1
  $line = FileReadLine($file, $i)
  If @error = -1 Then ExitLoop
  $linewrite = StringReplace($line,"' '","|")
  FileWriteLine($file2, $linewrite)
  $i = $i + 1
WEnd

and it only takes between 1.161 and 2.312 seconds (using Scite). The slowness is comming from your database transaction. If you don't believe me place a message box after the database query and see how long it takes to pop up. I assure you this code is not slowing down your script.

*** Matt @ MPCS

Link to comment
Share on other sites

Interesting answer. The database log file contains the time that it takes for the SQL command to take place and it is less than 1/2 a second. I can also see it because I see the minimized window pop up and go away. Maybe it was just because I had so much stuff running? Hrm I will try it again and see what happens.

JS

AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Link to comment
Share on other sites

It seems to take just a little bit over a second per KB in the file :( I am going to try it once without the $i in there and see what happens :idiot:

JS

Edit: I tried it without the $i and it went down to 10.783 ;):):):idiot::D

Hrm...

Edited by JSThePatriot

AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Link to comment
Share on other sites

It seems to take just a little bit over a second per KB in the file :( I am going to try it once without the $i in there and see what happens :idiot:

JS

Edit: I tried it without the $i and it went down to 10.783 ;)  :)  :)  :idiot:  :D

Hrm...

<{POST_SNAPBACK}>

I don't know how you would only copy out the lines after line 8 without having some sort of loop. I am telling you there is nothing in that code that will slow down your script, but if you got it to work a better/faster/smaller way then I would like to know what it is so I can use it for future reference.

*** Matt @ MPCS

Link to comment
Share on other sites

I am not just learning, though I need to retrieve a list of passwords and then use each one in that list to where it matches up to a MemberID # in another program. I was thinking autoit could do that for me... that way I wouldnt have to search through 13k records every time I needed to do this...

JS

<{POST_SNAPBACK}>

With that... I wonder if AutoIT has the feature of searching record with a

quickier way ? If compare the database one after another, it takes very very

long time to do so... imagine... 13K ! Wow.... :)

Link to comment
Share on other sites

I don't know how you would only copy out the lines after line 8 without having some sort of loop. I am telling you there is nothing in that code that will slow down your script, but if you got it to work a better/faster/smaller way then I would like to know what it is so I can use it for future reference.

*** Matt @ MPCS

<{POST_SNAPBACK}>

Below is the code I use to make it go at 10 seconds. I dont understand why adding that little extra makes it take sooo much longer.

$OpenAccess = "Open Access"
If WinExists($OpenAccess) Then Exit; It's already running
AutoItWinSetTitle($OpenAccess)

Dim $sql = "SELECT Family.MemberID, Family.MemberPassword FROM Family";SQL Statement
Dim $db = "FCA Database";Database connection setup in control panel.
Dim $file, $line, $linewrite;Just variables used several times.
Dim $file1 = "sql.txt";SQL Log File
Dim $file2 = "unapw.txt";Username and Password File
Dim $i;Line to start on

If Not(FileExists($file1)) Then
   Run('C:/Tools/sqlcmd.exe /db "' & $db & '" /silent /log "' & $file1 & '" /command "' & $sql & '"','',@SW_MINIMIZE)
   Sleep(500)
EndIf

$file = FileOpen($file1, 0)
If $file = -1 Then Exit
$i = 9

While 1
   $line = FileReadLine($file);, $i)
   If @error = -1 Then ExitLoop
   $linewrite = StringReplace($line,"' '","|")
   FileWriteLine($file2, $linewrite)
;   $i = $i + 1
WEnd

Its odd... I would have thought getting rid of the first 8 lines would have made it slightly faster, but when I do that it goes extremely slow and only processes about 1KB a second. (I know because I refresh the folder the file is in and watch it grow). The file is over 230KB.

With that... I wonder if AutoIT has the feature of searching record with a

quickier way ? If compare the database one after another, it takes very very

long time to do so... imagine... 13K ! Wow....  :)

<{POST_SNAPBACK}>

Friends... try that SQL command line that is linked above. It works great and quite fast. (13k Records in .5 seconds in my opinion is quite fast).

JS

AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Link to comment
Share on other sites

Okay... I have almost the exact output I want. I have cut it all the way down I just want to get rid of the first 8 lines and the last 3. I suppose that isnt that big of a deal, but I am not sure how the string split will handle those lines...

Also if I try to remove the 8 lines after I already have the output close to what I want it still takes 200+ seconds...

This is my code...

$OpenAccess = "Open Access"
If WinExists($OpenAccess) Then Exit; It's already running
AutoItWinSetTitle($OpenAccess)

Dim $sql = "SELECT Family.MemberID, Family.MemberPassword FROM Family";SQL Statement
Dim $db = "FCA Database";Database connection setup in control panel.
Dim $file, $line, $linewrite;Just variables used several times.
Dim $file1 = "sql.txt";SQL Log File
Dim $file2 = "unapw.txt";Username and Password File
Dim $file3 = "new.txt";Final output
Dim $i;Line to start on

If Not(FileExists($file1)) Then
   Run('C:/Tools/sqlcmd.exe /db "' & $db & '" /silent /log "' & $file1 & '" /command "' & $sql & '"','',@SW_MINIMIZE)
   Sleep(500)
EndIf

$file = FileOpen($file1, 0)
If $file = -1 Then Exit
;$i = 9

While 1
   $line = FileReadLine($file);, $i)
   If @error = -1 Then ExitLoop
   $linewrite = StringReplace($line,"' '","|")
   $linewrite = StringTrimLeft($linewrite,1)
   $linewrite = StringTrimRight($linewrite,2)
   FileWriteLine($file2, $linewrite)
;   $i = $i + 1
WEnd

FileClose($file)

From there I get the following out put in about 15 seconds...

ogging into 'FCA Database' using account ''.

CMD BEGI

ELECT Family.MemberID, Family.MemberPassword FROM Fami

CMD EN

                    Name      Index      Width Type  XLT Descripti

                MemberID [ 1 of  2]        11    4    3 Integ

          MemberPassword [ 2 of  2]        10  12    2 Ch

4|Mark519

5|1Tim211

6|Zech91

10|Eph618

11|Lev1316

13|1King141

14|Jer124

15|Zech85

58171|3John2

58172|Phi321

16|Hag21

CMD PROCESSED IN 1.48 SEC

Please note that the above version is a shortened version.

I have tried the whole $i in another While statement and it didnt work in a timely manner... because it still took way too long. Is there a command (I cannot find it in the help file though I seem to remember one) that will tell me how many lines there are in a file? That way I could atleast remove the last 3 and the first 8 I would just have to wait almost 5 minutes. :-/

JS

AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Link to comment
Share on other sites

Okay... I have almost the exact output I want. I have cut it all the way down I just want to get rid of the first 8 lines and the last 3. I suppose that isnt that big of a deal, but I am not sure how the string split will handle those lines...

Also if I try to remove the 8 lines after I already have the output close to what I want it still takes 200+ seconds...

This is my code...

$OpenAccess = "Open Access"
If WinExists($OpenAccess) Then Exit; It's already running
AutoItWinSetTitle($OpenAccess)

Dim $sql = "SELECT Family.MemberID, Family.MemberPassword FROM Family";SQL Statement
Dim $db = "FCA Database";Database connection setup in control panel.
Dim $file, $line, $linewrite;Just variables used several times.
Dim $file1 = "sql.txt";SQL Log File
Dim $file2 = "unapw.txt";Username and Password File
Dim $file3 = "new.txt";Final output
Dim $i;Line to start on

If Not(FileExists($file1)) Then
   Run('C:/Tools/sqlcmd.exe /db "' & $db & '" /silent /log "' & $file1 & '" /command "' & $sql & '"','',@SW_MINIMIZE)
   Sleep(500)
EndIf

$file = FileOpen($file1, 0)
If $file = -1 Then Exit
;$i = 9

While 1
   $line = FileReadLine($file);, $i)
   If @error = -1 Then ExitLoop
   $linewrite = StringReplace($line,"' '","|")
   $linewrite = StringTrimLeft($linewrite,1)
   $linewrite = StringTrimRight($linewrite,2)
   FileWriteLine($file2, $linewrite)
;   $i = $i + 1
WEnd

FileClose($file)

From there I get the following out put in about 15 seconds...

Please note that the above version is a shortened version.

I have tried the whole $i in another While statement and it didnt work in a timely manner... because it still took way too long. Is there a command (I cannot find it in the help file though I seem to remember one) that will tell me how many lines there are in a file? That way I could atleast remove the last 3 and the first 8 I would just have to wait almost 5 minutes. :-/

JS

<{POST_SNAPBACK}>

First of all there is a simpler way to do what you are doing in your code. Explaination: StringReplace the single quote to nothing and then StringReplace the space with a pipe. The code would look something like this(untested):

$OpenAccess = "Open Access"
If WinExists($OpenAccess) Then Exit; It's already running
AutoItWinSetTitle($OpenAccess)

Dim $sql = "SELECT Family.MemberID, Family.MemberPassword FROM Family";SQL Statement
Dim $db = "FCA Database";Database connection setup in control panel.
Dim $file, $line, $linewrite;Just variables used several times.
Dim $file1 = "sql.txt";SQL Log File
Dim $file2 = "unapw.txt";Username and Password File
Dim $file3 = "new.txt";Final output
Dim $i;Line to start on

If Not(FileExists($file1)) Then
  Run('C:/Tools/sqlcmd.exe /db "' & $db & '" /silent /log "' & $file1 & '" /command "' & $sql & '"','',@SW_MINIMIZE)
  Sleep(500)
EndIf

$file = FileOpen($file1, 0)
If $file = -1 Then Exit
;$i = 9

While 1
  $line = FileReadLine($file);, $i)
  If @error = -1 Then ExitLoop
  $linewrite = StringReplace($line,"'","")
  $linewrite = StringReplace($line," ","|")
  FileWriteLine($file2, $linewrite)
;   $i = $i + 1
WEnd

FileClose($file)

This cuts out only one line of code but it makes it easier to see what you are trying to do. It doesn't really matter which way you do it as either way apparently works.

As for your question about a command that tells you how many lines are in a file look at _FileCountLines from the File.au3 include file. I think all this does though is loop through the file and return the counter when the EOF is reached. I don't see how this will speed up the code but if you have an idea on how to use it... go for it.

*** Matt @ MPCS

Link to comment
Share on other sites

First of all there is a simpler way to do what you are doing in your code. Explaination: StringReplace the single quote to nothing and then StringReplace the space with a pipe. The code would look something like this(untested):

$OpenAccess = "Open Access"
If WinExists($OpenAccess) Then Exit; It's already running
AutoItWinSetTitle($OpenAccess)

Dim $sql = "SELECT Family.MemberID, Family.MemberPassword FROM Family";SQL Statement
Dim $db = "FCA Database";Database connection setup in control panel.
Dim $file, $line, $linewrite;Just variables used several times.
Dim $file1 = "sql.txt";SQL Log File
Dim $file2 = "unapw.txt";Username and Password File
Dim $file3 = "new.txt";Final output
Dim $i;Line to start on

If Not(FileExists($file1)) Then
  Run('C:/Tools/sqlcmd.exe /db "' & $db & '" /silent /log "' & $file1 & '" /command "' & $sql & '"','',@SW_MINIMIZE)
  Sleep(500)
EndIf

$file = FileOpen($file1, 0)
If $file = -1 Then Exit
;$i = 9

While 1
  $line = FileReadLine($file);, $i)
  If @error = -1 Then ExitLoop
  $linewrite = StringReplace($line,"'","")
  $linewrite = StringReplace($line," ","|")
  FileWriteLine($file2, $linewrite)
;   $i = $i + 1
WEnd

FileClose($file)

This cuts out only one line of code but it makes it easier to see what you are trying to do. It doesn't really matter which way you do it as either way apparently works.

As for your question about a command that tells you how many lines are in a file look at _FileCountLines from the File.au3 include file. I think all this does though is loop through the file and return the counter when the EOF is reached. I don't see how this will speed up the code but if you have an idea on how to use it... go for it.

*** Matt @ MPCS

<{POST_SNAPBACK}>

Thanks... the code I posted above doesnt cut any of the actual lines... it just trims the file left right and gets the id and pw in the proper format for a stringsplit() I will try what you posted above that will save a couple of lines of code. The reason I was wondering about getting how many lines there are the the file is because that is the only way I can think to get rid of the last 3.

I had tried using a null value in the stringreplace and iti didnt seem to work but that was probably because my code was new and not much was working. I will try it. I dont know what I am going to do about the rest of it. Maybe I will just make sure it only has to do all of that once that way it wont take 5 minutes everytime I run this program unless there are new members. Hrm... from here I need a command line FTP program. If you know of one off the top of your head please share... if not I will get one and see how well it works.

JS

AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Link to comment
Share on other sites

Thanks... the code I posted above doesnt cut any of the actual lines... it just trims the file left right and gets the id and pw in the proper format for a stringsplit() I will try what you posted above that will save a couple of lines of code. The reason I was wondering about getting how many lines there are the the file is because that is the only way I can think to get rid of the last 3.

I had tried using a null value in the stringreplace and iti didnt seem to work but that was probably because my code was new and not much was working. I will try it. I dont know what I am going to do about the rest of it. Maybe I will just make sure it only has to do all of that once that way it wont take 5 minutes everytime I run this program unless there are new members. Hrm... from here I need a command line FTP program. If you know of one off the top of your head please share... if not I will get one and see how well it works.

JS

<{POST_SNAPBACK}>

Why not just use Windows FTP? It is easy to use but I have never automated it with Autoit before so I don't know how easy that will be. I would search the forum... I am almost positive I have seen someone ask before.

*** Matt @ MPCS

Link to comment
Share on other sites

Sweet... I thought I had heard mention of that before. I will certainly give it a shot.

Thanks,

JS

AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Link to comment
Share on other sites

Well I finally have the list the exact way I want it. I will just have to make sure that when the script is ran it checks to see if that file exists before trying to create it again because it still takes 5 mins... if anyone can help with the code let me know...

$OpenAccess = "Open Access"
If WinExists($OpenAccess) Then Exit; It's already running
AutoItWinSetTitle($OpenAccess)

Dim $sql = "SELECT Family.MemberID, Family.MemberPassword FROM Family";SQL Statement
Dim $db = "FCA Database";Database connection setup in control panel.
Dim $file, $line, $linewrite;Just variables used several times.
Dim $file1 = "sql.txt";SQL Log File
Dim $file2 = "unapw.txt";Username and Password File
Dim $si;Start line integer
Dim $lines;$File1 Line Count
Dim $ei;End line integer

If Not(FileExists($file1)) Then
   Run('C:/Tools/sqlcmd.exe /db "' & $db & '" /silent /log "' & $file1 & '" /command "' & $sql & '"','',@SW_MINIMIZE)
   Sleep(1500)
EndIf

$file = FileOpen($file1, 0)
If $file = -1 Then Exit

While 1
   FileReadLine($file)
   If @error = -1 Then ExitLoop
   $lines = $lines + 1
WEnd

FileClose($file)

$lines = $lines - 10
$file = FileOpen($file1, 0)
If $file = -1 Then Exit
$ei = 1
$si = 9

While $ei <= $lines
   $line = FileReadLine($file, $si)
   If @error = -1 Then ExitLoop
   $linewrite = StringReplace($line,"' '","|")
   $linewrite = StringReplace($linewrite, "'","")
   FileWriteLine($file2, $linewrite)
   $si = $si + 1
   $ei = $ei + 1
WEnd

FileClose($file)

From here I'm going to get working on the FTP (I am going to start out messing with the one for windows). Then I will move on to other things if necessary. Then I will figure out the rest of this program... thanks Matt for everything so far :)

JS

AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Link to comment
Share on other sites

Hrm... I need to use an FTP program that can search the files and download directories... :)

I think I may have to use a FTP GUI program that will stink.

JS

AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...