Sign in to follow this  
Followers 0
BigyBG

Help whith SQLite change/update single field

8 posts in this topic

Can someone explain to me how to change or update entries in a single field from an existing table?

Thank you in advance for the help :D

Share this post


Link to post
Share on other sites



Use _SQLite_Exec with "UPDATE"


Share this post


Link to post
Share on other sites

If you need to update a field based on a key value (typically in col. 1), you could write your own little UDF to do the job. Either by hard coding the field name or by specifying the field name as a variable. Depends on your needs

So you could call the function like this

; _SQLiteUpdate($id, $field, $newvalue)
_SQLiteUpdate(1703, size, "L")       ; change field size to value L for id=1703

The SQL sentence inside must be something like

UPDATE table set $field = $newvalue where idfield=$id

It is a good idea to quote things in SQLite, so values are put between ' and '


I am just a hobby programmer, and nothing great to publish right now.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

i use this

this function wil find a value in a colom and then change a valua in a colom on the sam row

update functie
; zoekt de opgegeven waarde in het opgegeven tabel en verander een waarde in de opgegeven colum
; gebruik _sqlite_update( $hDB,$sTable,$ColumFind,$sValueFind,$ColumChange,$sValueChange)
; $hDB = An open database, use -1 To use last opened database
; $sTable = The Name Of the Tabel to be search
; $ColumFind = The Name Of the Colum to be search in the Tabble
; $sValueFind = The Value to be search In the Colum
; $ColumChange = The to change the colum in the Tabble
; $sValueChange = The to change the Value  in the Colum
 
func _SQLite_update( $hDB,$sTable,$sColumFind,$sValueFind,$sColumChange,$sValueChange)
 
$SQLFINDSTRING = "UPDATE "&$sTable&" SET "&$sColumChange&" = '"&$sValueChange&"' WHERE "&$sColumFind&" = '"&$sValueFind&"';"
_SQLite_Exec ($hDB,$SQLFINDSTRING)
 
EndFunc
Edited by BartW

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

@BartW, OP

Don't simply surround litteral text values with single quotes: that's not enough to protect you from SQL errors at runtime.

Why? Because if a litteral itself contains a single quote, it will terminate the litteral. Embedded single quotes should be doubled.

UPDATE mytable set mycolumn = 'O'Connor' where ... <--- Syntax error after 'O'

UPDATE mytable set mycolumn = 'O''Connor' where ... <--- will work

You should wrap your string litterals using _SQLite_Escape (or the much faster _SQLite_Fast_Escape() offered with the last beta).

For brevity, I use the following equivalent:

Func X($sValue)
  Return(StringReplace($sValue, "'", "''"))
EndFunc

And then:

$sName = "O'Connor"
_SQLite_Exec($hDB, "UPDATE mytable set mycolumn = " & X($sName) & " where ...")

BTW doing so also protects you from SQL injection attacks, even if such attack is improbable in an embedded DB context. Anyway, it's a sane habit to grab.

While I'm at it, you must know you need to use square brackets around schema names when they may otherwise lead to SQL error:

SELECT [isn't that a strange name for a ÁÇÌþæ÷Ĉ column?] FROM [my table has a long name with spaces] WHERE [where] LIKE 'abc%'

SQLite also accepts double quotes instead of square brackets:

SELECT "Isn't that a strange name for a ÁÇÌþæ÷Ĉ column?" FROM "my table has a long name with spaces" WHERE "where" LIKE 'abc%'

While SQLite will do its best to disambiguate wrongly quoted statements, you should avoid mixing roles of single- and double-quotes.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Thanx jchd

Hmm I must revise my scrips

well I learn something new every day :D

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

oke i think i got it now.

and fore the starter of this tropic .

http://www.sqlite.org/lang.html

func _SQLite_update( $hDB,$sTable,$sColumFind,$sValueFind,$sColumChange,$sValueChange)
 
$SQLFINDSTRING = "UPDATE ["&$sTable&"] SET ["&$sColumChange&"] = "&_SQLite_Escape($sValueChange)&" WHERE ["&$sColumFind&"] = "&_SQLite_Escape($sValueFind)&";"
_SQLite_Exec ($hDB,$SQLFINDSTRING)
 
EndFunc
Edited by BartW

Share this post


Link to post
Share on other sites

Fine, even is that looks like a bit of a mouthful, it's rock solid this way.

In practice you often have to perform more complex updates of several columns, sub-selects with joins, more conditions, etc.

The practical way is to forget about fixed-form SQL statements and write them in the flow of your program. You know your tables and know which one need [ ] quoting, same with data: when you know you have a correctly formatted text date (e.g. 2010-10-31) you know you don't have to escape it since it will never contain a single quote. Of course it's obviously safer to always escape fields like persons' names, addresses, items descriptions, comments, ... as they might have one or more single quote embedded.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

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