Text files in QML

[KillerSmath]

Just complementing what lelev said, the advantage is that LocalStorage SQL approach gives you a good interface to insert, remove and recover data from the file. But nothing restricts you to use a raw file approach to this work.

[tomy]

@LeLev
@KillerSmath

I'm not sure which on is preferred for a smartphone app. But since SQL is popular, widely used, and I'm sure sooner or later I will have to learn it for apps I go for that.

So I like to do the task using SQL. I hope the resource I'm studying (Local Storage - SQL) is good for that goal.

[tomy]

I wrote this:

Window {
    visible: true
    width: 640; height: 480
    title: qsTr("SQL Example")
    property var db

    TextField {
        id: name
        placeholderText: qsTr("Enter Username")
        objectName: 'userNam'
    }

    Button {
        id: saveB
        text: "Save"
        anchors.left: name.right
        onClicked: {
            initDatabase()
            if(storeData(name.displayText))
                userCheck.text =  "Username Exits"
            else
                userCheck.text = "Username Saved"
        }
    }

    Text {
        id: userCheck
        anchors.centerIn: parent
    }

    function initDatabase() {
        db = LocalStorage.openDatabaseSync("data", "1.0", "Save Usernames", 1000000)
        db.transaction( function(tx)
        { tx.executeSql('CREATE TABLE IF NOT EXISTS data(name TEXT)') })
    }

    function storeData(username) {
        if(!db) return

        db.transaction( function(tx) {
            var result = tx.executeSql('SELECT * from data');

            for (var i=0; i<result.rows.length; ++i)
                if(result.rows.item(i) === username)
                    return 1

            tx.executeSql('INSERT INTO data VALUES (?)', ['username'])
            return 0
        })
    }
}

My purpose is that when the user types "John Smith", the for loop in the storeData function firstly searches in the database for such a name. If exits, it returns 1, else 0.

If 1 is returned, the text written will be "Username Exits" otherwise "Username Saved".

Two questions:

1- Is the code above correct for this purpose?
2- When a name is entered more than one time, it once again writes "Username Saved", why? It's as though the for loop doesn't work properly.

[KillerSmath]

Hi @tomy

1- Is the code above correct for this purpose?

yes, however, you should to call initDatabase just one time, for example, using Component.onCompleted.

function initDatabase() {
        db = LocalStorage.openDatabaseSync("data", "1.0", "Save Usernames", 1000000)
        db.transaction( function(tx)
        { tx.executeSql('CREATE TABLE IF NOT EXISTS data(name TEXT)') })
    }

The above code tries to open the database file then execute a query to create the table data if not exists (not the database but the table). this function is called by your code for everytime you have clicked in save button.

That is the openDatabaseSync signature:

LocalStorage.openDatabaseSync(identifier, version, description, estimated_size, callback(db))

The above code returns the database identified by identifier. If the database does not already exist, it is created, and the function callback is called with the database as a parameter. identifier is the name of the physical file (with or without full path) containing the database. description and estimated_size are written to the INI file (described below), but are currently unused.

Note: a callback function is called if the database not exists, so use this feature. Write a full sql query to create your database only execute it if the database file not exists.

---

2- When a name is entered more than one time, it once again writes "Username Saved", why? It's as though the for loop doesn't work properly.

I noticed a comparison mistake inside your storeData.

Take a look at tx.executeSql documentation.

rows.item(i) -> Function that returns row i of the result

That means, returns an object with respective column values:

{
   column_1: value_1,
   column_2: value_2
}

But, you are comparing a string with this object, and it will return false...

So, to solve this mistake, you should replace this comparison as below

for (var i=0; i<result.rows.length; ++i)
    if(result.rows.item(i).name === username) // access the attribute name of row
        return 1

[tomy]

@KillerSmath

Thank you for your answer.
I changed the code this way:

