antmar904

Help with SQLite

10 posts in this topic

#1 ·  Posted (edited)

I am trying to write some simple information to a SQLite db but this is not working.  I get no error messages or output written to the log file.

#NoTrayIcon
#RequireAdmin
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <ColorConstantS.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>
#include <FileConstants.au3>
#include <StringConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

Global $Log = "C:\Windows\MSICACHE\GP310Installation\GP310Install.log"

$hFile = FileOpen($Log, 9)

Func _SQL()
    Local $Db = "C:\Temp\GP.db"
    Local $CompName = @ComputerName
    Local $User = @UserName
    Local $IP = @IPAddress1

    _SQLite_Startup()
    If @error Then
        FileWriteLine($Log, @MON & "/" & @MDAY & "/" & @YEAR & " - " & @HOUR & ":" & @MIN & ":" & @SEC & " SQL Error: SQLite.dll can't be loaded!.")
    EndIf

    ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF);debug

    _SQLite_Open($Db) ; Opens database
    If @error Then
        FileWriteLine($Log, @MON & "/" & @MDAY & "/" & @YEAR & " - " & @HOUR & ":" & @MIN & ":" & @SEC & " SQL Error: Cannot create DB!.")
    EndIf

    If Not _SQLite_Exec(-1, "INSERT INTO Computers (ComputerName,User,IP) VALUES (" & _SQLite_FastEscape($CompName) & "," & _SQLite_FastEscape($User) & "," & _SQLite_FastEscape($IP) & ")") = $SQLITE_OK Then _
        FileWriteLine($Log, @MON & "/" & @MDAY & "/" & @YEAR & " - " & @HOUR & ":" & @MIN & ":" & @SEC & " SQL Error: " & _SQLite_ErrMsg())
        FileClose($hFile)
    _SQLite_Close($Db)
    _SQLite_Shutdown()
EndFunc   ;==>_SQL

 

Edited by antmar904

Share this post


Link to post
Share on other sites



#2 ·  Posted

IS the above code complete ? I dont see where you are calling the function _Sql

Share this post


Link to post
Share on other sites

#3 ·  Posted

ha!

I copied this func from another one of my scripts and forgot to call the func.  Loooooong day.

Ok that is working.

I have the script add some info to the db at the beginning of the script but how would I add data (exit code) to the table at the end of my script?

In the image Row 1 is the data that gets entered at the beginning of my script then at the end of my script depending on how things went I want to add the exitcode (in this case 62) to the same line matching the computer name the script is running on.

Capture.GIF

Share this post


Link to post
Share on other sites

#4 ·  Posted

You need something like this (adapt to your use case):

_SQLite_Exec(-1, "update computers set exitcode = " & 62 & " where computername = " & _SQLite_FastEscape($CompName))

 


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

#5 ·  Posted

@jchd

That worked great, thank you.

Share this post


Link to post
Share on other sites

#6 ·  Posted

If a user runs my script multiple times, it will create a new record in the table.

How can I make my script create/update the record for the same computer name if it already exist in the table?

Capture.GIF

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

Check for existance first:

Local $aRow
Local $ret = _SQLite_QuerySingleRow(-1, "select 1 from computers where computername = " & _SQLite_FastEscape($CompName), $aRow)
If $aRow[0] = 0 Then
    ; the row doesn't exist yet
    _SQLite_Exec(-1, "insert into computers (...............")
Else
    ; the row already exists
    _SQLite_Exec(-1, "update computers set ............... where ")
EndIf

 

Edited by jchd
Sorry wrong paste; fixed

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

#8 ·  Posted

I can't get it to work:

I just want one record per computer no matter how man times my install script runs on a computer.

The only

Local $aRow
Local $ret = _SQLite_QuerySingleRow(-1, "select 1 from Computers where ComputerName = " & $CompName & ", $aRow)
If UBound($aRow) = 0 Then
    ; the row doesn't exist yet
    If Not _SQLite_Exec(-1, "INSERT INTO Computers (Date,ComputerName,User,IP) VALUES (" & _SQLite_FastEscape($Date) & "," & _SQLite_FastEscape($CompName) & "," & _SQLite_FastEscape($User) & "," & _SQLite_FastEscape($IP) & ")") = $SQLITE_OK Then _
            FileWriteLine($Log, @MON & "/" & @MDAY & "/" & @YEAR & " - " & @HOUR & ":" & @MIN & ":" & @SEC & " SQL Error: " & _SQLite_ErrMsg())
Else
    ; the row already exists
    _SQLite_Exec(-1, "UPDATE Computers SET Date = " & $Date & " WHERE ComputerName = " & _SQLite_FastEscape($CompName))
    _SQLite_Exec(-1, "UPDATE Computers SET User = " & $User & " WHERE ComputerName = " & _SQLite_FastEscape($CompName))
    _SQLite_Exec(-1, "UPDATE Computers SET IP = " & $IP & " WHERE ComputerName = " & _SQLite_FastEscape($CompName))
EndIf

I want to update is Date, User and IP address.

Share this post


Link to post
Share on other sites

#9 ·  Posted

Sorry it was a copy/paste typo.

Try this snippent instead:

Local $aRow
Local $ret = _SQLite_QuerySingleRow(-1, "select 1 from Computers where ComputerName = " & _SQLite_FastEscape($CompName), $aRow)
If $aRow[0] = 0 Then
    ; the row doesn't exist yet
    If Not _SQLite_Exec(-1, "INSERT INTO Computers (Date,ComputerName,User,IP) VALUES (" & _SQLite_FastEscape($Date) & "," & _SQLite_FastEscape($CompName) & "," & _SQLite_FastEscape($User) & "," & _SQLite_FastEscape($IP) & ")") = $SQLITE_OK Then _
            FileWriteLine($Log, @MON & "/" & @MDAY & "/" & @YEAR & " - " & @HOUR & ":" & @MIN & ":" & @SEC & " SQL Error: " & _SQLite_ErrMsg())
Else
    ; the row already exists
    _SQLite_Exec(-1, "UPDATE Computers SET Date = " & _SQLite_FastEscape($Date) & ", User = " & _SQLite_FastEscape($User) & ", IP = " & _SQLite_FastEscape($IP) & " WHERE ComputerName = " & _SQLite_FastEscape($CompName))
EndIf

You can update as many columns as needed in a single UPDATE statement.


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

#10 ·  Posted

That worked.  Thank you @jchd

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