SQLite - ATTACH and SELECT from different db

[Iczer]

I need to copy difference between 2 db-s into temp memory db make something with it and then copy entries from memory db to one of the two db-s,

but update statement return error in whatever form i try:

expandcollapse popup

$SQLITE_OPEN_URI = 0x40

#include <MsgBoxConstants.au3>
#include <SQLite.au3>

$sPathTo_SourceDB = "E:\Auto-it scripts\testDB_S.sqlite"

$sPathToSDB = StringRegExpReplace($sPathTo_SourceDB, "\\", "/")
$sPathToSDB = StringRegExpReplace($sPathToSDB, "\h", "%20")

$sPathTo_DestinDB = "E:\Auto-it scripts\testDB_D.sqlite"

$sPathToDDB = StringRegExpReplace($sPathTo_DestinDB, "\\", "/")
$sPathToDDB = StringRegExpReplace($sPathToDDB, "\h", "%20")

Local $aResult, $iRows, $iColumns, $iRval

_SQLite_StartupEX2("sqlite3.dll")
$hDb = _SQLite_Open(':memory:',$SQLITE_OPEN_READWRITE + $SQLITE_OPEN_URI)

_SQLite_Exec($hDb,"BEGIN; CREATE TABLE IF NOT EXISTS DeltaDB (HKey TEXT PRIMARY KEY NOT NULL, HVal TEXT COLLATE NOCASE);COMMIT;")
_SQLite_Exec($hDb,"ATTACH DATABASE 'file:///" & $sPathToSDB & "?cache=private&mode=ro' AS SourceDB;")
_SQLite_Exec($hDb,"ATTACH DATABASE 'file:///" & $sPathToDDB & "?cache=private&mode=rw' AS DestinDB;")

_SQLite_Exec($hDb,"UPDATE OR IGNORE DeltaDB SET HKey = SourceDB.CasheDB.HKey, HVal = SourceDB.CasheDB.HVal WHERE NOT EXIST " & _ 
                                                                                                    "(SELECT DestinDB.CasheDB.HKey FROM DestinDB.CasheDB WHERE DestinDB.CasheDB.HKey = SourceDB.CasheDB.HKey)")

$iRval = _SQLite_GetTable2d($hDb, "SELECT * FROM DeltaDB;", $aResult, $iRows, $iColumns)

If $iRval = $SQLITE_OK Then
    _SQLite_Display2DResult($aResult)
Else
    MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf

_SQLite_Close($hDb)
_SQLite_Shutdown()

 

!   SQLite.au3 Error
--> Function: _SQLite_Exec
--> Query:    UPDATE OR IGNORE ImgDelta SET LinkHash = SourceDB.CasheDB.LinkHash, LocalPath = SourceDB.CasheDB.LocalPath WHERE NOT EXIST (SELECT DestinDB.CasheDB.LinkHash FROM DestinDB.CasheDB WHERE DestinDB.CasheDB.LinkHash = SourceDB.CasheDB.LinkHash)
--> Error:    near "SELECT": syntax error

how i can correct this query?

 

 

[jchd]

33 minutes ago, Iczer said:

WHERE NOT EXIST (SELECT

WHERE NOT EXISTS (SELECT

[Iczer]

Thanks! I didn't see it...
 but now error changed to:

!   SQLite.au3 Error
--> Function: _SQLite_Exec
--> Query:    UPDATE OR IGNORE ImgDelta SET LinkHash = SourceDB.CasheDB.LinkHash, LocalPath = SourceDB.CasheDB.LocalPath WHERE NOT EXISTS (SELECT DestinDB.CasheDB.LinkHash FROM DestinDB.CasheDB WHERE DestinDB.CasheDB.LinkHash = SourceDB.CasheDB.LinkHash)
--> Error:    no such column: SourceDB.CasheDB.LinkHash

which is wrong - those columns identical to all tables...

[Skysnake]

Look at this for inspiration

[jchd]

49 minutes ago, Iczer said:

UPDATE OR IGNORE ImgDelta SET

Shouldn't that be:

UPDATE OR IGNORE DeltaDB SET ...

because:

51 minutes ago, Iczer said:

CREATE TABLE IF NOT EXISTS DeltaDB

 

[jchd]

@Iczer, aren't you reinventing sqldiff.exe ?

Also why did you use CachedDB and not implicit main?