- This topic has 6 replies, 5 voices, and was last updated 6 years ago by
.
-
Topic
-
All, I am writing some tcl code to generate a report. For the report, I need access to an active smatdb file. I do not want to cycle the smat file so I am copying it to an archive directory. When I attempt to access the file, it is giving me the following error:
file is encrypted or is not a database
My code is attempting to access the copied smatdb file and unencrypt it. Can someone point me to where I’m going wrong? I’m a relative newbie at SQLite stuff, so please be gentle! Below is my code:
DBCMD eval {PRAGMA KEY=’$site’;
ATTACH DATABASE $decryptsmatdb AS plaintext KEY ”;
SELECT sqlcipher_export(“plaintext”);
DETACH DATABASE $decryptsmatdb | sqlcipher $SMATCHGdb }
Thanks….
Tom Rioux
-
Replies
-
-
Hi Tom,
I would be curious to see the rest of your code. I am assuming you are doing a sqlite command to create the DBCMD command. I am also assuming there is code to set $site and $decryptsmatdb. That code isn’t included.
But the first thing I wonder about is if variable substitution is happening since you’ve surrounded your database commands in curly braces. You might try the same thing with a series of DBCMD eval commands, one for each separate database command. Also, try enclosing each command in double quotes instead of the curly braces. So, something like…
DBCMD eval “PRAGMA KEY=’$site'”
Good luck.
Bill
-
Thanks for the reply Bill. I’ve posted the code below. I’ve attempted just about everything you have mentioned. I’ve been working with Max Drown of Infor and Glen Goldsmith of Christus. They have given me lots of good advice. I’ve also tried from the command line but still get the encryption error.
Just some insight into my code.
The “SMATCHGdb” will be one of two locations where the smat files are located. One location is a where the cycled files are archived. The other is where some “live” smat files are copied to.
The portion of code in the “else” statement is working correctly. The one in the “if” statement is the one that isn’t working.
I’m sure the code in the IF portion needs some tweaking. I’ve tweaked it over and over and this was my last attempt to get it to work. Since then I’ve been trying command line to see if my commands are working.
set SMATCHGdb [file join $smatdir $fn ]
sqlite3 DBCMD $SMATCHGdb
DBCMD timeout 5000
set decryptsmatdb $fn
if {[file exists $fn] && [string equal $smatdir $svdir]} {
DBCMD eval {PRAGMA KEY=’$site’;
ATTACH DATABASE $decryptsmatdb AS plaintext KEY ‘$site’;
SELECT sqlcipher_export(“plaintext”);
DETACH DATABASE $decryptsmatdb | sqlcipher $SMATCHGdb }
} else {
# DBCMD eval {PRAGMA key=’$site’}
set chgcnt [DBCMD eval {SELECT COUNT(*) FROM smat_msgs}]
sqlite3 DBCMD $chgdb
DBCMD eval {INSERT INTO CHGDATA (thread, count, grp, day) VALUES ($text, $chgcnt, $group, $date); }
}
-
Tom,
We have written a command line program to gain access to the SMATDB files and have had luck running the code below. The one thing that I noticed about yours is that you have sqlcipher that I am not familiar with. I am not sure if you can use the PIPE to sqlcipher that way in the DBCMD. Of course I could be wrong but I have no idea.
This is not the full program below and some of the logic was taken from Charlie’s hcismatdb program that he posted a while back. ( Thanks Charlie for the great work over the years ). Some of the logic is old and just copied from other programs.
There are a lot of command line options that my program takes to make certain decisions about which files to choose, which sqlcmd to build, etc.
Code:
cd $HciRootDir/$site/exec/processesset SmatFiles [recursive_glob ./ “*${thread}*smatdb”]
if { $optd } { puts “SmatFiles == $SmatFiles” }if { [llength $SmatFiles] $TypeOfSql”}
set whatToFind [string toupper $whatToFind]
if { $optH == 0 } {
switch -exact — $TypeOfSql {
“hour” {
set sqlcmd “select MessageContent,DestConn,DriverCtl,UserData,SourceConn,Time,TimeIn,TimeOut
from smat_msgs where UPPER(MessageContent) like ‘%$whatToFind%’ and
TimeIn >= $FirstTime;”
}
“SingleDay” {
set sqlcmd “select MessageContent,DestConn,DriverCtl,UserData,SourceConn,Time,TimeIn,TimeOut
from smat_msgs where UPPER(MessageContent) like ‘%$whatToFind%’ and
TimeIn >= $MidnightOf and TimeOut = $MidnightOfBegin and TimeOut = $FirstTime;”
}
“SingleDay” {
set sqlcmd “select MessageContent,DestConn,DriverCtl,UserData,SourceConn,Time,TimeIn,TimeOut
from smat_msgs where UPPER(DriverCtl) like ‘%$whatToFind%’ and
TimeIn >= $MidnightOf and TimeOut = $MidnightOfBegin and TimeOut <= $MidnightToEnd;"
}
default {
set sqlcmd "select MessageContent,DestConn,DriverCtl,UserData,SourceConn,Time,TimeIn,TimeOut
from smat_msgs where UPPER(DriverCtl) like '%$whatToFind%';"
}
}
}
if { $optd } { puts "sqlcmd == $sqlcmd" }
foreach File $SmatFiles {
if { $optd } { puts "File == $File" }
# checking for regular file.
if {[catch {open $File rb} fd]} {
puts stderr "n Cannot open $Filen"
continue
}
if {[string toupper [read $fd 6]] eq "SQLITE"} {
set encrypt 0
} else {
# Assume encrypted
set encrypt 1
}# Close the file
close $fdlassign "" msg DestConn DriverCtl UserData SourceConn TimeSaved
set dbName "$File"
sqlite DBCMD $dbName -readonly 1
DBCMD timeout 4000# If suspect encrypted issue PRAGMA
if {$encrypt} {DBCMD eval "PRAGMA KEY='$site'"}
# Query for tables get names to see if SMAT DB
if {[catch { DBCMD eval "SELECT name FROM sqlite_master WHERE ENGINE='table'"} rslt]} {
DBCMD close
puts stderr "n Cannot query $File as SMAT DB – $rsltn"
if {$encrypt} {
puts stderr "Make sure site name is correct for this SMAT DB!n"
}
continue
}
if {![regexp -nocase — {smat_msgs} $rslt]} {
DBCMD close
puts stderr "n $File is sqlite but does not seem to be a SMAT DB file!n"
continue
}
catch {unset x}
DBCMD eval $sqlcmd x {
set msg $x(MessageContent)
set DestConn $x(DestConn)
set DriverCtl $x(DriverCtl)
set UserData $x(UserData)
set SourceConn $x(SourceConn)
set TimeSaved $x(Time)
set TimeIn $x(TimeIn)
set TimeOut $x(TimeOut)
if { [string equal $msg ""] } {
continue
}
set milli [string range $TimeSaved 10 end]
set TimeSaved [string range $TimeSaved 0 9]
if { $optw } { puts $ofileid "$msg" }
incr OutCnt
regsub -all "n" $msg "r" msg
regsub -all "rr" $msg "r" msgif { ! $optx && ! $optt } {
puts "Found in file: $File"
set FirstThree [string range $msg 0 2]
if { [string equal $FirstThree "MSH"] } {
set segments [split $msg r]
foreach dispSeg $segments {
puts $dispSeg
}
} else {
puts "[pretty-print-xml $msg]"
}
if { $opth } {
puts "tDriver Control Info: $DriverCtl"
if { ! [string equal $SourceConn ""] } { puts "tSource Interface: $SourceConn" }
if { ! [string equal $UserData ""] } { puts "tMsg User Data: $UserData" }
}
puts "ntDate/Time saved: [clock format $TimeSaved -format "%D %T.$milli"]"
puts ""
flush stdout
}
}
DBCMD close
} -
Rob,
In your example above, is there any logic wrapped around the Time, TimeIn, and TimeOut values that are returned from your query?
I’m asking b/c in my current environment, the time values are returning to me as clock clicks rather than clock seconds. As is, I’m having difficulties getting a useable time value from my smatdb queries. else, should I be using clock clicks and converting to clock seconds somehow?
-
-
-
- The forum ‘Cloverleaf’ is closed to new topics and replies.
