SQLExpress - Xb2.NET     ot4xb  
ot4xb.public
Postgress PGexecparam
Thread Starter: Sander Elias Started: 8/15/2006 11:36 AM UTC
Replies: 5
8/15/2006 11:36 AM UTC
Postgress PGexecparam
Reply Quote

Sander Elias

Hi,

I'm working together with Phil, on a extension of the xbase Postgress
library, to support binaries. now we need to call this function:
from the Postgress docs:
------------------------------------------------------------------------------------------------------------------
PQexecParams Submits a command to the server and waits for the result, with the
ability to pass parameters separately from the SQL command text.  

PGresult *PQexecParams(PGconn *conn,
                      const char *command,
                      int nParams,
                      const Oid *paramTypes,
                      const char * const *paramValues,
                      const int *paramLengths,
                      const int *paramFormats,
                      int resultFormat);
PQexecParams is like PQexec, but offers additional functionality:
parameter values can be specified separately from the command string
proper, and query results can be requested in either text or binary
format. PQexecParams is supported only in protocol 3.0 and later
connections; it will fail when using protocol 2.0.

If parameters are used, they are referred to in the command string as
$1, $2, etc. nParams is the number of parameters supplied; it is the
length of the arrays paramTypes[], paramValues[], paramLengths[], and
paramFormats[]. (The array pointers may be NULL when nParams is zero.)
paramTypes[] specifies, by OID, the data types to be assigned to the
parameter symbols. If paramTypes is NULL, or any particular element in
the array is zero, the server assigns a data type to the parameter
symbol in the same way it would do for an untyped literal string.
paramValues[] specifies the actual values of the parameters. A null
pointer in this array means the corresponding parameter is null;
otherwise the pointer points to a zero-terminated text string (for
text format) or binary data in the format expected by the server (for
binary format). paramLengths[] specifies the actual data lengths of
binary-format parameters. It is ignored for null parameters and
text-format parameters. The array pointer may be null when there are
no binary parameters. paramFormats[] specifies whether parameters are
text (put a zero in the array) or binary (put a one in the array). If
the array pointer is null then all parameters are presumed to be text.
resultFormat is zero to obtain results in text format, or one to
obtain results in binary format. (There is not currently a provision
to obtain different result columns in different formats, although that
is possible in the underlying protocol.)
--------------------------------------------------------------------------------------------------------

How can I do this using xb4ot? (or bap2)
I'm hoping for an simple and easy to implement solution!

with kind regards
Sander Elias
Regards
Sander Elias