Window {
    visible: true
    width: 640; height: 480
    title: qsTr("SQL Example")
    property var db

    TextField {
        id: name
        placeholderText: qsTr("Enter Username")
        objectName: 'userNam'
    }

    Button {
        text: "Save"
        anchors.left: name.right
        onClicked: storeData(name.displayText)
    }

    Text {
        id: userCheck
        anchors.centerIn: parent
    }

    Component.onCompleted: initDatabase()

    function initDatabase() {
        db = LocalStorage.openDatabaseSync("data", "1.0", "Save Usernames", 1000000)
        db.transaction( function(tx)
        { tx.executeSql('CREATE TABLE IF NOT EXISTS data(name TEXT)') })
    }

    function storeData(username) {
        if(!db) return

        db.transaction( function(tx) {
            var result = tx.executeSql('SELECT * from data');

            for (var i=0; i<result.rows.length; ++i)
                if(result.rows.item(i).name === username) {
                    userCheck.text =  "Username Exits"
                    return }

            tx.executeSql('INSERT INTO data VALUES (?)', ['username'])
            userCheck.text = "Username Saved"
        })
    }
}

You're right about both initializing the database and the for loop if condition, but the problem with a same name given more than one time still exits!
On the callback function, I read that it's optional and also in the code above if the database is not created so the line if(!db) return terminates the function, while in reality that condition result in false. So the database does exist. By the way, I don't know what callback function to write for that. There's no example of that in the Docs page!

On the issue: For example I run the project and enter the name "John" in the field the hit Save. It writes "Username Saved". Then I close the project and re-run it. This time too, when entering the same name as before, "John", I once again get the message: "Username Saved"! :(

What's the problem with it yet, in your opinion, please?

[tomy]

@tomy
Part of the problem was solved by replacing
tx.executeSql('INSERT INTO data VALUES (?)', ['username'])
with
tx.executeSql('INSERT INTO data VALUES (?)', [username])

[KillerSmath]

@tomy mentioned:

There's no example of that in the Docs page!

True. They only mentioned this feature and din't show no one example, although this feature is always used when the database is not exists.

A database callback is similar of transation callback transaction(callback(tx)). The only difference is the parameter variable. callback(db) [look at openDataaseSync signature)

You can directly create it when you call the function or pass a function instead. Below is an example of how you can use this feature by function pass.

function initDatabase() {
    db = LocalStorage.openDatabaseSync("data", "1.0", "Save Usernames", 1000000, createDatabaseCallBack)
    console.log("Database has been loaded")
}

function createDatabaseCallBack(db){
    console.log("Database has been created")
    db.transaction( function(tx) {
        tx.executeSql('CREATE TABLE IF NOT EXISTS data(name TEXT)')
    })
    db.changeVersion("", "1.0");
}

Note: i called changeVersion because for some reason, the source code uses an empty string as changeVersion when it is creating by callback, so it replace "" -> "1.0"

---

On the issue: For example I run the project and enter the name "John" in the field the hit Save. It writes "Username Saved". Then I close the project and re-run it. This time too, when entering the same name as before, "John", I once again get the message: "Username Saved"! :(

Could you verify if the file is stored on your android device ? It is working on Linux.

[tomy]

@KillerSmath

Thank you. What's the advantage of using a callback function for creating a database in comparison to not using it, please?

Could you verify if the file is stored on your android device ? It is working on Linux.

The prior problem using the replacement written in the previous post of me was solved.

Now I want to be able to empty the database. For that I added this button:

Button {
       text: "Empty Database"
       anchors.top: name.bottom
       onClicked: db.transaction(function (tx) {
           tx.executeSql('DROP TABLE data');
       })
   }

But I get this error for that! :(
Error: no such table: data Unable to execute statement

[KillerSmath]

@tomy
DROP TABLE data will delete the table 'data' from your database. I am not sure, but when you say "Empty Database", you are trying to clear all data inside of Table Data (records) instead of delete the table.

To delete records from your table you should use this command:
DELETE FROM table_name [WHERE condition]
Note: Where Condiction is useful to delete only specific records from your table, for example where name = "Jack Sparrow".

[tomy]

This post is deleted!