Overview
Features
Download
Documentation
Community
Add-Ons & Services

SELECT and INSERT of NULLs (SQLite)

Please post support and help requests here.

SELECT and INSERT of NULLs (SQLite)

Postby jobobo » 17 Dec 2012, 07:07

Fairly new here. I was wondering what's the most convenient and efficient way to handle NULLS from a query and then insert them (probably with adjustments) to another table? Below is what I understand so far about extracting from a table using RecordSet (haven't thought about insertion yet). I was thinking DynamicAny would be empty if it's a NULL value, but apparently that's not the case. Any suggestions? Thanks in advance.

Code: Select all
Statement select(session);
select << "SELECT * FROM Files";
select.execute();
RecordSet rs(select);
bool more = rs.moveFirst();
while (more)
{
   int cols = rs.columnCount();
   bool is_null = rs.isNull("Filename"); //appears to work, but not very efficient.
   for (std::size_t col = 0; col < cols; ++col)
   {
      DynamicAny & obj = rs[col];
      if (obj.isEmpty()) { //<-- is not empty when NULL value
         std::cout << "<empty>" << "|"; 
      } else {
         std::cout << "[";
         std::cout << obj.convert<std::string>() << "|";
         std::cout << "]";
      }
   }
   std::cout << std::endl;
   more = rs.moveNext();
}
jobobo
 
Posts: 2
Joined: 16 Dec 2012, 20:56

Re: SELECT and INSERT of NULLs (SQLite)

Postby alex » 18 Dec 2012, 16:56

jobobo wrote:I was thinking DynamicAny would be empty if it's a NULL value, but apparently that's not the case.

Actually it is, see testNullable and testNull.
alex
 
Posts: 1113
Joined: 11 Jul 2006, 16:27
Location: United_States

Re: SELECT and INSERT of NULLs (SQLite)

Postby jobobo » 19 Dec 2012, 10:54

Ok, thank you. I was just curious why isEmpty() is not used to imply NULL values?
jobobo
 
Posts: 2
Joined: 16 Dec 2012, 20:56

Re: SELECT and INSERT of NULLs (SQLite)

Postby alex » 19 Dec 2012, 19:45

jobobo wrote:I was just curious why isEmpty() is not used to imply NULL values?

It is, when directly passed to the statement (as tests demonstrate). DynamicAny is empty if (a) it was not assigned anything or (b) was explicitly reset to empty. In your code, you assign something to it, so it is not empty. Internally, the holder pointer indicates empty state. Perhaps adding null/Nullable/Optional VarHolder implementation and amending logic for empty would be a good idea.
alex
 
Posts: 1113
Joined: 11 Jul 2006, 16:27
Location: United_States


Return to Support

Who is online

Users browsing this forum: No registered users and 2 guests