Overview
Features
Download
Documentation
Community
Add-Ons & Services

Poco::Data Binding SELECT and NULL values

Please post support and help requests here.

Poco::Data Binding SELECT and NULL values

Postby nmaludy » 28 Oct 2011, 20:49

Hello,

I having been using Poco::Data and have run into an issue when selecting/querying for data and the data in the column is NULL.

I have the following database schema:

Code: Select all
TABLE: person
| COLUMN          | DATA_TYPE |
--------------------------------
| name            | TEXT      |
| age             | INT       |
| account_balance | DECIMAL   |


Now all of these columns are optional and are able to have a value of NULL.

So, i do a simple insert:

Code: Select all

struct Person {
  std::string name;
  int age;
  double accountBalance;
}

bool persistPerson()  {
  try {
    Poco::Data::Session databaseSession;
    // connect to the database...
   
    databaseSession.begin();
    Poco::Data::Statement insert(databaseSession);
    insert << "INSERT INTO person VALUES(DEFAULT, DEFAULT, DEFAULT)";
    insert.execute();
    databaseSession.commit();
  } catch (Poco::Exception& ex) {
    std::string error = ex.displayText();
    databaseSession.rollback();
    return false;
  }
  return true;
}



Now when i try to do a query:

Code: Select all
 
std::list<Person>* queryAllPersons() {
  std::list<Person>* results = new std::list<Person>;
  try {
    Poco::Data::Session databaseSession;
    // connect to the database...

    Person person_result;

    Poco::Data::Statement select(databaseSession);
    select <<  "SELECT * FROM person",
        ::Poco::Data::into(person_result.name),
        ::Poco::Data::into(person_result.age),
        ::Poco::Data::into(person_result.account_balance),
        ::Poco::Data::range(0, 1);

    while (!select.done()) {
      select.execute();
      // copy the result into the list
      results->push_back(person_result);
    }
  } catch (Poco::Exception& ex) {
    std::cout << ex.displayText() << std::endl;
    results.clear();
  }
  return results;
}



I get the following results:

Code: Select all
person.name = ""
person.age = 0
person.account_balance = 0.0


It seems every column that was NULL gets the default value for that data type put into it. I found in the documentation that you can use the:

Code: Select all
template < typename T > Extraction < T > * into(
    T & t,
    const T & def
);


That works well for unsigned int's: into(my_int, -1);
But doesn't work so well for signed integers, doubles, or strings. (In my world "", DBL_MAX, INT_MAX, etc are valid values).

Is there a better way to detect NULL values coming out of the database?

-Nick
nmaludy
 
Posts: 5
Joined: 27 Sep 2011, 23:33

Re: Poco::Data Binding SELECT and NULL values

Postby BGawenda » 17 Apr 2012, 12:58

Hi,

I'm also currently playing around with the Poco Framework and am looking for a solution to handle NULL values from database.
Did you find a solution for that?

Björn
BGawenda
 
Posts: 1
Joined: 17 Apr 2012, 12:52

Re: Poco::Data Binding SELECT and NULL values

Postby Hitnrun » 21 Aug 2012, 22:43

I'm also having a problem with this, a DateTime value than can be null, is throwing an SyntaxException when selecting data from the table using Data::Statement.

Looking at the code gave no clues of how to handle it, is there a way to handle this?
Hitnrun
 
Posts: 20
Joined: 01 Apr 2009, 18:32

Re: Poco::Data Binding SELECT and NULL values

Postby alex » 21 Aug 2012, 23:24

If Dynamic::Var is used, for null values, it will be empty. But what does one do with, say, "naked" integer or float? How do you express that as null? And then, different databases treat strings differently - e.g. for Oracle, there is no difference between empty string and null value (see emptyStringIsNull and forceEmptyString properties in Data::AbstractSessionImpl for details). I posted a feature request on this topic long time ago, got no response whatsoever, but I will get back to it soon and most likely do what proposed there.

Comments welcome, if you want to see it in 1.5, now is the time to speak up!
alex
 
Posts: 1044
Joined: 11 Jul 2006, 16:27
Location: United_States

Re: Poco::Data Binding SELECT and NULL values

Postby Hitnrun » 22 Aug 2012, 15:23

I am doing an application that will really need to have null support, so most likely I will try doing this implementation in Poco, contribuing back of course if the team seems it to be acceptable.

Is there a common way of forking the code? Should I create a private SVN and work there, or is there some GitHub mirror?

Also, do you have any suggestion on how I could implement this? Maybe using Poco::Data::Nullable, but will the template type detection still work?
Hitnrun
 
Posts: 20
Joined: 01 Apr 2009, 18:32

Re: Poco::Data Binding SELECT and NULL values

Postby alex » 22 Aug 2012, 16:00

Best in the SVN sandbox. Email me at(alex, dot(pocoproject, org)) for SVN write access and other details.
alex
 
Posts: 1044
Joined: 11 Jul 2006, 16:27
Location: United_States

Re: Poco::Data Binding SELECT and NULL values

Postby Hitnrun » 22 Aug 2012, 19:09

