[Dbo] retrieving and iterating on multiple raws results

Added by Vincenzo Romano 3 months ago

Hi all.
I just started experimenting with Wt::Dbo but failed to fully understand the provided tutorial.
I need to retrieve a set of raws from a stored procedure (but it could be an actual table or view).
Then I need to iterate of that set.

My data is:

struct ConfigEntry {
  std::string k;
  std::string v;
  template<class Action> void persist( Action& a ) {
    Wt::Dbo::field( a,k,"k");
    Wt::Dbo::field( a,v,"v");
  }
};

The query I'd run is:

select * from a_function()

where the result is a "virtual" table with two text columns called 'k' and 'v'.

Unluckily it's far from being clear to me how to write the call:
aaa res = sessionObject.Query<bbb>( "select * from a_function()" );

In particular I am failing to understand:
  1. whether the classes aaa and bbb are the same
  2. what'd be their actual types.

Any hint, link, example?


Replies (12)

RE: [Dbo] retrieving and iterating on multiple raws results - Added by lm at 3 months ago

It looks like you have the first step: creating a class to hold results, and your persist template looks great.

You'll probably want:

auto entries {sessionObject.find<ConfigEntry>().resultList()}
If you want the results in a std::vector, and spelled out long so that you can see the types, I think it works out to:
Wt::Dbo::Query<Wt::Dbo::ptr<ConfigEntry>> query {sessionObject.find<ConfigEntry>()};
Wt::Dbo::collection<Wt::Dbo::ptr<ConfigEntry>> collection {query.resultList()};
std::vector<Wt::Dbo::ptr<ConfigEntry>> vec {collection.begin(), collection.end()};
You may be wondering why you would want to use a std::vector when you have Wt::Dbo::collection. Excellent question! The latter can only iterate the results once and cannot go backward, etc. It's efficient if all you need to do is iterate once, and you don't want to hold the whole set in memory at the same time, but often you do want the whole set in memory at a time and want to be able to search through them and run standard algorithms on them, etc.

I thought this was a little confusing at first, too. Let me know if anything else seems foggy to you.

RE: [Dbo] retrieving and iterating on multiple raws results - Added by Vincenzo Romano 3 months ago

Thanks @lm.
While I understand the syntax you are using, I don't see a reference to the actual SQL query.
Would you please elaborate a little bit more on this?

RE: [Dbo] retrieving and iterating on multiple raws results - Added by lm at 3 months ago

Oh, of course. The query returned by Wt::Dbo::Session::find will return all results. If you want to add a where clause, you'll do

Wt::Dbo::Query<Wt::Dbo::ptr<ConfigEntry>> query {sessionObject.find<ConfigEntry>()};
query = query.where("k = ?");
query = query.bind("Cool Value");
The table that will be consulted is that used in the call to Wt::Dbo::Session::mapClass. If you really, really want to enter SQL, you'll do so this way:
Wt::Dbo::Query<Wt::Dbo::ptr<ConfigEntry>> query {sessionObject.query<Wt::Dbo::ptr<ConfigEntry>>("select u from a_function();")};
I'm not sure about the syntax or the return types because I've never done this.

RE: [Dbo] retrieving and iterating on multiple raws results - Added by Vincenzo Romano 3 months ago

Thanks again, lm.
I failed to mention I am (still) on v3 because of the complexities with the syntax/semantics of @std::unique_ptr
. But this is another story.

Anyway, as the source for the data I need to read is not a table but a function, I (think I) cannot use thge ::mapClass() method.
Is this correct?
I ask because I use to push as much as data handling to the DB (I prefer Postgres) so I rarely handle tables directly.
I've done more searching within the documentation and the syntax for my query seems to be slightly simpler than your hint:

Wt::Dbo::Query<Wt::Dbo::ptr<ConfigEntry>> res = dbSession.query< Wt::Dbo::ptr<ConfigEntry> >( "select k,v from session_init()" );

or, even leaner,
auto res = dbSession.query< Wt::Dbo::ptr<ConfigEntry> >( "select k,v from session_init()" );

What do you think about it?
Maybe it's just me, but syntax in C++ is getting polarized: it's either really complex but esplicative, or very simple but obscure.
But also this is another story.

RE: [Dbo] retrieving and iterating on multiple raws results - Added by Vincenzo Romano 3 months ago

Unluckily, the use of auto yields (weirdly to me) to a compiler (g++ v8.2.1) error:

