Sign in to follow this  
Followers 0
gcue

sqlite synatx different than sql syntax?

8 posts in this topic

#1 ·  Posted (edited)

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 by gcue

Share this post


Link to post
Share on other sites



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


*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

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

$sql_db & ".Table.Field" ??

thanks for the info

Share this post


Link to post
Share on other sites

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

don't get any errors though. =)

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by ProgAndy

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

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)) ")

Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

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.

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

works great progandy!

thanks for your help!

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
Sign in to follow this  
Followers 0