Show sql data on scene

Hi,

i want to show the data from sql like this -

col1       col2      col3      col4
------------------------------------
data1      data2     data3    data4
data5      data6     data7    data8
data9      data10    data11   data12

It could have around 10 rows and only 4 columns. all columns should be seen on a scene and we can scroll vertically to load rows, so what would be the best way to show the data?

I am thinking of using scrollview with textwidget, but how to load multiple rows and columns in a scrollview?

Can anyone give example of sqlite3_step(). Couldn’t find any example for reference. i have string and int in my data…

and how to load it on scrollview?

anything would be appreciated… :grinning:

I use SQLite a lot in my projects. Here is a function I used to populate objects from the database:

std::vector<SQLFieldObject> SQLManager::ReturnSQLVectorFields(const std::string& _recordType, const std::string& _sql, int _rowCount)
{
    sqlite3_stmt* statement;
    
    //std::vector<SQLFieldObject> v;
    
    if (sqlite3_prepare_v2(SQLINSTANCE->getDatabase(), _sql.c_str(), -1, &statement, 0)
        == SQLITE_OK)
    {
        int result = 0;
        
        while(true)
        {
            result = sqlite3_step(statement);
            
            if(result == SQLITE_ROW)
            {
                if (_recordType == AppConstants::sSQLFieldObject)
                {
                    std::vector<SQLFieldObject> v;
                    v.reserve(_rowCount);
                    
                    for(int r=0; r < _rowCount; r++)
                    {
                        SQLFieldObject o;
                        
                        o.putFieldNumber(sqlite3_column_int(statement, 0));
                        o.putFieldTag(sqlite3_column_int(statement, 0));
                        o.putFieldName(sqlite3_column_int(statement, 0));
                        o.putFieldCenterPointX(sqlite3_column_int(statement, 4));
                        o.putFieldCenterPointY(sqlite3_column_int(statement, 5));
                        o.putFieldCreateDate(sqlite3_column_int(statement, 6));
                        o.putFieldTotalTimesPlanted(sqlite3_column_int(statement, 7));
                        o.putFieldTotalTimesPlantedCounter(sqlite3_column_int(statement, 8));
                        o.putFieldTotalPlantsCounter(sqlite3_column_int(statement, 9));
                        o.putFieldTotalTimesRejuvinated(sqlite3_column_int(statement, 10));
                        o.putFieldState(sqlite3_column_int(statement, 11));
                        o.putFieldSignCenterPointX(sqlite3_column_int(statement, 12));
                        o.putFieldSignCenterPointY(sqlite3_column_int(statement, 13));
                        
                        v.push_back(o);
                        sqlite3_step(statement);
                    }
                    
                    return v;
                }
            }
            else
            {
                break;
            }
        }
    }
    
    //return v;
}
1 Like

Thanks @slackmoehrle :grinning:

i got the databse creation, creating table and printing data working.

but when i add variable in insert statement it doesn’t insert data into table, tried lots of ways

 sql="insert into test2(name,time) values(%s,%i)",(uname,t);

is this the rightway to add variable, reference from - How to insert variable at sqlite???

i also tried

'" + uname +"'

as found on stackoverflow, didn’t work.

edit - uname is std::string
t is int

show me exactly uname and t and let me write the statement.

Also, there is a great app SQLPro for SQLite that I use to test all of my work.

Create statement-

result=sqlite3_exec(pdb,"create table test2(name text,success integer,failed integer, time integer)",NULL,NULL,NULL);

and variables -

std::string uname = "abcd";
int t = 10;

and downloading sqlpro, seems a good tool…

I do mine a bit differently. Consider this:

 _sql = "create table " +
    std::string("Master") +
    std::string(" (_id TEXT PRIMARY KEY, _value INT);");
    
    //std::cout << "sql: " << _sql << std::endl;
    
    result = sqlite3_exec(_pdb, _sql.c_str(), NULL, NULL, NULL);

sorry man, i had a problem with insert statement, not with create statement…by mistake i shared create statement…(dont work when you are sleepy… :smile: )

sql="insert into test2(name,time) values(%s,%i)",(uname,t);

please share the right way to write insert statement…

I do something like this:

std::string GeneratedOrder::getSQLForGeneratedOrder()
{ 
    std::string _sql = "INSERT INTO " +
    std::string(AppConstants::AppDBOrdersTable.c_str()) +
    std::string(" VALUES(") +
    std::to_string(this->getOrderID()) + std::string(", ") +
    std::to_string(this->getOrderInternalID()) + std::string(", ") +
    std::string("'") + std::string(this->getOrderName()) + std::string("', ") +
    std::to_string(this->getOrderType()) + std::string(", ") +
    std::to_string(this->getOrderStatus()) + std::string(", ") +
    std::to_string(this->getOrderCount()) + std::string(", ") +
    std::to_string(this->getOrderDifficulty()) + std::string(", ") +
    std::to_string(this->getOrderReward()) + std::string(", ") +
    std::to_string(0) + // order complete is always hardcoded
    std::string(");");
    
    //std::cout << "Insert Order SQL: " << _sql << std::endl;
    
    return _sql;
}

i don’t have that functions to get value “this-getOrderID()”, i have directly string and int variable, unable to get it done, please help…whats wrong in my statement…

sql="insert into test2(name,time) values(%s,%i)",(uname,t);
result=sqlite3_exec(pdb,sql.c_str(),NULL,NULL,NULL);
    if(result!=SQLITE_OK)
        log("insert data failed.. ffffffffffffffffffff");

This was just an example of what i do, with data from my own code. The same principals apply.

Edit: I did the following:

CREATE TABLE test2(uname string, time int);

Then to insert, I did this:

insert into test2(uname,time) values("slackmoehrle",1);

which, if I were to build this in code, I would have done:

std::string sql = "insert into test2(uname, t) values(" + 
std::string("'") + uname + std::string("', ") + 
std::to_string(time) +
std::string(");");

note: I didn’t test the string in code anyplace. It might need an adjustment.

note: storing time as an int might be weird.

1 Like

It is giving error -

expected '(' for function-style cast or type construction

on second line…

The statement seems right with brackets and quotes, and i googled it but couldn’t figure it out…

Can you paste here the whole statement so I can see it?

Statement is the same as you gave with name changed a little -

std::string sql = "insert into test2(name, time) values(" + 
std::string=("'") + uname + std::string=("', ") +  std::to_string(t) +
std::string(");");

with variables declared as -

std::string uname = "abc";
int t = 5;

sorry, put this after

std::cout << sql << std::endl;

and send me that.

because of the error in the sql query, project doesn’t execute, so how can we cout the sql.

I see the issue.

use this:

std::string sql = "insert into test2(name, time) values(" +
    std::string("'") + uname + std::string("', ") +  std::to_string(t) +
    std::string(");");
    
    std::cout << sql << std::endl;
1 Like

such a silly issue… :grimacing:…Thanks