insert to database

[haribo]

i have 2 fields , name, vorname in the stack, this script in the button for sqlite 3, connection is correct,

on mouseUp
global gConID

put field "name" into tname
put field "vorname" into tvorname

answer tname, tvorname

revExecuteSQL gConID,"insert into Schueler(name, vorname) VALUES(:1,:2)",tname, tvorname
handleRevDBerror tResult
if the result is not empty then
answer warning the result
exit mouseUp
end if
answer information "Number of rows added: " & return & tResult

end mouseUp

add some letters to fields and press the button, there is only a new schuelerID, in the fields of table schueler, name, vorname, nothing.
tested on mac with sqlite databrowser.

??
Ralle

[haribo]

hi,
whats wrong, 21 views, no reply, please help.
rallle

[Janschenkel]

Hi Ralle,

Don't be alarmed by multiple views without a solution - not everybody is familiar with the intricacies of SQL databases - in fact, many Revolution users wanted to get away from those beasties in the first place

You don't pass the variables themselves, but rather the names of the variables, as the extra parameters for revExecuteQuery:

Code: Select all

revExecuteSQL gConID, "insert into Schueler(name, vorname) VALUES(:1,:2)", "tname,tvorname"

The database command will then look for a variable with that name and insert its contents. It may seem counter-intuitive but it is actually a compromise that allows you to pass binary data as well, by prepending a '*b' to the name of the variable:

Code: Select all

revExecuteSQL gConID, "insert into Schueler(name, binarystuff) VALUES(:1,:2)", "tname,*bbinarystuff"

Sometimes the database library has its peculiarities, but once you get the hang of it and read the fineprint in the documentation, you can build a database front-end in far less time than your PHP or Java buddies.

Jan Schenkel.

[haribo]

thanks for help, my second name is "nervious"
it was a little mistake, i mean this one "yxz" oops,

there is no way, tried with "*b" or this
"local tSQL
put "INSERT INTO Example(" & return & \
"tname, name)" & \
"VALUES(" & return & \
":1, :2,)" into tSQL


local tFirstname
put field "name" into tname

..................


local tResult
revExecuteSQL gConID tSQL, "tname"
put the result into tResult "

nothing appears

only a new schuelerID nothing in column
tell me " number of rows added: 1

in another way it tells 2 rows of 2 columns added, but the same, nothing in DB, please dont ask me this way, nothing in db and clich away, oops
its not a problem with sqlite database browser, in textedit its the same, columns ok , but nothing of data
i hve read many of sqlite, but i cant find the mistake, i think its a problem with "," or ";"


the code of table Schueler
"on preopenstack
global gConId
local tSQL, tResult
put "create table Schueler(schuelerID integer primary key autoincrement,name Text,vorname Text)" into tSQL
revExecuteSQL gConID, tSQL
put the result into tResult
if the result is not empty then
answer warning the result
exit preopenstack
end if
answer information "Number of Tables added" & tResult
end preopenstack
"
???????

then i try this
revExecuteSQL gConID,"insert into Schueler(SchuelerID,name, vorname) VALUES(:1,:2);", tname, tvorname

the answer is " number of added rows : 2 values in 3 columns"
but there is no bb or yyy
Ralle

[Janschenkel]

Hi Ralle,

When I look at the following line:

Code: Select all

revExecuteSQL gConID,"insert into Schueler(SchuelerID,name, vorname) VALUES(:1,:2);", tname, tvorname

I see that you're mixing a number of things.

First off, the VALUES clause has one field less than the number of fields that you put in parentheses right after the table name.
The second bit is that you have to use variable names with quotes around them, as the 'variable list'. The following worked for me:

Code: Select all

put 12345 into tID
put "Schenkel" into tName
put "Jan" into tVorname
revExecuteSQL gConID, "INSERT INTO Schueler (schuelerID,name,vorname) VALUES(:1,:2,:3)", "tID", "tName", "tVorname"

Hope this helped,

Jan Schenkel.

[haribo]

Thanks for help.
There was 2 silly mistakes, the quotes and "insert must be Insert".
now works
Ralle