sqlite synatx different than sql syntax?

[gcue]

hello.

is it possible to reference fields using this format?

field.table

i am trying to replace some text within the ScriptDirectory field. Asset is an existing field within the same aScriptUpdater table.

_SQLite_Startup()
_SQLite_Open($sql_db)

_SQLite_Exec(-1, "UPDATE aScriptUpdater SET ScriptDirectory=REPLACE(ScriptDirectory, 'C:', '' & Asset.aScriptUpdater & 'c$')")

_SQLite_Close()
_SQLite_Shutdown()

the error i get is:

Error: no such column: Asset.aScriptUpdater

thanks in advance! Edited June 5, 2012 by gcue

[ProgAndy]

In SQL it is always database.table.field or table.field or field

[gcue]

ah ok.. how do i reference database using sqlite? would it be

$sql_db & ".Table.Field" ??

thanks for the info

[gcue]

also are my quotes and & correcly placed? because it still doesn't work with Table.Field

don't get any errors though. =)

[ProgAndy]

Just

ah ok.. how do i reference database using sqlite? would it be

$sql_db & ".Table.Field" ??

thanks for the info

If you don't use ATTACH DATABASE or temporary tables, you can ignore the database name. Otherwise it is "main" for the database opened with _Sqlite_open, "temp" for additaional databases attached with ATTACH ... it is the name specified in the command.

If you need more information, refer to the SQLite docs: http://www.sqlite.org/lang_createtable.html

Edit: String concatenation is done with ||:

"UPDATE aScriptUpdater SET ScriptDirectory=REPLACE(ScriptDirectory, 'C:', '' || aScriptUpdater.Asset || 'c$')

Edited June 5, 2012 by ProgAndy

[hannes08]

I don't think you really have a problem with your tables.

I tried the following:

#include <SQLite.au3>
#include <SQLite.dll.au3>

Local $hQuery, $aRow
_SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open()
_SQLite_Exec(-1, "Create table tblTest (a,b);" & _
        "Insert into tblTest values ('C:tempscript1.au3','PC01');" & _
        "Insert into tblTest values ('C:tempscript2.au3','PC02');" & _
        "Insert into tblTest values ('C:tempscript3.au3','PC03');")

Local $d = _SQLite_Exec(-1, "Select a, b From tblTest", "_cb") ; _cb will be called for each row
_SQLite_Exec(-1, "UPDATE tblTest SET a=(REPLACE(a,'C:',b)) ")
$d = _SQLite_Exec(-1, "Select a, b From tblTest", "_cb") ; _cb will be called for each row

Func _cb($aRow)
    For $s In $aRow
        ConsoleWrite($s & @TAB)
    Next
    ConsoleWrite(@CRLF)
EndFunc   ;==>_cb

_SQLite_Close()
_SQLite_Shutdown()

It works correctly.

If I try to prepend something to the REPLACE statement it doesn't work. E.g.:

_SQLite_Exec(-1, "UPDATE tblTest SET a=('x' & REPLACE(a,'C:',b)) ")

[ProgAndy]

If I try to prepend something to the REPLACE statement it doesn't work. E.g.:

_SQLite_Exec(-1, "UPDATE tblTest SET a=('x' & REPLACE(a,'C:',b)) ")

SQLite uses || for string concatenation.

[gcue]

works great progandy!

thanks for your help!