How to create multiple SQlite tables using JavaScript?

Question

So far in my app development (phonegap/JQuery Mobile) I have setup a database and have a table created to which I have implemented a few functions that interact with the table for different events e.g. user registration.

However, I need to create another table within the same database however I'm not sure how to go about this.

I attempted to created another function (createEvent(tx) {) that creates a table however that isn't working.

Please see my javascript below which builds the database and creates the table with.

  document.addEventListener("deviceready", onDeviceReady, false);

                     var db;
                     function onDeviceReady() {
                         db = window.openDatabase("SoccerEarth", "2.0", "SoccerEarthDB", 2*1024*1024);
                         db.transaction(createDB, createEvents, errorCB, successCB);

                     }
                     function createDB(tx) {
                         tx.executeSql('CREATE TABLE IF NOT EXISTS SoccerEarth (UserName text, FirstName text, LastName text, Email text, Password text, CPass text)');

                     }
                     function createEvents(tx) {
                     tx.execute2('CREATE TABLE IF NOT EXISTS SoccerEvents (Title text, Location text, NoPeople text, Date text, Description text)');
                                              }

                     function errorCB(err) {
                         alert("Error processing SQL: "+err.code);
                     }

                     function successCB() {
                  alert("Database Ready!");
                     }

Steve Kennedy · Accepted Answer · 2016-03-16 15:31:29Z

2

I would do something like this, just for simplicity:

db.transaction(function (tx) {            
tx.executeSql('CREATE TABLE IF NOT EXISTS SoccerEarth (UserName text, FirstName text, LastName text, Email text, Password text, CPass text)');
tx.executeSql('CREATE TABLE IF NOT EXISTS SoccerEvents (Title text, Location text, NoPeople text, Date text, Description text)');
 }, errorCB, successCB);

answered Mar 16, 2016 at 15:31

Steve Kennedy

5,4225 gold badges30 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mahdi Over a year ago

Thanks, I see my mistake. I accidentaly typed execute2 instead of executeSql for the 2nd table.

Kendall Over a year ago

@Mahdi Please accept this as the answer if it answered your question. I know it did mine :).

Mahdi Over a year ago

@KendallRoth Done :)

IamKarim1992 · Accepted Answer · 2016-03-31 14:03:02Z

You can pass the SQL statement as an array of string something. and execute each statement in the array.

var sql = [ "CREATE TABLE IF NOT EXISTS LEAD (ID VARCHAR(35) PRIMARY KEY NOT NULL, PROCESS VARCHAR (32), VERSION VARCHAR (8), CREATED_BY VARCHAR (128), CREATED_ON VARCHAR (32), RECIPIENT VARCHAR (32), STATUS VARCHAR (8))",
         "CREATE TABLE IF NOT EXISTS DOCUMENT (ID INTEGER PRIMARY KEY AUTOINCREMENT, LEAD_ID VARCHAR(35) REFERENCES LEAD (ID) NOT NULL, NAME VARCHAR (255) NOT NULL, TYPE VARCHAR (32) NOT NULL, CONTENT BLOB NOT NULL, RETRY INTEGER NOT NULL, STATUS VARCHAR (8))",
         "CREATE TABLE IF NOT EXISTS FUNDATA(ID INTEGER PRIMARY KEY AUTOINCREMENT, FUN_ID INTEGER REFERENCES LEAD (ID), NAME VARCHAR(128) NOT NULL, VALUE VARCHAR (255) NOT NULL)"  ];

now pass all the sql to function which would execute , also pass your database object . Callback can be any other function.

function write(database, sql){
    this.sql = sql;
    database.transaction(
      function(tx){
      for(var i=0; i<this.sql.length; i++){
      console.log("execute sql : " + this.sql[i]);
      tx.executeSql(this.sql[i]);
    }    
      },function(error){
    console.log("error call back : " + JSON.stringify(error));
    console.log(error);
  },
     function(){
    console.log("transaction complete call back ");
  }
    );  
  }

Hope it provides a better way of doing things and making the code more robust .You could also execute Insert statement like this.

Collectives™ on Stack Overflow

How to create multiple SQlite tables using JavaScript?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related