Connecting to Sqlite database via Html5

Beta Testing HTML5
#21

hi

if my sqlite database contains the following line

id     content   content2

5       hello      world

how can i obtain just the word,  world under content2 into variable

im using the following code but it prints out the full line

full source is in zip as above

local tbl = sqlite.exec(db, “select * from test where id=5”)

    if tbl then
    local str = json.prettify(tbl)
    print(str)
    data.text = str
    end
    
   
end

local function pluginListener( event )
    data.text = data.text …’\n’…json.prettify(event)
end
 

#22

[{

    “columns”:[“id”,“content”,“content2”],

    “values”:[[1,“Hello”,“World”],[2,“World”,“Hello”],[3,“Hello”,“Lua”],[4,“Hello”,“World”],[5,“World”,“Hello”],[6,“Hello”,“Lua”],[7,“Hello”,“World”],[8,“World”,“Hello”],[9,“Hello”,“Lua”],[10,“Hello”,“World”],[11,“World”,“Hello”],[12,“Hello”,“Lua”],[13,“Hello”,“World”],[14,“World”,“Hello”],[15,“Hello”,“Lua”]]

  }]

as we see result (tbl)  is array, and it has only 1 item, and this item is object

object has 2 items: ‘columns’ and ‘values’ 

‘columns’ is array of strings, and it has 3 items, 3 column names

‘values’ is array of arrays, 15 rows, each row is array of column values

here’s howto rich any item of the result

    local tbl = sqlite.exec(db, “select * from test”)

    if tbl then

        local str = json.prettify(tbl)

        print('rows: ', str)

        data.text = str

        for i = 1, #tbl[1].columns do 

            print(tbl[1].columns[i])

        end

        for i = 1, #tbl[1].values do 

            local row = tbl[1].values[i]

            for j = 1, #row do 

                print(row[j])

            end

        end

    end

#23

hi

if you would be so kind.

Just wondering could you possibly test the code as the full table shows with all data

instead of just the table data “world”

output box shows:

[{
    “columns”:[“id”,“content”,“content2”],
    “values”:[[5," World",“five”]]
  }]

i need to catch just the data “world”

im using

local tbl = sqlite.exec(db, “select * from test where id=5”)
     if tbl then
        local str = json.prettify(tbl)
        print('rows: ', str)
        data.text = str
 
        for i = 1, #tbl[1].columns do
            print(tbl[1].columns[i])
        end
        for i = 1, #tbl[1].values do
            local row = tbl[1].values[i]
            for j = 1, #row do
                print(row[j])
            end
        end
    end

thankyou so much

#24

hi

Just wondering as we are working with database sqlite

how would i  possibly insert record into database with this method

thankyou

#25

this will insert 1 record:

local db = “data.db”

sqlite.open(db)

sqlite.exec(db, "insert into test(id,content,content2) values(123,‘aaaaaaa’,‘bbbbbbbbb’))

sqlite.close(db)  – save changes and close db

here’s a SQL tutorial

http://www.sqlitetutorial.net/sqlite-insert/

#26

your help is most appreciated

there seems to be some sort of syntax error with line

sqlite.exec(db, "insert into test(id,content,content2) values(123,‘aaaaaaa’,‘bbbbbbbbb’))

i have also tried these

sqlite.exec(db, insert into test(“id,content,content2”) values(‘123’,‘bbb’,‘ccc’)   )

sqlite.exec(db, insert into test(‘id,content,content2’) values(‘123’,‘aaaaaaa’,‘bbbbbbbbb’)
sqlite.exec(db, INSERT INTO test(id, content, content2) VALUES(123, ‘aaa’, ‘bbb’)
sqlite.exec(db, “insert into test(id,content,content2)” (values(123,‘aaaaaaa’,‘bbbbbbbbb’))   )    
  

#27

oops, missing ending double quote sign

sqlite.exec(db, "insert into test(id,content,content2) values(123,‘aaaaaaa’,‘bbbbbbbbb’))

should bet be replaced on

sqlite.exec(db, “insert into test(id,content,content2) values(123,‘aaaaaaa’,‘bbbbbbbbb’)”)

#28

Hi

Managed to get it working But,

Just wondering does this code actually save records into the website database or is it just temporary record/variable

the record doesnt seem to stay in the database,

local db = “data.db”

sqlite.open(db)

sqlite.exec(db, “insert into test(id,content,content2) values(123,‘aaaaaaa’,‘bbbbbbbbb’)”)

sqlite.close(db)  – save changes and close db

#29

a bit earlier in this thread @bif2 said:

"Yes,

  1. on start you should download remote database to arraybuffer

and then use this downloaded  arraybuffer as source to open SQL.Database

var db = new SQL.Database(arraybuffer)

  1. on exit you should save updated database in server;

export the database to an Uint8Array containing the SQLite database file

var data = db.export();

and then upload ‘data’ to server

 

https://developer.mozilla.org/en-US/docs/Web/API/XMLHttpRequest/Sending_and_Receiving_Binary_Data

"