locked
Access SQLite from HTML5/JS Metro Apps

    Question

  • Hi,

    Is there any example available to show how to access sqlite database via javascript for metroapps? I have tried using https://github.com/doo/SQLite3-WinRT but haven't been successful yet. 

    Any direction would be of great help.

    Thanks,

    Sharath

    Monday, August 20, 2012 11:15 AM

Answers

All replies

  • Hi Sharath,

    There is no sample that I am aware of.  You can create a winMD component in another language to encapsulate your Data access however and expose it to JavaScript:

    http://msdn.microsoft.com/en-us/library/windows/apps/br230301(v=vs.110).aspx

    -Jeff


    Jeff Sanders (MSFT)

    Monday, August 20, 2012 2:19 PM
    Moderator
  • Hi,

    I successfully used the SQLite3-WinRT library. Here is how i did it:

    https://github.com/doo/SQLite3-WinRT/issues/42

    Fabien

    Wednesday, September 19, 2012 6:39 PM
  • Hi,

    I did exact what has been described and so far I'm able to build and start the app. Unfortunatlly If I try to retrive a row from a database it allways returns nothing. Do you have an idea why the db and later the row object is undefined?

           SQLite3JS.openAsync(dbPath)
                .then(function (db) {
                    console.log('DB opened');
                    return db.eachAsync('SELECT * FROM perscomp;', function (row) {
                        console.log('Get a ' + row.namecomp);
                    });
                })
               .then(function (db) {
                   console.log('close the db');
                   db.close();
               });


    Thursday, September 20, 2012 10:35 PM
  • Well your db shouldn't be null in the first place. Add a breakpoint and watch the value of "dbPath" and make sure your DB is there.

    It had something like:

    var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\TILES_SQLITE.db';

    and I had to copy my DB at the right place

    It happened to me, the DB file was in my project and wasn't copied where i thought it was. Once the db variable is ok, your query should run just fine

    Fabien

    Friday, September 21, 2012 4:04 PM
  • The hint with the db path was correct. I change the path form documentsLibrary to the localFolder and it works. The DB object is not undefined anymore and I'm able to work with the database.

    Does someone know if the use of the documentsLibrary is supported? If supported is someone having an example?

    Monday, September 24, 2012 8:03 PM
  • what you mean by "use of the documentsLibrary"? You can put the DB in any place your app has access to. If you want to store it in the documentsLibrary folder you have to declare the capability in your manifest. Then SQLite WinRT component will also be able to access that location. But you have to also declare supported filetypes when you request access to the documentsLibrary. Not a good idea. Place the db in your apps local folder. Its not for the user to mock around with anyway.
    Thursday, September 27, 2012 10:18 PM
  • That's exactly what I thought too. The only drawback of having the app using the app local folder is that if the app gets deleted the data are gone as well. I thought it might be better to store the data in the documentLibrary instead.

    I have been spending hours of trying to open a database in the documentLibrary but did not succeed. I was able to do everything in the documentLibrary like copy files, create files, etc. but not open a SQLite DB. That was the reason why I thought there must be a general problem opening a SQLite DB in the documentLibrary folder. Has someone done this successfully?

    By now I changed the location back to the localfolder and it works like a charm.

    Friday, September 28, 2012 7:47 AM
  • Bruno, I might not have said it clearly enough. You *can* open a db from documentsLibrary. But there is one more step involved to be able to do that. Lets say your database file is called "data.sqlite". Then you would have to create a file association with .sqlite in the apps manifest to be able to "see" and access sqlite files. However there are 2 problems: 

    1. SQLite needs 2 additional files with different file extensions for its write-ahead cache. So you would have to declare those 2 extensions also in your apps manifest

    2. All file associations you declare in your app manifest makes your app handle types of those files. So whenever a user double clicks a ".sqlite" file from then on, your app will appear in the list of apps that can "open" this file. If the user chooses your app, Windows activates your app with the file activation contract. And you are supposed to "open" that file and display some results to the user. Which is of course useless and not what you would want.

    So the only place to put a sqlite database file is the apps local storage. And yes, its unfortunate that all data will be lost when the user uninstalls the app. It says so in the uninstall flyout but users might not be sure about the implications always. Thats why you could also maybe off an online backup of the data, associated with the users current Microsoft account on your own server. Don't put the db in the roaming folder. Roaming space on MSFT servers is limited.

    Friday, September 28, 2012 8:27 AM
  • Hi Bruno,

    Can you simply prompt the user for the location by using the file picker?  That way the use can choose where their DB is located.

    -Jeff


    Jeff Sanders (MSFT)

    Friday, September 28, 2012 11:54 AM
    Moderator
  • You mean the folder picker? I have never tried, but if Win32 File I/O functions can access that location then too without having the access token, then it should work.
    Friday, September 28, 2012 12:09 PM
  • Hi Bruno proITservice,

    Can you please give me some code to  show insert data into sqlite database table.

    and how we can use select query in sqlite.

    waiting for your reply!!

    Thnx in advance!!

    Thursday, January 31, 2013 12:21 PM
  • I have placed all code for updating, inserting and deleting data in a file called data.js. Just have a look at it. For each object you define an own class.

     /// <reference path="SQLite3.js" />
    
    (function () {
    
        var res = WinJS.Resources;
        var nav = WinJS.Navigation;
     
         // class service
        var Service = WinJS.Class.define(function () {
            that = this;
            that.db = null
            that.path = _getFullPath();
        },
        {
            update: function (id, code, name, unit, rnd, taxcode, value, account, imagename, updateDoneFunction) {
    
                // open the database and update the service data
                SQLite3JS.openAsync(that.path)
                    .then(function (myDB) {
                        that.db = myDB;
                        console.log('DB opened for update service');
                        return that.db.runAsync("UPDATE service SET code = :code,name = :name,unit = :unit,rnd = :rnd,taxcode = :taxcode,value = :value,account = :account,imagename = :imagename WHERE serviceid = :serviceid",
                            { code: code, name: name, unit: unit, rnd: rnd, taxcode: taxcode, value: value, account: account, imagename: imagename, serviceid: id });
                    })
                    .done(function () {
                        console.log('Updated service nr.: ' + id + " name: " + name);
                        that.db.close();
                        // when finish execute function (if it null don't call)
                        if (updateDoneFunction) {
                            updateDoneFunction();
                        }
                    },
                    function (error) {
                        if (that.db) {
                            that.db.close();
                        }
                        console.log('Error updating service  -   ' + error.message);
                    })
    
            },
            insert: function (id, code, name, unit, rnd, taxcode, value, account, imagename, insertDoneFunction) {
    
                // open the database and insert the new contact
                SQLite3JS.openAsync(that.path)
                    .then(function (myDB) {
                        that.db = myDB;
                        console.log('DB opened for insert service');
                        return that.db.runAsync("INSERT INTO service (code, name, unit, rnd, taxcode, value, account, imagename) VALUES (:code, :name, :unit, :rnd, :taxcode, :value, :account, :imagename)",
                            { code: code, name: name, unit: unit, rnd: rnd, taxcode: taxcode, value: value, account: account, imagename: imagename });
                    })
                    .done(function () {
                        console.log('Insert new service, code: ' + code) + ' decription: ' + name;
                        that.db.close();
                        // when finish execute function (if it null don't call)
                        if (insertDoneFunction) {
                            insertDoneFunction();
                        }
                    },
                    function (error) {
                        if (that.db) {
                            that.db.close();
                        }
                        console.log('Error inserting service  -  ' + error.message);
                    })
            },
            remove: function (id, deleteDoneFunction) {
                // open the database and delete the contact data
                SQLite3JS.openAsync(that.path)
                    .then(function (myDB) {
                        that.db = myDB;
                        console.log('DB opened for delete service');
                        return that.db.runAsync("DELETE FROM service WHERE serviceid = :serviceid",
                            { serviceid: id });
                    })
                    .done(function () {
                        console.log('removed service nr.: ' + id);
                        that.db.close();
                        // when finish execute function (if it null don't call)
                        if (deleteDoneFunction) {
                            deleteDoneFunction();
                        }
                    },
                    function (error) {
                        if (that.db) {
                            that.db.close();
                        }
                        console.log('Error removing service  -   ' + error.message);
                    })
            }
        },
        { getPath: this.path });
    
    
    
        // local functions
    
        function _getFullPath() {
            return Windows.Storage.ApplicationData.current.localFolder.path + "\\" + res.getString("dbFileName").value;
        };
    
        function _getImage(isCompany) {
            return (isCompany) ? res.getString("ContactImageFirma").value : res.getString("ContactImagePerson").value;
        }
    
        // make function public
        WinJS.Namespace.define("Data",
        {
            Contact: Contact,
            Service: Service
        });
    
    })();

    Sunday, February 3, 2013 8:19 PM
  • Hi Fabien,

                 I am struggling a lot to for sqlite connection..please help  me in this issue..................

    Wednesday, February 27, 2013 10:37 AM
  • Hi Satish Sagar,

    At starting i was also having some issue with sqlite in windows metro app using javascript. But Now most of things are clear.

    Let me Help u to solve ur problems. Step By Steps

    1. Create a new project.

    2. Go to references , then right click on the references there you will get Manage NuPackage Click on that.

    3. In online search for Sqlite-net package , intall that package.

    4. Again right click on the References and click add refrences , there in Extension section you will get two unchecked reference MS visual C++ run time and Sqlite Windows runtime. check both the reference and  add references. It will two CS files in your solution explorer.

    5. Now Download Zip file from https://github.com/doo/SQLite3-WinRT  It will provide you the wrapper thing to use cs files in your project.

    6. Unzip at any location.

    7. Now in project go to FILE->ADD->Existing Project-> and  browse your unzip location . There you will get a file SQLite3Component.vcxproj inside the SQLite3Component. Add that file.

    8. Now wrapper Project is included in you  Project.

    9. Now in your unzipped files you ll get SQLite3JS folder copy that folder and paste in your solution explorer.

    10. Now you are ready to use sqlite in your project.

    11. Try to build your project it will show two error that winres.h file is not there. For that go to error location rename it winresrc.h and build it again. Now it will build and you can use sqlite in your project.

     12. To use .js file of SQLite3JS , Provide the src  of js file like SQLite3JS\js\SQLite3.js  in html page where you are going to use sqlite.

    Here i am giving you some example to use sqlite in js

    // This will Create database name db.sqlite and try to create a table Name Notes. If it is not there 

    var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\db.sqlite';

        SQLite3JS.openAsync(dbPath)

          .then(function (db) {

              return db.runAsync('CREATE TABLE Notes (id TEXT PRIMARY KEY, notes TEXT)');

    I hope all these step will help you out.

    Best of luck and Happy Coding !!

    • Proposed as answer by satish sagar Thursday, February 28, 2013 5:25 AM
    Wednesday, February 27, 2013 11:52 AM
  • hi AMit,

             Thanks for valuable reply.......upto step 10 everything is fine but i didnt get any errors which you have mentioned in step 11......please help me in this issue..............

    Thursday, February 28, 2013 5:27 AM
  • hi amit,waiting for your reply.................
    Thursday, February 28, 2013 6:22 AM
  • Hi Satish,

    If there is no error that u can build ur project and i think u can use that code which i describe.

    Please let me know what happen when u try to build that project?

    Thursday, February 28, 2013 2:34 PM

  • @Amit Chaurasiya

    I fallowed the steps you described,But I am getting a error when I try build the project\. The error is 

    Error 1 The project contains 2 items that represent the app manifest: package.appxmanifest, SQLite3JS\package.appxmanifest. The project can contain only one app manifest. C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\AppxPackage\Microsoft.AppXPackage.Targets

    Please help me out..

    devendra

    Monday, October 7, 2013 1:01 PM
  • Hi Devendra,

    One project can contain only one manifest file .

    So Go to SQLite3js folder in solution explorer and Rite Click and delete Manifest file of SQLite3JS Project. 

    Than your app ll be compile

    • Proposed as answer by i-exceed Tuesday, October 8, 2013 8:00 AM
    Monday, October 7, 2013 1:13 PM
  • Hi 

    thanks for your help

    i do all the step but SQLite3 is not defind. I put it in my project folder at the same level as css, js, ...

    i put this in my home page 

    <script src="/SQLite3JS/js/SQLite3.js"></script>

    what is my mistake?


    PS: the matter seems to be localise in the SQLite3.js file.
    • Edited by Flink1988 Monday, June 2, 2014 12:25 PM
    Monday, June 2, 2014 11:16 AM
  • what is the problem? Is the file loaded? The path is hardly at the same level with css and js, as it is in /SQLite3JS according to your snippet.
    Tuesday, June 3, 2014 9:23 AM
  • here my home.js

    (function () {
        "use strict";
    
        WinJS.UI.Pages.define("/pages/home/home.html", {
            // Cette fonction est appelée chaque fois qu'un utilisateur accède à cette page. Elle
            // remplit les éléments de la page avec les données d'application.
            ready: function (element, options) {
                // TODO: initialisez la page ici.
            }
        });
    })();
    
    function test() {
        var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\db.sqlite';
        SQLite3JS.openAsync(dbPath)
        .then(function (db) {
            return db.runAsync('CREATE TABLE Item (name TEXT, price REAL, id INT PRIMARY KEY)')
            .then(function () {
                return db.runAsync('INSERT INTO Item (name, price, id) VALUES (?, ?, ?)', ['Mango', 4.6, 123]);
            })
            .then(function () {
                return db.eachAsync('SELECT * FROM Item', function (row) {
                    console.log('Get a ' + row.name + ' for $' + row.price);
                    var sortie = 'Get a ' + row.name + ' for $' + row.price;
                    document.getElementById('test34').innerHTML = sortie;
                });
            })
            .then(function () {
                db.close();
            });
        });
    }


    here my home.html

    <!DOCTYPE html>
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
        <meta charset="utf-8" />
        <title>homePage</title>
    
        <!-- Références WinJS -->
        <link href="//Microsoft.WinJS.2.0/css/ui-dark.css" rel="stylesheet" />
        <script src="//Microsoft.WinJS.2.0/js/base.js"></script>
        <script src="//Microsoft.WinJS.2.0/js/ui.js"></script>
    
        <link href="/css/default.css" rel="stylesheet" />
        <link href="/pages/home/home.css" rel="stylesheet" />
        <script src="/pages/home/home.js"></script>
        <script src="/SQLite3JS/js/SQLite3.js"></script>
        </head>
    <body>
        <!-- Contenu à charger et afficher. -->
        <div class="fragment homepage">
            <header aria-label="Header content" role="banner">
                <button data-win-control="WinJS.UI.BackButton"></button>
                <h1 class="titlearea win-type-ellipsis">
                    <span class="pagetitle">Bienvenue dans testdb !</span>
                </h1>
            </header>
            <section aria-label="Main content" role="main">
                <p>Le contenu s'affiche ici.</p>
            	<button type="submit" onclick="test()">button</button>
                <textarea id="test34"></textarea>
            </section>
        </div>
    </body>
    </html>


    at the debug it seems to good load sqlite3.js but nothing apear

    db.sqlite is create at C:\Users\username\AppData\Local\Packages\35be6591-2444-4b17-aabc-2e441166ab63_zg5b7q65fgeqj\LocalState

    and all item are saved into the table
    • Edited by Flink1988 Tuesday, June 3, 2014 10:52 AM
    Tuesday, June 3, 2014 10:24 AM
  • after many change everything work but i don't know how to use my own data base.

    can you help me please?

    Tuesday, June 3, 2014 5:32 PM
  • Hi Flink,

    you have already created database... 

    Using following lines of code u can access that 

    var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + dbName;  // THis is data baseName
            SQLite3JS.openAsync(dbPath).then(function (db) {
                db.allAsync(executeQuery)
                 .then(function (result) {
                     db.close();

    });

      });

    Wednesday, June 4, 2014 5:41 AM
  • hi

    thanks for your answer but i don't know how to use my own db.

    should i try to move it in Windows.Storage.ApplicationData.current.localFolder.path ?

    if yes how can i do that?

    Wednesday, June 4, 2014 7:35 AM
  • Hi Flink,

    Yes you can move ur db from solution explorer to localfolder

    So on initializing of app u can call this code of line.

    It will copy your db to localfolder than you can use your db in your code.

    function CopyDatabase() {
        var installFolder = Windows.ApplicationModel.Package.current.installedLocation;
        var localFolder = Windows.Storage.ApplicationData.current.localFolder;
        installFolder.getFolderAsync("sqlite").then(function (folder) {  // HERE Sqlite is the folder in Solution Explorer which containing all the db files 
            CopyDatabaseFiles(folder, localFolder);
        },
        function (folder) {
            console.log("folder not found: " + folder.name);
            return;
        })
        function CopyDatabaseFiles(folder, localFolder) {
            if (folder == null)
                return;
            folder.getFilesAsync().then(function (files) {
                if (files != null) {
                    files.forEach(function (result) {
                        console.log("copy file: " + result.displayName);
                        result.copyAsync(localFolder);
                    });
                }
            });
        }
    }

    Call this function on launch of app first time.

    Wednesday, June 4, 2014 8:41 AM
  • thanks for your reply.

    where should i put this call?

    in 

    / / TODO: This application has been launched. initialize 
                     / / Your application here.

    ???

    thanks very much


    • Edited by Flink1988 Wednesday, June 4, 2014 9:11 AM
    Wednesday, June 4, 2014 9:10 AM
  • HI Flink,

    on first launch of you application you can call this function.

    PS: I am not able to understand your Code .. Please write in English

    Wednesday, June 4, 2014 9:20 AM
  • yes sorry it's in english now.

    where can i call this function? in default.js ?

    where should i save my db in solution explorer?

    Wednesday, June 4, 2014 9:34 AM
  • hi 

    i have succeed to launch this function on launch but but i don't know where to save my db.

    thanks

    Thursday, June 5, 2014 11:11 AM
  • Hi Flink,

    Just use that CopyDatabase() function , It will store your files to to install folder of your app.

    That location will be like 

    C:\Users\Shyam3105\AppData\Local\Packages\436e350a-552e-49c6-8a74-ae1c2600dced_q591fg9b2v1gp\LocalState

    It is my system's path. Similar it will copy to your system also.

    Thursday, June 5, 2014 11:44 AM
  • ok i understand but where should i have my original db? when i exec this function i have an error because my original db is not found.
    Thursday, June 5, 2014 5:37 PM
  • Hi Flink,

    Create a folder in your solution explorer named "sqlite" and copy your data base file in that folder,

    Than Build your app and run it. On Execution of Copydatabase function , Your Database will be copied to the localStorage folder so after that you can use that.

    Thursday, June 5, 2014 8:39 PM
  • it doesn't work

    folder not found: WinRTError

    • Edited by Flink1988 Thursday, June 5, 2014 9:14 PM
    Thursday, June 5, 2014 9:13 PM
  • When i add a floder directly in my solution explorer the folder is add in

    C:\Users\UserName\Documents\Visual Studio 2013\Projects\testdb\testdb

    but when i exec the function vs try to find this folder in

    C:\Users\UserName\Documents\Visual Studio 2013\Projects\testdb\testdb\bin\x86\Debug\AppX

    Friday, June 6, 2014 7:43 AM
  • In Solution Explorer, CHeck the property of DBFiles In Properties window,

    Check where it is having containe type Compile or inclue,

    In your case, I think it is none.


    Friday, June 6, 2014 8:43 AM
  • OK thank you verry much.

    It semmes to work but i come back if i have an other problem.

    Thank


    • Edited by Flink1988 Friday, June 6, 2014 10:03 AM
    Friday, June 6, 2014 9:39 AM
  • Hi Flink,

    If It is working, Mark it as answered.

    Friday, June 6, 2014 10:09 AM
  • no finaly it don't work. When i tried the same on my own projeci can't find the sqlite reference.
    Friday, June 6, 2014 4:06 PM
  • hi,

    i have  progress in my app.

    how can i get the id of a new line create with isert into?

    function creer() {
        //on récupère les valeurs des différents champs
        var nom = document.getElementById("nom").value;
        var cal = document.getElementById("Calories").value;
        var ig = document.getElementById("IG").value;
        var prot = document.getElementById("Proteines").value;
        var glu = document.getElementById("Glucides").value;
        var lip = document.getElementById("Lipides").value;
        var fib = document.getElementById("Fibres").value;
        var conv = document.getElementById("convers").value;
    
            var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\aliments.sqlite';
            SQLite3JS.openAsync(dbPath)
            .then(function (db) {
                var test = id;
                return db.runAsync('INSERT INTO conversion (gr, cl, ID_aliments) VALUES (?, ?, ?)', ['100', '100', id])
    
                .then(function () {
                    db.close();
                });
            })
    }

    thanks

    Tuesday, June 17, 2014 12:19 PM
  • Care to read the docs of the lib?

    lastInsertRowId is what you are looking for.

    Tuesday, June 17, 2014 12:28 PM
  • sorry i didn't find the docs. if u have a link i would be happy to give it to me.

    thanks

    like that?

    function creer() {
        //on récupère les valeurs des différents champs
        var nom = document.getElementById("nom").value;
        var cal = document.getElementById("Calories").value;
        var ig = document.getElementById("IG").value;
        var prot = document.getElementById("Proteines").value;
        var glu = document.getElementById("Glucides").value;
        var lip = document.getElementById("Lipides").value;
        var fib = document.getElementById("Fibres").value;
        var conv = document.getElementById("convers").value;
    
            var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\aliments.sqlite';
            SQLite3JS.openAsync(dbPath)
            .then(function (db) {
                var test = id;
                return db.runAsync('INSERT INTO conversion (gr, cl, ID_aliments) VALUES (?, ?, ?)', ['100', '100', id])
    
    		var newid = lastInsertRowId;
    
                .then(function () {
                    db.close();
                });
            })
    }
    


    • Edited by flink88 Tuesday, June 17, 2014 12:37 PM
    Tuesday, June 17, 2014 12:35 PM
  • not like that, you are missing the object you want to call that property on.

    var newId = db.lastInsertRowId

    and the docs are the specs and readme at https://github.com/doo/SQLite3-WinRT

    Tuesday, June 17, 2014 12:41 PM
  • ok thanks a lot
    Tuesday, June 17, 2014 2:42 PM
  • hi

    i have a problem with this code:

    (function () {
        "use strict";
    
        WinJS.UI.Pages.define("/pages/gestion/gestion.html", {
            // Cette fonction est appelée chaque fois qu'un utilisateur accède à cette page. Elle
            // remplit les éléments de la page avec les données d'application.
            ready: function (element, options) {
                // TODO: initialisez la page ici.
                getalim();
            }
        });
    })();
    
    function getalim(parid) {
        document.getElementById("alim").innerHTML = null;
        var firstoption = new Option("new", "", false, false)
        document.getElementById("alim").options.add(firstoption);
        var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\aliments.sqlite';
        SQLite3JS.openAsync(dbPath)
        .then(function (db) {
            return db.eachAsync('SELECT * FROM aliments', function (row) {
                var nom = row.Nom
                var id = row.ID
                if (parid == 'undefined') {
                    var eloption = new Option(nom, id, false, false)
                    document.getElementById("alim").options.add(eloption);
                }
                else{
                    if (parid == id) {
                        var eloption = new Option(nom, id, true, false)
                        document.getElementById("alim").options.add(eloption);
                    }
                    else{
                        var eloption = new Option(nom, id, false, false)
                        document.getElementById("alim").options.add(eloption);
                    }
                }
            })
            .then(function () {
                db.close();
            });
        })
    }
    
    function change() {
        //d'abord on réinitialise tout
        document.getElementById("nom").innerHTML = '';
        document.getElementById("Calories").innerHTML = '';
        document.getElementById("IG").innerHTML = '';
        document.getElementById("Proteines").innerHTML = '';
        document.getElementById("Glucides").innerHTML = '';
        document.getElementById("Lipides").innerHTML = '';
        document.getElementById("Fibres").innerHTML = '';
        document.getElementById("convers").innerHTML = '';
        //document.getElementById("id").innerHTML = '';
        //on récupère l'id de l'aliment sélectionné
        var id = document.getElementById("alim").value;
        //on change le bouton en fonction de l'aliment sélectionné
        if (id != '') {
            document.getElementById("creer").style.display = 'none';
            document.getElementById("modif").style.display = 'block';
        }
        else {
            document.getElementById("creer").style.display = 'block';
            document.getElementById("modif").style.display = 'none';
        }
    
        var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\aliments.sqlite';
        SQLite3JS.openAsync(dbPath)
        .then(function (db) {
            return db.eachAsync('SELECT * FROM aliments WHERE ID = ?', [id], function (row) {
                //on récupère tout les infos...
                var nom = row.Nom
                //var id = row.ID
                var cal = row.Calories
                var ig = row.IG
                var prot = row.Proteines
                var glu = row.Glucides
                var lip = row.Lipides
                var fib = row.Fibres
                //...et on les injecte dans les différents champs
                document.getElementById("nom").innerHTML = nom;
                document.getElementById("Calories").innerHTML = cal;
                document.getElementById("IG").innerHTML = ig;
                document.getElementById("Proteines").innerHTML = prot;
                document.getElementById("Glucides").innerHTML = glu;
                document.getElementById("Lipides").innerHTML = lip;
                document.getElementById("Fibres").innerHTML = fib;
                document.getElementById("id").innerHTML = id;
    
            })
            .then(function () {
                return db.eachAsync('SELECT * FROM conversion WHERE ID_aliments = ?', [id], function (row) {
                    //on fait pareil pour la convesion
                    var cl = row.cl;
                    document.getElementById("convers").innerHTML = cl;
                });
            })
            .then(function () {
                db.close();
            });
        })   
    }
    
    function creerconvers(newid) {
        var conv = document.getElementById("convers").value;
    
        var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\aliments.sqlite';
        SQLite3JS.openAsync(dbPath)
        .then(function (db) {
            return db.runAsync('INSERT INTO conversion (gr, cl, ID_aliments) VALUES (?, ?, ?)', [100, conv, newid])
            .then(function () {
                db.close();
            });
        });
    }
    
    function creer() {
        var newid;
    
        //on récupère les valeurs des différents champs
        var nom = document.getElementById("nom").value;
        var cal = document.getElementById("Calories").value;
        var ig = document.getElementById("IG").value;
        var prot = document.getElementById("Proteines").value;
        var glu = document.getElementById("Glucides").value;
        var lip = document.getElementById("Lipides").value;
        var fib = document.getElementById("Fibres").value;
        var conv = document.getElementById("convers").value;
    
        var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\aliments.sqlite';
        SQLite3JS.openAsync(dbPath)
        .then(function (db) {
            return db.runAsync('INSERT INTO aliments (Nom, Calories, IG, Proteines, Glucides, Lipides, Fibres) VALUES (?, ?, ?, ?, ?, ?, ?)', [nom, cal, ig, prot, glu, lip, fib])
    
            .then(function () {
                newid = db.lastInsertRowId;
                creerconvers(newid);
                change();
                getalim(newid);
                db.close();
            })
            //creerconvers(newid);
        });
        //creerconvers(newid);
    }
    
    
    

    sometime these or one or some of these function

    creerconvers(newid);
                change();
                getalim(newid);
                db.close();

    are not call

    EDIT: it's realy aleat
    • Edited by flink88 Tuesday, June 17, 2014 5:16 PM
    Tuesday, June 17, 2014 4:55 PM
  • for more detail 

    in this function :

    function creerconvers(newid) {
        //document.getElementById("test").innerHTML = newid;
        var conv = document.getElementById("convers").value;
        //document.getElementById("test").innerHTML = newid;
        console.log('6 : creerconvers est appele avec newid qui vaut : ' + newid);
    
        var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\aliments.sqlite';
        console.log('6.1 : dbpath vaut : ' + dbPath);
        SQLite3JS.openAsync(dbPath)
        .then(function (db) {
            //document.getElementById("test").innerHTML = newid;
            console.log('6.2 avant insert avec conv = ' + conv + 'et newid = ' + newid);
            return db.runAsync('INSERT INTO conversion (gr, cl, ID_aliments) VALUES (?, ?, ?)', [100, conv, newid])
            .then(function () {
                console.log('6.3 avant close avec conv = ' + conv + 'et newid = ' + newid);
                db.close();
            });
        });
    }

    it never go into 

    .then(function (db) {
            //document.getElementById("test").innerHTML = newid;
            console.log('6.2 avant insert avec conv = ' + conv + 'et newid = ' + newid);
            return db.runAsync('INSERT INTO conversion (gr, cl, ID_aliments) VALUES (?, ?, ?)', [100, conv, newid])
            .then(function () {
                console.log('6.3 avant close avec conv = ' + conv + 'et newid = ' + newid);
                db.close();
            });
        });

    Tuesday, June 17, 2014 6:15 PM
  • litle up
    Wednesday, June 18, 2014 5:26 AM
  • after many change i try to do that

    function creeralim() {
    
        //on récupère les valeurs des différents champs
        var nom = document.getElementById("nom").value;
        var cal = document.getElementById("Calories").value;
        var ig = document.getElementById("IG").value;
        var prot = document.getElementById("Proteines").value;
        var glu = document.getElementById("Glucides").value;
        var lip = document.getElementById("Lipides").value;
        var fib = document.getElementById("Fibres").value;
        var conv = document.getElementById("convers").value;
        console.log('4 : la val conv vaut : ' + conv);
    
        var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\aliments.sqlite';
        SQLite3JS.openAsync(dbPath)
        .then(function (db) {
            return db.runAsync('INSERT INTO aliments (Nom, Calories, IG, Proteines, Glucides, Lipides, Fibres) VALUES (?, ?, ?, ?, ?, ?, ?)', [nom, cal, ig, prot, glu, lip, fib])
            .then(function () {
                var newid = db.lastInsertRowId;
                console.log('6.2 avant insert avec conv = ' + conv + 'et newid = ' + newid);
                return db.runAsync('INSERT INTO conversion (gr, cl, ID_aliments) VALUES (?, ?, ?)', [100, conv, newid])
            })
            .then(function () {
                //newid = db.lastInsertRowId;
                //console.log('5 : la val newid vaut : ' + newid);
                db.close();
            });
        });
        //return newid;
    }

    but before the first insert into it go out the function.

    somebody can say me where is the problem?

    thanks a lot


    • Edited by flink88 Wednesday, June 18, 2014 4:08 PM
    Wednesday, June 18, 2014 3:50 PM
  • somebody can help me please
    Thursday, June 19, 2014 7:21 AM
    • Proposed as answer by Flink1988 Thursday, July 3, 2014 5:42 PM
    • Unproposed as answer by Flink1988 Thursday, July 3, 2014 5:42 PM
    Wednesday, July 2, 2014 4:35 PM
  • How fast it your solution in terms of mass selects? SQLite3JS-WinRT was optimized for huge result sets and we ended up using transfering the results as Strings and parsing the string as JSON on the JS site
    Wednesday, July 2, 2014 4:46 PM
  • There is a sample and blog post now. See http://blogs.windows.com/windows/b/buildingapps/archive/2014/07/02/writing-a-sqlite-wrapper-component-for-universal-windows-apps.aspx.
    your link don't work
    Thursday, July 3, 2014 5:43 PM