init.cpp:49:19: error: invalid use of incomplete type ‘class Wt::Dbo::collection<Wt::Dbo::ptr<ConfigEntry> >’
   for( auto i = res.resultList().begin(); i != res.resultList().end(); i++  ) {
                                 ^                  ^

It looks like that the auto didn't infer an incomplete type!
Any hint on what I am missing?

RE: [Dbo] retrieving and iterating on multiple raws results - Added by Vincenzo Romano 3 months ago

I was missing a #include <Wt/Dbo/collection>.
It looks weird to me that I need to manually include it!

RE: [Dbo] retrieving and iterating on multiple raws results - Added by lm at 3 months ago

Yeah, that looks right. I don't personally hold to "push as much as data handling to the DB". I think it's easier to debug C++ than stored procs, etc., but I won't judge ;-)

The incomplete type problem should be there with the explicit type as well; glad you got it sorted quickly.

I agree: either we're typing and typing type names, or use auto and can't tell what anything is :-D. That is really what auto is mostly for: so you can't tell what stuff is. For instance, with lambdas, there is no way to name the actual type:

some_mystery_type my_lamb = [](){};
There is no (standard) way to know the type name, so auto must be used if you want the actual lambda object. In this case, the obscurity is welcome.

There is some more complication in Wt where there is some casting that needs to be done so auto isn't so helpful because it doesn't do the cast for you. I forgot where that is exactly...oh well.

Good luck!

RE: [Dbo] retrieving and iterating on multiple raws results - Added by Roel Standaert 3 months ago

Which other includes do you have?

Including Wt/Dbo/Dbo.h should cause Wt/Dbo/collection.h to be included (along with a lot of other headers). (It includes Wt/Dbo/Types.h and Wt/Dbo/Impl.h.)

If you're including some unrelated Dbo headers I don't see a reason why you'd expect that Wt/Dbo/collection.h doesn't need to be included?

RE: [Dbo] retrieving and iterating on multiple raws results - Added by Roel Standaert 3 months ago

lm at: auto can be used for the result type, but only if you call resultValue() or resultList(). Otherwise it's indeed not implicitly converted to the result type (it just remains a Query).

RE: [Dbo] retrieving and iterating on multiple raws results - Added by Vincenzo Romano 2 months ago

Despite I am compiling with -lwtdbo -lwtdbopostgres -lwt -lwthttp flags, I get this linker error:

/usr/bin/ld: /tmp/ccxNUNeD.o: in function `setEntryPoints()':
init.cpp:(.text+0x259): undefined reference to `Wt::Dbo::Query<Wt::Dbo::ptr<ConfigEntry>, Wt::Dbo::DynamicBinding> Wt::Dbo::Session::query<Wt::Dbo::ptr<ConfigEntry> >(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)'
/usr/bin/ld: init.cpp:(.text+0x27a): undefined reference to `Wt::Dbo::Query<Wt::Dbo::ptr<ConfigEntry>, Wt::Dbo::DynamicBinding>::resultList() const'
/usr/bin/ld: init.cpp:(.text+0x282): undefined reference to `Wt::Dbo::collection<Wt::Dbo::ptr<ConfigEntry> >::size() const'
/usr/bin/ld: init.cpp:(.text+0x2a1): undefined reference to `Wt::Dbo::collection<Wt::Dbo::ptr<ConfigEntry> >::~collection()'
/usr/bin/ld: init.cpp:(.text+0x2ac): undefined reference to `Wt::Dbo::Query<Wt::Dbo::ptr<ConfigEntry>, Wt::Dbo::DynamicBinding>::resultList() const'
/usr/bin/ld: init.cpp:(.text+0x2b4): undefined reference to `Wt::Dbo::collection<Wt::Dbo::ptr<ConfigEntry> >::size() const'
/usr/bin/ld: init.cpp:(.text+0x2bf): undefined reference to `Wt::Dbo::collection<Wt::Dbo::ptr<ConfigEntry> >::~collection()'
/usr/bin/ld: init.cpp:(.text+0x311): undefined reference to `Wt::Dbo::Query<Wt::Dbo::ptr<ConfigEntry>, Wt::Dbo::DynamicBinding>::resultList() const'
/usr/bin/ld: init.cpp:(.text+0x31c): undefined reference to `Wt::Dbo::collection<Wt::Dbo::ptr<ConfigEntry> >::begin()'
/usr/bin/ld: init.cpp:(.text+0x324): undefined reference to `Wt::Dbo::collection<Wt::Dbo::ptr<ConfigEntry> >::~collection()'
/usr/bin/ld: init.cpp:(.text+0x34f): undefined reference to `Wt::Dbo::Query<Wt::Dbo::ptr<ConfigEntry>, Wt::Dbo::DynamicBinding>::resultList() const'
/usr/bin/ld: init.cpp:(.text+0x35a): undefined reference to `Wt::Dbo::collection<Wt::Dbo::ptr<ConfigEntry> >::end()'

What am I missing?

RE: [Dbo] retrieving and iterating on multiple raws results - Added by lm at 2 months ago

I'm not sure; make sure you have all the #include@s you need...maybe it has to do with the fact that you're not calling @Wt::Dbo::Session::mapClass which instantiates some of the templates?

It looks like any template based on Wt::Dbo::ptr<ConfigEntry> is not being instantiated (though it was clearly seen by the compiler in the earlier steps)...

Maybe try something simple like constructing a Wt::Dbo::ptr<ConfigEntry> in your main function and see if that compiles?

RE: [Dbo] retrieving and iterating on multiple raws results - Added by Vincenzo Romano 2 months ago

Thanks lm.
As the compilation itself is error free, I would exclude I am missing any
#include@ .
On the other side, yes, I haven't any Wt::Dbo::Session::mapClass in my code as my data source isn't a table but rather a set-returning-function.
Will recheck.

(1-12/12)