float Array rounds decimal values into zeros !!!

[sheennave]

When i insert value into table row (data type float), smt rounds decimal values into zeros. Here is the dramatically simplified code in order to see the situation.Interestingly, assigning a single array value into a variable (as usual) doesn't work here !
Is this a SQLite bug ??

[sheennave]

When i insert value into table row (data type float), smt rounds decimal values into zeros. Here is the dramatically simplified code in order to see the situation.Interestingly, assigning a single array value into a variable (as usual) doesn't work here !
Is this a SQLite bug ??

float * arraySource_energy_kcal = new float[1]; // member capacity
float temp = 6.08 ;
arraySource_energy_kcal[0] = temp ; // Array initialised

label1->Text = Convert::ToString(arraySource_energy_kcal[0]); // initialized array value displayed on the screen OK , 67.08

float Weight = 100 ;

// Db connection
SQLiteConnection ^connection = gcnew SQLiteConnection();
connection->ConnectionString = "Data Source = stock.db3";
connection->Open();
// Db connection

SQLiteCommand^ command1= connection->CreateCommand();
command1->CommandText = "CREATE TABLE meyve(`category` TEXT,`weight` FLOAT, `energy_kcal` FLOAT ) ";
command1->ExecuteNonQuery();

SQLiteCommand^ command2= connection->CreateCommand();
command2->CommandText = " INSERT INTO meyve(`category`,`weight`,`energy_kcal`) VALUES ('greyfurt', '"+Weight+"' , '"+arraySource_energy_kcal[0]+"' ) ";
command2->ExecuteNonQuery();

connection->Close();

[Ancient Dragon]

>>Is this a SQLite bug ??

No -- it is a bug in your program.

>>VALUES ('greyfurt', '"+Weight+"' , '"+arraySource_energy_kcal[0]+"' ) ";

Do not put quotes around the floats -- the quotes are only for the strings. You will have to format the query before sending it to SqLite, something like this

string cmd;
cmd = " INSERT INTO meyve(`category`,`weight`,`energy_kcal`) VALUES ('greyfurt',";
stringstream s;
s << Weight << "," << arraySource_energy_kcal[0];
cmd += s.str();

[sheennave]

Dear Dragon, forget about stupid quotes they are ok except arrays..? Still when i insert a decimal value into table row from the array , smt rounds decimal values into zeros on the table.

float arr[2];
arr[0] = 245 ; // right , 245 on the table
arr[1]= 1.098 ; // wrong ! , 0 in the table

SQLiteConnection ^Conn = gcnew SQLiteConnection();
Conn->ConnectionString = "Data Source = test.db3";
Conn->Open();

SQLiteCommand ^cmd = Conn->CreateCommand();
System::String ^query = "INSERT INTO xenon (`enerji_kcal`,`protein_ounce`) VALUES ('"+arr[0]+"','"+arr[1]+"' ) ";
cmd->CommandText = query ;
cmd->ExecuteNonQuery();

[Ancient Dragon]

I realize you are using CLR/C++, but here is a c++ program that works ok with floats.

#include <iostream>
#include <string>
#include "sqlite3.h"

// Delete this pragma if you are not using
// a Microsoft compiler.
#pragma warning(disable: 4996)

using std::cout;
using std::cin;
using std::string;

// Function prototypes
void fill(sqlite3* db, string tbname);
void CreateDatabase(sqlite3** db);
void CreateTable(sqlite3* db, string& tbname);
void display(sqlite3* db, string tbname);


// This is a callback function that is called by Sqlite to process the 
// resultset of a query.  There are other ways to do it without using
// a callback function, such this is the simplest to implemnent.
//
// Parameters are:
//      argc -- the number of rows in the resultset
//      argv[] -- the data for each row
//      azColName -- the name of each column
int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i<argc; i++){
    cout << azColName[i] 
          << " = ";
    if( argv[i] )
        cout << argv[i];
    else
        cout << "NULL";
     cout << "\n";
  }
  return 0;
}

