Prepared_Statement :(

Prepared_Statement :(

[eduardsapotski]

Trying insert data into PostgreSql:

   function Insert_Trade(Trade : Trade_Type) return Boolean is 
      
      Conn   : Database_Connection := Build_Database_Connection;
      PS     : Prepared_Statement  := Prepare(Query => "INSERT INTO exmo.trades(trade_id, pair, type, price, quantity, amount, date) VALUES (?, ?, ?, ?, ?, ?, ?)");             
      Params : SQL_Parameters(1..7);
      FC     : Forward_Cursor;
      
      Result : Boolean := False;
         
   begin

      Params(1) := "+"(Trade.Trade_ID);
      Params(2) := "+"(Trade.Pair);
      Params(3) := "+"(Trade.Direction);
      Params(4) := "+"(Trade.Price'Img);
      Params(5) := "+"(Trade.Quantity'Img);
      Params(6) := "+"(Trade.Amount'Img);
      Params(7) := "+"(Trade.Date);
    
      Execute (Conn, PS, Params);
        
      Commit_Or_Rollback(Conn);
      
      Put_Line(Conn.Last_Error_Message);
      
      return Result;
      
   end;

Result:

ERROR:  syntax error at or near ","
LINE 1: ...ir, type, price, quantity, amount, date) VALUES (?, ?, ?, ?,...
                                                             ^

What's wrong? :( 
Can anyone have a working example?

Re: Prepared_Statement :(

[eduardsapotski]

Swears on a comma where the first question mark.

Re: Prepared_Statement :(

[Dmitry A. Kazakov]

On 2018-11-24 11:45, eduardsapotski@gmail.com wrote:
> Trying insert data into PostgreSql:
> 
>     function Insert_Trade(Trade : Trade_Type) return Boolean is
>        
>        Conn   : Database_Connection := Build_Database_Connection;
>        PS     : Prepared_Statement  := Prepare(Query => "INSERT INTO exmo.trades(trade_id, pair, type, price, quantity, amount, date) VALUES (?, ?, ?, ?, ?, ?, ?)");
>        Params : SQL_Parameters(1..7);
>        FC     : Forward_Cursor;
>        
>        Result : Boolean := False;
>           
>     begin
> 
>        Params(1) := "+"(Trade.Trade_ID);
>        Params(2) := "+"(Trade.Pair);
>        Params(3) := "+"(Trade.Direction);
>        Params(4) := "+"(Trade.Price'Img);
>        Params(5) := "+"(Trade.Quantity'Img);
>        Params(6) := "+"(Trade.Amount'Img);
>        Params(7) := "+"(Trade.Date);
>      
>        Execute (Conn, PS, Params);
>          
>        Commit_Or_Rollback(Conn);
>        
>        Put_Line(Conn.Last_Error_Message);
>        
>        return Result;
>        
>     end;
> 
> Result:
> 
> ERROR:  syntax error at or near ","
> LINE 1: ...ir, type, price, quantity, amount, date) VALUES (?, ?, ?, ?,...
>                                                               ^
> What's wrong? :(

First. You don't say what library/binging/RDBMS client you are using, 
which version, what platform. That is certainly wrong.

Then, "date" is a reserved keyword in SQL, which has tons of. You could 
start with qualifying all column names in the statement.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Prepared_Statement :(

[eduardsapotski]

суббота, 24 ноября 2018 г., 14:05:02 UTC+3 пользователь Dmitry A. Kazakov написал:
> On 2018-11-24 11:45, eduardsapotski@gmail.com wrote:
> > Trying insert data into PostgreSql:
> > 
> >     function Insert_Trade(Trade : Trade_Type) return Boolean is
> >        
> >        Conn   : Database_Connection := Build_Database_Connection;
> >        PS     : Prepared_Statement  := Prepare(Query => "INSERT INTO exmo.trades(trade_id, pair, type, price, quantity, amount, date) VALUES (?, ?, ?, ?, ?, ?, ?)");
> >        Params : SQL_Parameters(1..7);
> >        FC     : Forward_Cursor;
> >        
> >        Result : Boolean := False;
> >           
> >     begin
> > 
> >        Params(1) := "+"(Trade.Trade_ID);
> >        Params(2) := "+"(Trade.Pair);
> >        Params(3) := "+"(Trade.Direction);
> >        Params(4) := "+"(Trade.Price'Img);
> >        Params(5) := "+"(Trade.Quantity'Img);
> >        Params(6) := "+"(Trade.Amount'Img);
> >        Params(7) := "+"(Trade.Date);
> >      
> >        Execute (Conn, PS, Params);
> >          
> >        Commit_Or_Rollback(Conn);
> >        
> >        Put_Line(Conn.Last_Error_Message);
> >        
> >        return Result;
> >        
> >     end;
> > 
> > Result:
> > 
> > ERROR:  syntax error at or near ","
> > LINE 1: ...ir, type, price, quantity, amount, date) VALUES (?, ?, ?, ?,...
> >                                                               ^
> > What's wrong? :(
> 
> First. You don't say what library/binging/RDBMS client you are using, 
> which version, what platform. That is certainly wrong.
> 
> Then, "date" is a reserved keyword in SQL, which has tons of. You could 
> start with qualifying all column names in the statement.
> 
> -- 
> Regards,
> Dmitry A. Kazakov
> http://www.dmitry-kazakov.de

with GNATCOLL.SQL_Impl;       use GNATCOLL.SQL_Impl;
with GNATCOLL.SQL.Exec;       use GNATCOLL.SQL.Exec;
with GNATCOLL.SQL.Postgres;   use GNATCOLL.SQL;

If I just write:

Execute(Conn, "INSERT INTO exmo.trades(trade_id, pair, type, price, quantity, amount, date) VALUES (100500, 'pair', 'type', 1.0, 1.0, 1.0, 100500)");

Everything is working!
I think the problem is in the parameters.

Re: Prepared_Statement :(

[briot.emmanuel]

> > >        Conn   : Database_Connection := Build_Database_Connection;


You only show part of your code, this always makes things difficult.
I don't know how you build the connection, but my guess is that you actually build a sqlite connection, not postgresql.

"?" is how sqlite represents parameters. Postgresql uses $1, $2,... So the query is not working.

https://www.postgresql.org/docs/10/sql-prepare.html

Re: Prepared_Statement :(

[Simon Wright]

briot.emmanuel@gmail.com writes:

>> > >        Conn   : Database_Connection := Build_Database_Connection;
>
> You only show part of your code, this always makes things difficult.
> I don't know how you build the connection, but my guess is that you
> actually build a sqlite connection, not postgresql.
>
> "?" is how sqlite represents parameters. Postgresql uses $1, $2,... So
> the query is not working.
>
> https://www.postgresql.org/docs/10/sql-prepare.html

But that doesn't explain a compiler error report pointing at the middle
of a string!

OH! OH! OH! it's not a compiler error at all! (or if it is, it's when
the DBC compiles the prepared statement).

OP, it helps if you post the exact error message in context.

Re: Prepared_Statement :(

[Dmitry A. Kazakov]

On 2018-11-24 18:56, briot.emmanuel@gmail.com wrote:

> "?" is how sqlite represents parameters. Postgresql uses $1, $2,... So the query is not working.

I guess that SQLite borrowed '?' from ODBC.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Prepared_Statement :(

[eduardsapotski]

> "?" is how sqlite represents parameters. Postgresql uses $1, $2,... So the 
> https://www.postgresql.org/docs/10/sql-prepare.html
Thenks! It really works!
I did as in JDBC. It uses exactly '?'.

Re: Prepared_Statement :(

[Dennis Lee Bieber]

On Sun, 25 Nov 2018 22:52:49 -0800 (PST), eduardsapotski@gmail.com
declaimed the following:

>> "?" is how sqlite represents parameters. Postgresql uses $1, $2,... So the 
>> https://www.postgresql.org/docs/10/sql-prepare.html
>Thenks! It really works!
>I did as in JDBC. It uses exactly '?'.

	JDBC is its own database interface. Parameter identifiers are specific
to the interface package in use. For a comparison, Python DB-API compatible
database adapters can use any of five parameter:
		?		(replacement in order arguments provided)
		:# 		(numbered position)
		:name	(actual parameter name)
		%s		(C printf format codes, %s in Python works for anything)
		%(name)s	(as above, but with parameter name to select)
One has to read the documentation for the specific adapter to know which to
use (or put in a lot of overhead code to query the adapter for the
"paramstyle" and then algorithmically generate statements using the proper
style).


-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
	wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/ 

Re: Prepared_Statement :(

[Dmitry A. Kazakov]

On 2018-11-27 01:27, Dennis Lee Bieber wrote:
> On Sun, 25 Nov 2018 22:52:49 -0800 (PST), eduardsapotski@gmail.com
> declaimed the following:
> 
>>> "?" is how sqlite represents parameters. Postgresql uses $1, $2,... So the
>>> https://www.postgresql.org/docs/10/sql-prepare.html
>> Thenks! It really works!
>> I did as in JDBC. It uses exactly '?'.
> 
> 	JDBC is its own database interface. Parameter identifiers are specific
> to the interface package in use. For a comparison, Python DB-API compatible
> database adapters can use any of five parameter:
> 		?		(replacement in order arguments provided)
> 		:# 		(numbered position)
> 		:name	(actual parameter name)
> 		%s		(C printf format codes, %s in Python works for anything)
> 		%(name)s	(as above, but with parameter name to select)
> One has to read the documentation for the specific adapter to know which to
> use (or put in a lot of overhead code to query the adapter for the
> "paramstyle" and then algorithmically generate statements using the proper
> style).

Ergo, if you can, never use any DB-specific bindings, use ODBC. It is 
not portable across RDBMs either (SQL is very not Ada (:-)), but at 
least it would give you far less headache...

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Prepared_Statement :(

[briot.emmanuel]

> Ergo, if you can, never use any DB-specific bindings, use ODBC. It is 
> not portable across RDBMs either (SQL is very not Ada (:-)), but at 
> least it would give you far less headache...

Alternatively, use GNATCOLL and let it do the formatting for you. The OP
query could be written as :

  with Database;  --  generated via gnatcoll_db2ada
  declare
      PS     : Prepared_Statement  := Prepare(
         SQL_Insert
             ((Database.Exmo.Trades.Trade_Id = Integer_Param (1))
              & (Database.Exmo.Trades.Pair = Integer_Param (2))
              & ...));
  begin
      --  actual execution remains the same
  end;

Then whether you use sqlite, postgresql, or add a new backend to gnatcoll, the
code remains valid.

All of these are valid choices, and depends on your workflow

Re: Prepared_Statement :(

[eduardsapotski]

GNAT 2018 PostgreSQL 9.3 

Re: Prepared_Statement :(

[eduardsapotski]

Dmitry, if I wanted to get a universal solution, I would never use Ada-language.
Over year ago I wrote trading terminal on Java. Uses JDBC and can easily change db-driver.  
And application can be easily transferred to any server.
But what's the problem?
Year ago, trading was conducted on ten currency pairs, today more hundred pairs!
Java slows down and eats resources.
Server is running at the limit.
I am not so rich to buy Oracle SPARC M8.
Ada significantly reduces server load.
For this reason, I am not going to use anything other but PostgreSQL. 
PostgreSQL very fast on insert operations. Even much faster than Oracle, other things being equal.

Re: Prepared_Statement :(

[Dmitry A. Kazakov]

On 2018-11-27 10:58, eduardsapotski@gmail.com wrote:
> Dmitry, if I wanted to get a universal solution, I would never use Ada-language.

I doubt universal solutions, especially if not in Ada! (:-))

> Over year ago I wrote trading terminal on Java. Uses JDBC and can easily change db-driver.

Surely they cannot. RDBMS are way too different for this to work. Any 
"universal" solution must include a DB adapter to provide a reasonable 
application interface instead of the SQL mess (prepared or uncooked (:-)).

> And application can be easily transferred to any server.
> But what's the problem?
> Year ago, trading was conducted on ten currency pairs, today more hundred pairs!
> Java slows down and eats resources.
> Server is running at the limit.
> I am not so rich to buy Oracle SPARC M8.
> Ada significantly reduces server load.

Though Ada is far faster than Java it alone does not guarantee 
performance if the DB is the bottleneck.

Ada allows very efficient custom persistent storage implementations when 
RDBMS fails. Here is a small comparison SQLite vs B-trees in Ada:

http://ada-programming.blogspot.com/2014/09/ada-direct-io-b-tree-vs-sqlite.html

 > For this reason, I am not going to use anything other but PostgreSQL.
 > PostgreSQL very fast on insert operations. Even much faster than 
Oracle, other things being equal.

I cannot tell if PostgreSQL is better than Oracle or MySQL or whatever. 
One should really benchmark them for the specific case to know.

What I know is that the difference cannot be any dramatic. If one of 
them stops working all others probably will, and conversely. So if you 
have a real DB problem you possibly must say goodbye to all relational 
SQL-based DBs and look for a better custom algorithmic solution.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Prepared_Statement :(

[Björn Lundin]

On 2018-11-27 19:00, Dmitry A. Kazakov wrote:
> I cannot tell if PostgreSQL is better than Oracle or MySQL or whatever.
> One should really benchmark them for the specific case to know.
> 
> What I know is that the difference cannot be any dramatic. 

Oh yes it can.
Perhaps not for inserts, but for selects. (hm actually for inserts as
well, in old mysqls using table locks. Scales bad with many writers)

Especially if you are not alone, ie, there are other
writers when you read.
The RDBM's internal strategy for looking can be a show stopper on one DB
and great on another.

>If one of
> them stops working all others probably will, and conversely. So if you
> have a real DB problem you possibly must say goodbye to all relational
> SQL-based DBs and look for a better custom algorithmic solution.

Possibly, but not likely. Unless your problem was not suited for an RDBM
from the beginning


-- 
--
Björn

Re: Prepared_Statement :(

[eduardsapotski]

Our world is neither white nor black. Negative feedback always works. 
I understand perfectly well that in order to get the maximum performance we need to abandon RDBMS altogether. But is it simple? How much time will we spend on the implementation of the mechanisms already implemented in the RDBMS? 
For an example: 
Oracle has packages for working with network UTL_HTTP, there is a package for working with JSON APEX_JSON. Also in Oracle APEX there are many tools for building a user interface with charts, filters, etc.. 
I can solve my problems on Oracle for 2-3 weeks. But the solution itself will work very slowly! To speed it up, need to use very large computing resources!
On the other hand, everything can be implemented in the bare C language. Everything will work very fast! But can I still do that? No. I'll have to program for a very long time! The situation will change faster than I can adapt to it. 
That's the problem... Need to find a middle ground.