Well, I seem to have fixed my problem (datetime fields with null values on SQLite), can you tell me if you see any compatibility problem with this patch? (I tried attaching the .patch but the board didn't allow)

Code: Select all
Index: SQLite/src/Extractor.cpp
===================================================================
--- SQLite/src/Extractor.cpp   (revision 1980)
+++ SQLite/src/Extractor.cpp   (working copy)
@@ -202,6 +202,8 @@
 
 bool Extractor::extract(std::size_t pos, Date& val)
 {
+      if (isNull(pos))
+         return false;
         std::string str;
         extract(pos, str);
         int tzd;
@@ -213,6 +215,8 @@
 
 bool Extractor::extract(std::size_t pos, Time& val)
         {
+      if (isNull(pos))
+         return false;
         std::string str;
         extract(pos, str);
         int tzd;
@@ -224,6 +228,8 @@
 
 bool Extractor::extract(std::size_t pos, DateTime& val)
         {
+      if (isNull(pos))
+         return false;
         std::string dt;
         extract(pos, dt);
         int tzd;
Index: src/AbstractBinder.cpp
===================================================================
--- src/AbstractBinder.cpp   (revision 1980)
+++ src/AbstractBinder.cpp   (working copy)
@@ -505,6 +505,8 @@
       bind(pos, val.extract<Time>(), dir);
    else if(type == typeid(BLOB))
       bind(pos, val.extract<BLOB>(), dir);
+   else if(type == typeid(void))
+      bind(pos, Keywords::null, dir);
 #ifndef POCO_LONG_IS_64_BIT
    else if(type == typeid(long))
       bind(pos, val.extract<long>(), dir);


Simple test case, previously would crash with SyntaxException, now outputs "i is null", "d is null":

Code: Select all
   Session ses (Poco::Data::SQLite::Connector::KEY, "dummy.db");
   ses << "DROP TABLE IF EXISTS SQLBuilderTest", now;

   ses << "CREATE TABLE SQLBuilderTest (i INTEGER, d DATETIME)", now;

   ses << "INSERT INTO SQLBuilderTest VALUES(:i, :d)", bind(Poco::Dynamic::Var()), bind(Poco::Dynamic::Var()), now;

   RecordSet rs(ses, "SELECT * FROM SQLBuilderTest");
   rs.moveFirst();
   if (!rs.isNull("i"))
      cout << rs["i"].convert<std::string>() << endl;
   else
      cout << "i is null" << endl;
   if (!rs.isNull("d"))
      cout << rs["d"].convert<std::string>();
   else
      cout << "d is null" << endl;
Hitnrun
 
Posts: 20
Joined: 01 Apr 2009, 18:32

Re: Poco::Data Binding SELECT and NULL values

Postby alex » 23 Aug 2012, 04:39

That was actually a bug, thanks for pointing it out. I have committed the bugfix and Nullable test for SQLite:

Code: Select all
void SQLiteTest::testNullable()
{
   Session ses (Poco::Data::SQLite::Connector::KEY, "dummy.db");
   ses << "DROP TABLE IF EXISTS NullableTest", now;
   ses << "CREATE TABLE NullableTest (i INTEGER, r REAL, s VARCHAR, d DATETIME)", now;
   ses << "INSERT INTO NullableTest VALUES(:i, :r, :s, :d)", use(null), use(null), use(null), use(null), now;
   
   Nullable<int> i = 1; assert (!i.isNull());
   Nullable<double> f = 1.5; assert (!f.isNull());
   Nullable<std::string> s = "abc"; assert (!s.isNull());
   Nullable<DateTime> d = DateTime(); assert (!d.isNull());

   ses << "SELECT i, r, s, d FROM NullableTest", into(i), into(f), into(s), into(d), now;
   assert (i.isNull()); assert (f.isNull()); assert (s.isNull()); assert (d.isNull());

   RecordSet rs(ses, "SELECT * FROM NullableTest"); rs.moveFirst();
   assert (rs.isNull("i")); assert (rs.isNull("r")); assert (rs.isNull("s")); assert (rs.isNull("d"));
}

EDIT: added missing ', d' to the list of selected fields.
alex
 
Posts: 1044
Joined: 11 Jul 2006, 16:27
Location: United_States

Re: Poco::Data Binding SELECT and NULL values

Postby Hitnrun » 23 Aug 2012, 14:31

The AbstractBinder change can't be applied too? With it, an empty Poco::Dynamic::Var can be passed on all bind funcions, and it will be converted to NULL automatically.

Code: Select all
Index: src/AbstractBinder.cpp
===================================================================
--- src/AbstractBinder.cpp   (revision 1980)
+++ src/AbstractBinder.cpp   (working copy)
@@ -505,6 +505,8 @@
       bind(pos, val.extract<Time>(), dir);
    else if(type == typeid(BLOB))
       bind(pos, val.extract<BLOB>(), dir);
+   else if(type == typeid(void))
+      bind(pos, Keywords::null, dir);
 #ifndef POCO_LONG_IS_64_BIT
    else if(type == typeid(long))
       bind(pos, val.extract<long>(), dir);
Hitnrun
 
Posts: 20
Joined: 01 Apr 2009, 18:32

Re: Poco::Data Binding SELECT and NULL values

Postby alex » 23 Aug 2012, 14:59

I'll add it, but why do you need to pass DynamicAny explicitly when it is automatically generated internally without passing anything through use() or bind()? And if you want to extract nulls, you can use Nullable.

EDIT: Actually, what I meant is: why would you want to construct and pass empty Dynamic::Var when you can pass Keywords::null to achieve the same thing?
alex
 
Posts: 1044
Joined: 11 Jul 2006, 16:27
Location: United_States

Next

Return to Support

Who is online

Users browsing this forum: No registered users and 4 guests

cron