Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

[SOLVED] Qt 5.14 QML Quick local storage methods for SQL



  • I am attempting to learn QML and Quick by converting a Qt Widgets application to QML for the GUI. I have been through all the forum entries I can find, most of which are pretty old and may not be relevant now, and still I have very few questions answered in my mind.

    My problem lies with the divergence of methods between Qt C++ classes and QML Quick.

    With Qt widgets, I have constructed a class that handles the local storage as a proxy to the database, call methods on that object, and retrieve data from the database to display in a GUI form.

    With QML Quick, as I understand it, local storage is not handled that way. There must be a JavaScript interface that operates the QML local storage object after a db = Sql.openDatabaseSync() or something similar in the main.cpp module.

    It is the process of getting the data from the GUI to the database or the reverse, which is difficult for me. With C++ the data can be passed to the proxy object as variant values or a variant list. I find no equivalent in QML. The closest is a var Array().

    Calling a C++ method of a proxy module is not very straightforward and is not thoroughly explained with the overly simple examples in the docs. There is no comparison code either.

    C++ Example:
    sqliteproxy.cpp

    QString getMyStringValue()
    {
    . . . my SQL Select statement
    }
    
    void saveMyStringValue(QVariant v)
    {
    . . . my SQL INSERT statement
    }
    

    mainwindow.cpp

    SqliteProxy db;
    
    gui.text().setText(db.getMyStringValue());
    

    While the docs do make some effort to hint at there being a local storage interface for Quick, it falls short explaining in a practical way how to make the transition from C++ objects to QML GUI.

    Is there a better example or tutorial that will more practically explain how to use local storage with QML forms?

    BTW my widgets project does not have any SQL in the forms at all. Only the proxy module does the SQL. The GUI only collects data and displays data. The examples all put SQL as part of the GUI.

    I need to see some practical way to mimic my widgets project in functional QML and / or JavaScript so I can move on to other things, in this learning experience.

    So far, I like QML even with these questions in mind. Thanks in advance for your help.

    UPDATE:
    After several days of trying to navigate the QML and Quick docs (not sure why they are two separate things) I have made some small progress on a simple books library application. I will include the code here because it is difficult to explain in short messages what is happening.

    main.qml

    import QtQuick 2.0
    import QtQuick 2.12
    import QtQuick.Window 2.12
    import QtQuick.Layouts 1.11
    import QtQuick.Controls 2.3
    import QtQuick.Controls.Styles 1.4
    
    
    ApplicationWindow {
        id: mainwindow
        visible: true
        width: 640
        height: 500
        title: qsTr("MyBooks Media Library")
    
        Loader { id: viewloader }
    
        header: ToolBar {
            id: mainMenuBar
            width: parent.width
            height: 47
            font.pointSize: 11
    
            RowLayout {
                anchors.fill: parent
                ToolButton {
                    id: actionExit
                    width: 45
                    height: 45
                    display: AbstractButton.IconOnly
    
    
                    padding: 0
                    leftPadding: 1
                    rightPadding: 0
                    bottomPadding: 1
                    topPadding: 1
                    icon.source: "images/process-stop.png"
                    icon.width: 42
                    icon.height: 42
                    Layout.fillHeight: false
                    Layout.fillWidth: false
    
                    ToolTip.text: qsTr("Exit MyBooks")
                    ToolTip.visible: hovered
    
    
                    onClicked: Qt.quit()
                }
                ToolSeparator {
                    padding: vertical ? 10 : 2
                    topPadding: vertical ? 2 : 10
                    bottomPadding: vertical ? 2 : 10
    
                    contentItem: Rectangle {
                        implicitWidth: parent.vertical ? 1 : 24
                        implicitHeight: parent.vertical ? 24 : 1
                        color: "#c3c3c3"
                    }
                }
                ToolButton {
                    id: actionEditAuthors
                    width: 45
                    height: 45
                    display: AbstractButton.IconOnly
    
                    padding: 0
                    leftPadding: 1
                    rightPadding: 0
                    bottomPadding: 1
                    topPadding: 1
                    icon.source: "images/edit-authors.png"
                    icon.width: 42
                    icon.height: 42
                    Layout.fillHeight: false
                    Layout.fillWidth: false
    
                    ToolTip.text: qsTr("Edit Authors")
                    ToolTip.visible: hovered
                }
                ToolButton {
                    id: actionEditTitles
                    width: 45
                    height: 45
    
                    padding: 0
                    leftPadding: 1
                    rightPadding: 0
                    bottomPadding: 1
                    topPadding: 1
                    icon.source: "images/edit-titles.png"
                    icon.width: 42
                    icon.height: 42
                    Layout.fillHeight: false
                    Layout.fillWidth: false
    
                    ToolTip.text: qsTr("Edit Titles")
                    ToolTip.visible: hovered
                }
    
                ToolSeparator {
                    padding: vertical ? 10 : 2
                    topPadding: vertical ? 2 : 10
                    bottomPadding: vertical ? 2 : 10
    
                    contentItem: Rectangle {
                        implicitWidth: parent.vertical ? 1 : 24
                        implicitHeight: parent.vertical ? 24 : 1
                        color: "#c3c3c3"
                    }
                }
                ToolButton {
                    id: actionHelp
                    width: 45
                    height: 45
                    display: AbstractButton.IconOnly
    
                    padding: 0
                    leftPadding: 1
                    rightPadding: 0
                    bottomPadding: 1
                    topPadding: 1
                    icon.source: "images/help-contents.png"
                    icon.width: 42
                    icon.height: 42
                    Layout.fillHeight: false
                    Layout.fillWidth: false
    
                    ToolTip.text: qsTr("Help Contents")
                    ToolTip.visible: hovered
    
                    onClicked: Qt.openUrlExternally("https://qso.com/qLogger/index.html")
                }
                Rectangle {
                    id: dummy
                    height: 45
                    implicitWidth: 390
                    antialiasing: true
                    enabled: false
    
                    Image {
                        id: imgBlank
                        height: 45
                        width: parent.implicitWidth
                        fillMode: Image.scale
                        source: "images/blank.png"
                    }
                }
            }
        }
    
        Image {
            id: imgOwl
            x: 27
            y: 20
            width: 125
            height: 108
            fillMode: Image.PreserveAspectFit
            source: "images/Bookowl.png"
        }
    
        Image {
            id: imgShelf
            x: 32
            y: 135
            width: 355
            height: 283
            fillMode: Image.Stretch
            source: "images/Bookshelf.png"
        }
    
        Rectangle {
            id: btnBooks
            x: 170
            y: 40
            width: 91
            height: 60
            border.color: "#999999"
            border.width: 1
            radius: 6
            gradient: Gradient {
                GradientStop { position: 0.0; color: "lightgray" }
                GradientStop { position: 1.0; color: "gray" }
            }
    
            Text {
                id: lblButton
                x: 9
                y: 23
                text: qsTr("View / Search")
                font.bold: false
                font.family: "DejaVu Sans"
                font.pixelSize: 11
            }
    
            MouseArea {
                id: btnBooksClickArea
                x: 1
                y: 2
                width: 88
                height: 57
                hoverEnabled: false
                visible: true
                acceptedButtons: Qt.LeftButton
    
                onClicked: {
                    viewloader.source = "titlesview.qml"
                }
            }
        }
    }
    

    The images are not important. You can substitue any image.

    titleslistview.qml

    import QtQuick 2.14
    import QtQuick.Controls 2.14
    import QtQuick.Controls 2.3
    import QtQuick.Layouts 1.3
    import Qt.labs.qmlmodels 1.0
    import QtQuick.LocalStorage 2.12
    
    import "sqliteproxy.js" as JS
    
    Dialog {
        id: titlesView
        property alias titlesView: titlesView
    
        title: "Listing By Title and Author"
        standardButtons: Dialog.Close
        visible: true
    
        width: 1190
        height: 640
    
        onRejected: titlesView.close()
    
    
        RowLayout {
            width: parent.width
            height: 36
            visible: true
            spacing: 6
    
            Text {
                id: lblSrchTitles
                x: 10
                y: 10
                text: qsTr("Search Titles:")
                font.family: "DejaVu Sans"
            }
    
            Rectangle {
                id: srchTitlesBox
                x: 101
                y: 8
                width: 150
                height: 22
                color: "#00000000"
                Layout.maximumHeight: 30
                Layout.maximumWidth: 1920
                Layout.preferredHeight: 20
                Layout.preferredWidth: 150
                Layout.minimumHeight: 20
                Layout.minimumWidth: 100
                border.color: "#646464"
    
                TextInput {
                    id: editSrchTitles
                    x: 1
                    y: 1
                    width: 180
                    height: 20
                    text: qsTr("")
                    font.family: "DejaVu Sans"
                    font.pixelSize: 12
                }
            }
    
            Text {
                id: lblSrchAuthor
                x: 284
                y: 12
                text: qsTr("Search Author:")
                font.family: "DejaVu Sans"
                font.pixelSize: 12
            }
    
            Rectangle {
                id: srchAuthorBox
                x: 376
                y: 9
                width: 182
                height: 22
                color: "#00000000"
                border.color: "#666666"
                TextInput {
                    id: editSrchAuthor
                    x: 1
                    y: 1
                    width: 180
                    height: 20
                    text: qsTr("")
                    font.family: "DejaVu Sans"
                    font.pixelSize: 12
                }
            }
    
            Button {
                id: btnClrSrch
                x: 565
                y: 8
                width: 75
                height: 25
                text: "Clr Srch"
                font.family: "DejaVu Sans"
                antialiasing: false
                smooth: false
    
            }
    
            Button {
                id: btnAddNewBook
                x: 662
                y: 7
                width: 75
                height: 25
                text: "Add New"
                font.family: "DejaVu Sans"
                antialiasing: false
                smooth: false
            }
    
            Button {
                id: btnEditBook
                x: 749
                y: 8
                width: 75
                height: 25
                text: "Edit"
                font.family: "DejaVu Sans"
                antialiasing: false
                smooth: false
            }
    
            Button {
                id: btnDelBook
                x: 833
                y: 8
                width: 75
                height: 25
                text: "Delete"
                font.family: "DejaVu Sans"
                antialiasing: false
                smooth: false
            }
        }
    
        TableView {
            width: parent.width - 12
            height: parent.height - 90
            visible: true
    
            model: ListModel
        }
    }
    

    bookslistmodel.qml

    pragma Singleton
    
    import QtQuick 2.0
    import QtQml.Models 2.3
    import QtQuick.LocalStorage 2.0
    
    import "sqliteproxy.js" as JS
    
    ListModel {
        id: booksListModel
        property alias booksListModel: booksListModel
    
        Component.onCompleted: {
            var result = JS.get()
            var rows = result.rows.length
    
            for ( var row = 0; row < rows; ++row )
            {
                    id: result.Id
                    title: result.rows.item(1).Title
                    author: result.rows.item(i).Author
                    media: result.rows.item(i).MediaType
                    notes: result.rows.item(i).Notes
                    booksListModel.append(id,title,author,media,notes)
            }
        }
    }
    

    sqliteproxy.js

    
    function get() {
        var db = LocalStorage.openDatabaseSync("../Data/MyBooks.db", "", "MyBooks", 1000000)
        var strDML = "SELECT Books.ID, Books.Title Title, Authors.LastName||', "
        strDML = strDML + "'||Authors.FirstName Author, MediaType.Media Media, "
        strDML = strDML + "Books.Notes FROM Books, Authors, MediaType "
        strDML = strDML + "WHERE Books.AuthKey   = Authors.ID  AND "
        strDML = strDML + "Books.MediaKey  = MediaType.ID "
        strDML = strDML + "ORDER BY Books.Title ASC, Authors.LastName ASC, Authors.FirstName ASC "
    
        try
        {
            db.transaction(function(trans)
            {
                var result = tx.executeSql(strDML)
                return result
            })
        }
        catch (err)
        {
            console.log("Error creating table in database: " + err)
        };
    }
    

    So far this small app runs and the second window displays within the first. However, no rows display in the TableView component.

    BTW this tutorial is worth more than mst of the Qt Docs on the subject. Very much worth viewing for novices.



  • I still need some comment on this. Surely someone knows the solution.



  • Well so much for this forum being any help. Giving up and going back to C++ Widgets.



  • @ad5xj said in [SOLVED] Qt 5.14 QML Quick local storage methods for SQL:

    LocalStorage.openDatabaseSync("../Data/MyBooks.db", "", "MyBooks", 10000);

    Are you sure that you can access the database file i.e. 'MyBooks.db' from the file system and is accessible to the 'sqliteproxy.js' file ? I think that the database file is not accessible, to verify this you can check if the model contains the data after loading from script file.

    Try checking this example provided by Qt here https://doc.qt.io/qt-5/qtquick-localstorage-example.html



  • And also JS script file provided has errors. "tx" is not defined anywhere. probably it is "trans"

    db.transaction(function(trans)
            {
                var result = trans.executeSql(strDML)
                return result
            })
    

Log in to reply