-------------------------------------------------------------
xbHCL (http://www.xbHCL.com) the xbase HTML command layer
PBIH  (http://pbih.eu)       Polar Bear International Hosting
-------------------------------------------------------------
also a member off the XXP (http://www.xxp.nl)
8/16/2006 2:29 PM UTC
Re: Postgress PGexecparam
Reply Quote

Hector Pezoa

Hi Sander

Pablo, it returned soon,  it answered its questions
this outside the city

Best Regard
Hector Pezoa

Excuseme Phil, for send to private email
8/15/2006 3:51 PM UTC
Re: Postgress PGexecparam
Reply Quote

Phil Ide

Sander,

Hi,

Hi <g>

I'm working together with Phil, on a extension of the xbase Postgress
library, to support binaries. now we need to call this function:
from the Postgress docs:
PQexecParams  

Submits a command to the server and waits for the result, with the
ability to pass parameters separately from the SQL command text.  
PGresult *PQexecParams(PGconn *conn,
                      const char *command,
                      int nParams,
                      const Oid *paramTypes,
                      const char * const *paramValues,
                      const int *paramLengths,
                      const int *paramFormats,
                      int resultFormat);

Just to kick this discussion off, here's how I did it in my NNTP server:

aPValues := BAInit(3)
BaStruct( aPValues, @cMsgID )
BaStruct( aPValues, @cHead )
BaStruct( aPValues, @cBody )
cPValues := BAAccess( aPValues )

cSql := "insert into "+cGrp+" (msgid, headers, body ) values ($1, $2, $3);"
if ::oPG:execParams(cSql, 3, 0, cPValues, 0, 0, 0)
  o := ::oPG:result
  if o:resultStatus() == PGRES_COMMAND_OK
     lPosted := TRUE
  endif
  o:destroy()
endif

BaExtract( aPValues, cPValues )
BaExtract( aPValues )
BaExtract( aPValues )

Note from the above that the connection handle is supplied by the method
execParams(), which otherwise reflects the format of PQexecParams(). Note
also that since this is adding a record to save an inbound (submitted)
newsgroup message, it is pre-known that the msgid, headers and body will
contain textual data (ng messages and email messages cannot contain bytes
with an ascii value greater than 127 and any non-printable characters should
have been converted through MIME or some other encoding (uuencode, base64
etc.).

In fact, the BAP structure is not strictly necessary, since the data it
contains can simply be concatenated as STRZ strings. The reason I chose to
use BAP was to ensure that the memory pointed to by the structure was locked
and later freed as soon as possible.

A bigger problem is determining the PG data types of the columns, which
either requires pre-knowledge of column types (possibly from a database,
possibly from a cache) else a dynamic query to the database information
schema is required to get the info.

Now that we've cleared that up, any help would be appreciated - our goals
are twofold:

1. Make the call to execParams as simple and convenient as possible
2. Incur as small a penalty as possible when making this call (i.e. quick)

Regards,
-- Phil Ide

-----------------------------------------------------------------
xbHCL (http://www.xbHCL.com)     Xbase++ HTML command layer
PBIH  (http://www.pbih.eu)       Polar Bear International Hosting
-----------------------------------------------------------------
Xbase++ FAQ, Libraries and Sources:
http://www.idep.org.uk/xbase

Some days, it just doesn't pay to gnaw through those leather straps.
8/22/2006 7:01 PM UTC
Re: Postgress PGexecparam
Reply Quote

Pablo Botella

Hi Sander,

First sorry for the late response (was leave so many things unatended this
days).

I will try to explain the issues step by step

First issue to solve is how to pass an array of integers.
In pure Xbase++ probably you can do something like this

cSizes := ""
aEval( aValues , {|e,n| cSizes += L2Bin( Len( e ) ) } )
and pass cSizes to the called function

With ot4xb you can get some advantadges using nFpCall() instead of
DllCall() or DllExecuteCall(). One of this advantadges is that you can
provide an Xbase++ array of numeric values and nFpCall() will pass it
as an array of integers and, if is provided by reference, update the array
elements after the call.

aSizes := Array( Len( aValues) )
aEval( aValues , {|e,n| aSizes[n] := Len( e ) } )
result := nFpCall( fpPQexecParams, ....some_params.., aSizes, ...more_params...)

The second issue is how to pass an array of strings. If you see the Phil post
you can see how do it with bap or bap2.

In ot4xb as you can provide an array of numerics to nFpCall()  can store the pointers into an Xbase+ array and just pass it

apValues := Array( Len( aValues) )
aSizes := Array( Len( aValues) )
aEval( aValues , {|e,n,nSize| apValues[n] := _xgrab(e,@nSize) , aSizes[n] := nSize } )
result := nFpCall( fpPQexecParams, ....someParams.., apValues,aSizes, ...moreParams... )
aEval( apValues , {|e,n|_xfree(e)} )
apValues := NIL

..... ummmh works, but still there are another way ....... specially if you are not confortable dealing with pointers
// ---------------------------------------------------------------------------
function Array2ppMarshall( aItems , aSizes)
local nItems,nSize,pMem,nShift
if( ValType( aItems ) != "A" ) ; return 0 ; end
nItems := Len( aItems )
if( nItems < 1 ) ;  return 0 ; end
aSizes := Array(nItems)
nSize  := (5 * nItems) + 1
aEval( aItems , {|_e,_n| nSize += (aSizes[_n] := iif(_e == NIL,0,Len(_e)))})
pMem := _xgrab(nSize)
nShift := 4 * nItems
PokeDWord( pMem , @nShift , aSizes )
aEval( aItems , {|_e,_n| PokeDWord(pMem,4*(_n-1),pMem+nShift),;
                        PokeStr(pMem,@nShift,iif( _e == NIL,"",_e)),;
                        nShift++  } )
return pMem
// ---------------------------------------------------------------------------
I will include a C version of the Array2ppMarshall() function in next
release of ot4xb that probably will be a litle faster but you can use this in Xbase++ in the
meantime.
Syntax:
Array2ppMarshall( aItems , [ aSizes ] ) -> (LPSTR * ) pItems

The strings pointed from the pItems elements are contained inside the same
memory block so , you just need to release 1 pointer with _xfree(pItems) when no longer
needed.

aSizes := NIL
ppValues := Array2ppMarshall( aValues,@aSizes)
result := nFpCall( fpPQexecParams, ....someParams.., ppValues,aSizes, ...moreParams... )
_xfree( ppValues )
ppValues := NIL

Hope this can help you a bit, the rest of the implementation is specifically related to Postgress

Regards,
Pablo Botella
12/20/2006 7:33 AM UTC
Re: Postgress PGexecparam
Reply Quote

Sander Elias

Hi Pablo,

I finally got some time to work this out, the  Array2ppMarshall is
working like a charm. hat to figure out quite some other PG related
stuff, but I got it working anyway!
Again, thanks for you support!

Regards
Sander Elias

-------------------------------------------------------------
xbHCL (http://www.xbHCL.com) the xbase HTML command layer
PBIH  (http://pbih.eu)       Polar Bear International Hosting
-------------------------------------------------------------
also a member off the XXP (http://www.xxp.nl)
8/22/2006 7:16 PM UTC
Re: Postgress PGexecparam
Reply Quote

Pablo Botella

---------------------------------------------------------------------------
function Array2ppMarshall( aItems , aSizes)
local nItems,nSize,pMem,nShift
if( ValType( aItems ) != "A" ) ; return 0 ; end
nItems := Len( aItems )
if( nItems < 1 ) ;  return 0 ; end
aSizes := Array(nItems)
nSize  := (5 * nItems) + 1
aEval( aItems , {|_e,_n| nSize += (aSizes[_n] := iif(_e == NIL,0,Len(_e)))})
pMem := _xgrab(nSize)
nShift := 4 * nItems
aEval( aItems , {|_e,_n| PokeDWord(pMem,4*(_n-1),pMem+nShift),;
                        PokeStr(pMem,@nShift,iif( _e == NIL,"",_e)),;
                        nShift++  } )
return pMem
// ---------------------------------------------------------------------------