Help with SQLite

[antmar904]

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.

expandcollapse popup

#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 June 5, 2017 by antmar904

[HighlanderSword]

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

[antmar904]

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.

[jchd]

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

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

 

[antmar904]

@jchd

That worked great, thank you.

[antmar904]

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?

[jchd]

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 June 8, 2017 by jchd
Sorry wrong paste; fixed

[antmar904]

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.

[jchd]

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.

[antmar904]

That worked.  Thank you @jchd