problem inserting into sqlite

[hansel.rios]

i am new to database, I am following the turorias but im stuck. I was wondering if the following code is written correctly or if even possible. please help.

[code]
TextField = native.newTextField( 50, 150, 220, 36, handlerFunction )
local testvalue = {}

local function AddSQL(tap)

testvalue[1] = “’”… TextField.text … “’”
local insertQuery = [[INSERT INTO estudiantes VALUES (NULL, testvalue[1],‘This is an unknown person.’, ‘1234’, ‘09231990’); ]]
db:exec( insertQuery )

– Hide keyboard
native.setKeyboardFocus( nil )

end
botonInsert:addEventListener(“tap”,AddSQL)

local function ShowSQL(tap)

for row in db:nrows(“SELECT * FROM estudiantes”) do
local text = row.nombre…" “…row.apellido …” "…row.dob
local t = display.newText(text, 20, 30 * row.id, null, 40)
t:setTextColor(255,0,255)
end

– Hide keyboard
native.setKeyboardFocus( nil )

end
botonShow:addEventListener(“tap”,AddSQL)

[import]uid: 123133 topic_id: 33347 reply_id: 333347[/import]

[jkrassman]

Hi,

Can you tell me exactly how the table is designed?

Joakim

[import]uid: 81188 topic_id: 33347 reply_id: 132427[/import]

[hansel.rios]

[code]
require “sqlite3”
local path = system.pathForFile( “data.db”, system.DocumentsDirectory )
local db = sqlite3.open( path )

–Create
local tablesetup = [[CREATE TABLE IF NOT EXISTS estudiantes (id INTEGER PRIMARY KEY autoincrement, nombre, apellido, password, dob);]] --dob = date of birth
db:exec( tablesetup )

local botonInsert = display.newRect(0, 0, 250, 100);
botonInsert.x = 150; botonInsert.y = 950;
local botonShow = display.newRect(0,0, 250, 100);
botonShow.x = 450; botonShow.y = 950;

[import]uid: 123133 topic_id: 33347 reply_id: 132435[/import]

[jkrassman]

Give this a try!

  
 local insertQuery = "INSERT INTO estudiantes (nombre, apellido, password, dob) VALUES (" .. testvalue[1] .. ",'This is an unknown person.', '1234', '09231990','test')";  
  

One more thing, try download a graphical interface for sqlite. It is much easier to debug and you can print your sql statments and paste them into these tools and see if the query is correct.

I am using Base and it is a great tool and have saved me tons of hours debugging

Joakim [import]uid: 81188 topic_id: 33347 reply_id: 132445[/import]

[rob]

To expand on this:

 testvalue[1] = "'".. TextField.text .. "'"  
 local insertQuery = [[INSERT INTO estudiantes VALUES (NULL, testvalue[1],'This is an unknown person.', '1234', '09231990'); ]]  

Lets say your TextField.text has the value of “Fred”, it’s just a string.

The SQL Query you want is:

INSERT INTO estudiantes VALUES (NULL, ‘Fred’,‘This is an unknown person.’,‘1234’,‘09231990’);

So we need to find a way to substitute our string “Fred” in the middle of the string. Now Corona supports 3 types of sting quotes: single quotes (’), double quotes (") and the double square brackets.

“This is a string” and
[[This is a string]] are the same. The [[]] quotes allow you to have multi-line strings, but for this case they are the same.

Look at this string:

 local insertQuery = [[INSERT INTO estudiantes VALUES (NULL, ']] .. TextField.text .. [[','This is an unknown person.', '1234', '09231990');]]  

Where this differs from your version is first, we don’t need to make a string with the quotes on it. This lets you use the text straight out of the text field. We are splitting the query into 3 part. The first half which you see end’s in a single quote then we use the … operator to append the textfield string and then another … operator to append the rest of the query string. Note again, I close the single quotes around the text field just inside the [[ part of this.

Hope that helps you understand what’s going on.

Rob
[import]uid: 199310 topic_id: 33347 reply_id: 132495[/import]

[jkrassman]

Hi,

Can you tell me exactly how the table is designed?

Joakim

[import]uid: 81188 topic_id: 33347 reply_id: 132427[/import]

[hansel.rios]

[code]
require “sqlite3”
local path = system.pathForFile( “data.db”, system.DocumentsDirectory )
local db = sqlite3.open( path )

–Create
local tablesetup = [[CREATE TABLE IF NOT EXISTS estudiantes (id INTEGER PRIMARY KEY autoincrement, nombre, apellido, password, dob);]] --dob = date of birth
db:exec( tablesetup )

local botonInsert = display.newRect(0, 0, 250, 100);
botonInsert.x = 150; botonInsert.y = 950;
local botonShow = display.newRect(0,0, 250, 100);
botonShow.x = 450; botonShow.y = 950;

[import]uid: 123133 topic_id: 33347 reply_id: 132435[/import]

[jkrassman]

Give this a try!

  
 local insertQuery = "INSERT INTO estudiantes (nombre, apellido, password, dob) VALUES (" .. testvalue[1] .. ",'This is an unknown person.', '1234', '09231990','test')";  
  

One more thing, try download a graphical interface for sqlite. It is much easier to debug and you can print your sql statments and paste them into these tools and see if the query is correct.

I am using Base and it is a great tool and have saved me tons of hours debugging

Joakim [import]uid: 81188 topic_id: 33347 reply_id: 132445[/import]

[rob]

To expand on this:

 testvalue[1] = "'".. TextField.text .. "'"  
 local insertQuery = [[INSERT INTO estudiantes VALUES (NULL, testvalue[1],'This is an unknown person.', '1234', '09231990'); ]]  

Lets say your TextField.text has the value of “Fred”, it’s just a string.

The SQL Query you want is:

INSERT INTO estudiantes VALUES (NULL, ‘Fred’,‘This is an unknown person.’,‘1234’,‘09231990’);

So we need to find a way to substitute our string “Fred” in the middle of the string. Now Corona supports 3 types of sting quotes: single quotes (’), double quotes (") and the double square brackets.

“This is a string” and
[[This is a string]] are the same. The [[]] quotes allow you to have multi-line strings, but for this case they are the same.

Look at this string:

 local insertQuery = [[INSERT INTO estudiantes VALUES (NULL, ']] .. TextField.text .. [[','This is an unknown person.', '1234', '09231990');]]  

Where this differs from your version is first, we don’t need to make a string with the quotes on it. This lets you use the text straight out of the text field. We are splitting the query into 3 part. The first half which you see end’s in a single quote then we use the … operator to append the textfield string and then another … operator to append the rest of the query string. Note again, I close the single quotes around the text field just inside the [[ part of this.

Hope that helps you understand what’s going on.

Rob
[import]uid: 199310 topic_id: 33347 reply_id: 132495[/import]