Jump to content

[TOPIC: topicViewTemplate]
[GLOBAL: userSmallPhoto]
Photo

Connecting to Sqlite database via Html5
Started by akhtarx5 Nov 20 2018 12:32 AM

- - - - -
14 replies to this topic
[TOPIC CONTROLS]
[/TOPIC CONTROLS]
[modOptionsDropdown]
[/modOptionsDropdown]
[reputationFilter]
[TOPIC: post.html]
#1

akhtarx5

[GLOBAL: userInfoPane.html]
akhtarx5
  • Enthusiast

  • 47 posts
  • Corona SDK

Hi People

 

I know currently the Html5 build does not support connecting to Sqlite database

 

But is there any way or alternative method that i can some how connect to Sqlite database with

the Html5 Build

 

thankyou



[TOPIC: post.html]
#2

vitaly1

[GLOBAL: userInfoPane.html]
vitaly1
  • Contributor

  • 212 posts
  • Corona Staff

Yes, you can rich sqlite database from Corona Lua app through JS plugin. And the JS plugin can use, for example, this JS sqlite port 

https://github.com/kripken/sql.js



[TOPIC: post.html]
#3

akhtarx5

[GLOBAL: userInfoPane.html]
akhtarx5
  • Enthusiast

  • 47 posts
  • Corona SDK

hi

 

would there be any chance you could put a small , just a small small html5 build

with the js plugin

 

so i could see how to put it altogether

 

this would be so much appreciated

 

please... please...



[TOPIC: post.html]
#4

vitaly1

[GLOBAL: userInfoPane.html]
vitaly1
  • Contributor

  • 212 posts
  • Corona Staff

Attached the simplest html5 Lua / Sqlite3 app.

Attached Files



[TOPIC: post.html]
#5

akhtarx5

[GLOBAL: userInfoPane.html]
akhtarx5
  • Enthusiast

  • 47 posts
  • Corona SDK

Hi

 

This is Great,   thankyou so much for your help

 

but is there any chance we can connect to a mysql database on the web

outside of build folder

 

thanks so much



[TOPIC: post.html]
#6

bif2

[GLOBAL: userInfoPane.html]
bif2
  • Observer

  • 1 posts
  • Corona SDK

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)

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



[TOPIC: post.html]
#7

akhtarx5

[GLOBAL: userInfoPane.html]
akhtarx5
  • Enthusiast

  • 47 posts
  • Corona SDK

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
 



[TOPIC: post.html]
#8

vitaly1

[GLOBAL: userInfoPane.html]
vitaly1
  • Contributor

  • 212 posts
  • Corona Staff


[{
    "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


[TOPIC: post.html]
#9

akhtarx5

[GLOBAL: userInfoPane.html]
akhtarx5
  • Enthusiast

  • 47 posts
  • Corona SDK

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


[TOPIC: post.html]
#10

akhtarx5

[GLOBAL: userInfoPane.html]
akhtarx5
  • Enthusiast

  • 47 posts
  • Corona SDK

hi

 

Just wondering as we are working with database sqlite

 

how would i  possibly insert record into database with this method

 

thankyou



[TOPIC: post.html]
#11

vitaly1

[GLOBAL: userInfoPane.html]
vitaly1
  • Contributor

  • 212 posts
  • Corona Staff

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


[TOPIC: post.html]
#12

akhtarx5

[GLOBAL: userInfoPane.html]
akhtarx5
  • Enthusiast

  • 47 posts
  • Corona SDK

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



[TOPIC: post.html]
#13

vitaly1

[GLOBAL: userInfoPane.html]
vitaly1
  • Contributor

  • 212 posts
  • Corona Staff

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



[TOPIC: post.html]
#14

akhtarx5

[GLOBAL: userInfoPane.html]
akhtarx5
  • Enthusiast

  • 47 posts
  • Corona SDK

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


[TOPIC: post.html]
#15

vitaly1

[GLOBAL: userInfoPane.html]
vitaly1
  • Contributor

  • 212 posts
  • Corona Staff

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)

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

"

 




[topic_controls]
[/topic_controls]