mORMot Open Source

ericgarcia

Member

Registered: 2015-06-11

Posts: 9

Exception ESQLDBOracle : string buffer too small

hello,

1/ I am trying to connect to oracle and call a procedure with 2 parameters.
IN and OUT.  if the parameter out is a string or rawutf I get the following message when call the procedure at the statement ExecutePrepared.
When it's integer It works well.
I debug it on the PL/SQL side and the procedure is correctly excuted, but seems that the VARCHAR2 is not allocated.

Exception classe ESQLDBOracle with message TSQLDBORACLEstatement error ORA-06502: character string buffer too small

here some sub-set of code

 PropsOracle := TSQLDBOracleConnectionProperties.Create('DB','SID','USER','PWD');
  call:='select actcode,actnom,actid from acteur where actcode like ''AK0%''';
  query := 'BEGIN MYPROCEDURE(?, ?); END;';
  stmt := PropsOracle.NewThreadSafeStatementPrepared(query, false);
  stmt.BindTextS(1, call, paramIn); // call var string;
  stmt.BindTextS(2, res, paramOut); // res var string
  stmt.ExecutePrepared;

for information my procedure is declared as:

PROCEDURE MYPROCEDURE(   call              IN       varchar2,    retn             OUT     varchar2);

2/ General question:
I am newbie in mormot and I see that there is a lot of different  possibilities to create servers.
My first wish is to start a project able to reply to REST request from http.
so something like a console  server multi-thread based on the http.sys
in detail :
- start a basic authentification
- create a session oracle
- call procedure and get results (varchar, integer, no cursor)
. select , ... statements
.
.
. then close my session.

for the first time I don't want to use ORM possibilities. I will see later.
so I am looking for some piece of code or example, where I can  rely on .
anyway I love very much the Mormot concept.

thanks for your help

eric g

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: Exception ESQLDBOracle : string buffer too small

Use a shared PropsOracle := TSQLDBOracleConnectionProperties property, and call PropsOracle.NewThreadSafeStatementPrepared() within your methods services.
Do not close your sessions, but let the TSQLDBOracleConnectionProperties maintain the connections. There will be a connection per thread of the http.sys thread-pool (up to 32 threads by default), then all will work very fast.

Then you could either use mehtod-based services or interface-based services.
For delphi clients, interface-based services are preferred.
For AJAX or non delphi clients, maybe method-based services allows more customization, but even interface-based services are just fine.

I would go with interface-based services, in sicShared mode, and a TInterfacedObject implementation class which would be holding the TSQLDBOracleConnectionProperties instance.

See http://synopse.info/files/html/Synopse% … DE_TITL_63

ericgarcia

Member

Registered: 2015-06-11

Posts: 9

Re: Exception ESQLDBOracle : string buffer too small

please explain me more in detail what do you think about. thanks
Use a shared PropsOracle := TSQLDBOracleConnectionProperties property, and call PropsOracle.NewThreadSafeStatementPrepared() within your methods services.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: Exception ESQLDBOracle : string buffer too small

See https://github.com/synopse/mORMot/tree/ … 20services

ericgarcia

Member

Registered: 2015-06-11

Posts: 9

Re: Exception ESQLDBOracle : string buffer too small

After debugging the ProjectServer16http
finally i get errors when it is trying to execute the function  GetTableNames: TRawUTF8DynArray;
my  tnsnames.ora is as below
lsp45=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 54.85.217.118)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DFCASB)
    )
  )

the problem occured in SyndbOracle line 1845 at the statement:
Check(self,nil,TypeByName(fEnv,fError,fContext,Pointer(type_owner_name),length(type_owner_name),     Pointer(type_NymberListName),length(type_NymberListName),nil,0,OCI_DURATION_SESSION,OCI_TYPEGET_HEADER,     fType_numList),fError);

20150712 20384047  ! EXC   ESQLDBOracle {"Message":"TSQLDBOracleLib error: OCI-2
1560: argument 10 Null, non valide ou hors limites"} at 00705931  stack trace AP
I 005C0571 005C05A5 00007FFDC7299AB2 00007FFDC72982D3 00007FFDC729762D 00007FFDC
45A606C 00410526 004105A8 007083EB 006CA6E0 006C9AAA 006CF347 00739E63 006B3A88
006B3B55 00515AE5 005A7FC5 00742632 00007FFDC54A16AD 00007FFDC72AE954
571 005C05A5 00007FFDC7299AB2 00007FFDC72982D3 00007FFDC729762D 00007FFDC
45A606C 00410526 004105A8 007083EB 006CA6E0 006C9AAA 006CF347 00739E63 006B3A88
006B3B55 00515AE5 005A7FC5 00742632 00007FFDC54A16AD 00007FFDC72AE954

thanks for your help

Last edited by ericgarcia (2015-07-13 23:12:09)