// This function just display an error message that
// may have been returned by the various Sqlite functions.
void dsperr(char**db_err)
{
    if( *db_err )
    {
        cout << *db_err << "\n";
        sqlite3_free(*db_err); // release (free) memory
        *db_err = 0; // reinitialize the caller's pointer
    }
}

// This function is called from several places to get 
// the table name.  
void GetTablename(string& tbname)
{
    cout << "Enter the table name\n";
    cin >> tbname;
}


int main()
{
    sqlite3* db = 0; // database connection
    string tbname;   // table name
    bool done = false;
    while(!done)
    {
        string answer;
        cout << "\n\nPlease select one of the folowing options\n";
        cout << "1. Create a new or open an existing database\n";
        cout << "2. Create a new table\n";
        cout << "3. Add some data\n";
        cout << "4. List all data\n";
        cout << "5. Quit\n";
        cin >> answer;
        cin.ignore();
        switch(answer[0])
        {
            case '1':
                CreateDatabase(&db);
                break;
            case '2':
                CreateTable(db, tbname);
                break;
            case '3':
                fill(db, tbname);
                break;
            case '4':
                display(db, tbname);
                break;
            case '5':
                done = true;
                break;
        }

    }
    sqlite3_close(db);
}

// Create a new or open an existing database.
// If the database does not exist it will be
// created.  Note that you can specif the full
// path and filename which may, or may not, contain spaces.
void CreateDatabase(sqlite3** db)
{
    string dbname;
    cout << "Please enter the name of the database\n";
    getline(cin, dbname);
    int n = sqlite3_open(dbname.c_str(), db);
    if( n != SQLITE_OK )
    {
        cout << "Error opening database.\n";
    }
    else
        cout << "Database open ok\n";
}

// Add a new table to the database.  If the table
// name already exists then an error message will be
// displayed.
void CreateTable(sqlite3* db, string& tbname)
{
    char* db_err = 0;
    string statement;
    GetTablename(tbname);
    if( tbname == "" )
        return;
    statement = "CREATE TABLE " + tbname + " (`category` TEXT,`weight` FLOAT, `energy_kcal` FLOAT ) ";
    int n = sqlite3_exec(db, statement.c_str(), NULL, 0, &db_err);
    dsperr(&db_err);
    if( n != SQLITE_OK )
    {
        cout << "Error executing \"" << statement << "\"\n";
    }
    else
        cout << "Table created successfully\n";

}


// Just dump some data into the table.  You don't
// have a choice of the kind or quantity of data to be entered.
//
void fill(sqlite3* db, string tbname)
{
    char* db_err = 0;
    float weight = 100.0F;
    float arraySource_energy_kcal = 6.08F;

    if( tbname.length() == 0)
    {
        GetTablename(tbname);
        if( tbname == "" )
            return;
    }
    char buf[255];
    //sprintf(buf,"insert into %s values(%d);", tbname.c_str(), i);
    sprintf(buf,"insert into %s values('%s',%f,%f);", tbname.c_str(), "greyfurt",
            weight,arraySource_energy_kcal);

    int n = sqlite3_exec(db, buf, NULL, 0, &db_err);
    dsperr(&db_err);
    if( n != SQLITE_OK )
    {
        cout << "Error inserting value\n";
    }

}

// Query the database for all the data in the table and
// display it in the callback function.  
void display(sqlite3* db, string tbname)
{
    char* db_err = 0;
    string select;
    if( tbname.length() == 0)
    {
        GetTablename(tbname);
        if( tbname == "" )
            return;
    }
    if( tbname.length() > 0)
    {
        select = "select * from " + tbname + ";";
        sqlite3_exec(db, select.c_str(), callback, 0, &db_err);
        dsperr(&db_err);
    }

}

[sheennave]

Thank you . It's over .