[odb-users] arbitrary query returning tuples

[Патрушев Данил Андреевич]

>The reason we do this is because you can either specify the entire
>query of just the WHERE condition and we need to distinguish these
>two cases (there is also the stored procedure call complication).
>The way we do it is by looking for the leading word like SELECT,
>EXEC, etc.


 But in case of plain #pragma db view (no sql text whatsoever) it is clear that the user has to provide a complete statement, not just a condition, isn't it?

>Also, I am actually not sure whether INSERT returns results in the
>same way as SELECT; that's the compilation I refer to with stored
>procedure calls -- for some databases the result is returned
>differently. In fact, you can probably try /*CALL*/ INSERT to
>see if this maybe already works

Does the /*CALL*/ trick apply to #pragma db view query()  views?

________________________________
От: Патрушев Данил Андреевич
Отправлено: 14 декабря 2023 г. 16:45:27
Кому: odb-users at codesynthesis.com
Тема: Re: [odb-users] arbitrary query returning tuples


  1.   But in case of plain #pragma db view (no sql text whatsoever) it is clear that the user has to provide a complete statement, not just a condition, isn't it?
  2.  Does the /*CALL*/ trick apply to #pragma db view query()  views?


________________________________
От: Boris Kolpackov <boris at codesynthesis.com>
Отправлено: 14 декабря 2023 г. 13:47:23
Кому: Патрушев Данил Андреевич
Копия: odb-users at codesynthesis.com
Тема: Re: [odb-users] arbitrary query returning tuples

Патрушев Данил Андреевич <d.patrushev at prosoftsystems.ru> writes:

> I spent some time digging in the sources and realized libodb-pgsql
> does some preprocessing even for this kind of super low-level view,
> adding "WHERE" if the text of the query doesn't meet certain criteria,
> which results in invalid SQL. That was news to me. So, the question
> is:  is that a feature?

The reason we do this is because you can either specify the entire
query of just the WHERE condition and we need to distinguish these
two cases (there is also the stored procedure call complication).
The way we do it is by looking for the leading word like SELECT,
EXEC, etc.


> If it is, is there still a way to execute an arbitrary query
> returning tuples (for any database backend, not just libodb-pgsql)?

There is a note in odb/relational/processor.cxx:1183:

//@@ We need to recognize database-specific list of prefixes. For
//   example, PG has WITH. Alternatively (or in addition) we could
//   do the same comment trick (e.g., /*SELECT*/ to treat it as a
//   SELECT-like queiry).

So /*SELECT*/ could be one way to support this.

Also, I am actually not sure whether INSERT returns results in the
same way as SELECT; that's the compilation I refer to with stored
procedure calls -- for some databases the result is returned
differently. In fact, you can probably try /*CALL*/ INSERT to
see if this maybe already works.