--- In Firebird-Architect@yahoogroups.com, Alex Peshkov <peshkoff@...>
wrote:

>
> On Thursday 31 January 2008 17:17, zedalaye wrote:
> > > Can you provide a list of functions you suggest?
> >
> > At least, It would be very handy to have a function that returns the
> > contents of a column (given a statement handle and a column index) in
> > a char * buffer, that do all the complex stuff of converting data,
> > blobs from firebird datatypes into "standard strings".
>
> What should be done for non-character data? I can imagine use of

sprintf() for

> numerics, but please explain what to do with non-text BLOBs?

In my case (a full TEXT indexing engine) I don't care non text data
that can't be converted to usefull text :
- number can be formatted,
- binary blobs providing a text filter
- text blobs
- text fields with non OCTET character set (I think of GUID in
OCTET(16) format)

All the others are useless and can be ignored by returning NULL.

> Please provide full format of desired function call.

Considering SphinxSearch engine, here is the full interface to PgSQL :

Sphinx.h

#if USE_PGSQL
/// PgSQL specific source params
struct CSphSourceParams_PgSQL : CSphSourceParams_SQL
{
CSphString m_sClientEncoding;
CSphSourceParams_PgSQL ();
};


/// PgSQL source implementation
/// multi-field plain-text documents fetched from given query
struct CSphSource_PgSQL : CSphSource_SQL
{
CSphSource_PgSQL ( const char * sName );
bool Setup ( const CSphSourceParams_PgSQL & pParams );

protected:
PGresult * m_pPgResult; ///< postgresql execution restult context
PGconn * m_tPgDriver; ///< postgresql connection context

int m_iPgRows; ///< how much rows last step returned
int m_iPgRow; ///< current row (0 based, as in PQgetvalue)

CSphString m_sPgClientEncoding;

protected:
virtual void SqlDismissResult ();
virtual bool SqlQuery ( const char * sQuery );
virtual bool SqlIsError ();
virtual const char * SqlError ();
virtual bool SqlConnect ();
virtual void SqlDisconnect ();
virtual int SqlNumFields();
virtual bool SqlFetchRow();
virtual const char * SqlColumn ( int iIndex );
virtual const char * SqlFieldName ( int iIndex );
};
#endif // USE_PGSQL

Sphinx.cpp

/////////////////////////////////////////////////////////////////////////////
// PGSQL SOURCE
/////////////////////////////////////////////////////////////////////////////

#if USE_PGSQL

CSphSourceParams_PgSQL::CSphSourceParams_PgSQL ()
{
m_iRangeStep = 1024;
m_iPort = 5432;
}


CSphSource_PgSQL::CSphSource_PgSQL ( const char * sName )
: CSphSource_SQL ( sName )
, m_pPgResult ( NULL )
, m_iPgRows ( 0 )
, m_iPgRow ( 0 )
{
}


bool CSphSource_PgSQL::SqlIsError ()
{
return ( m_iPgRow<m_iPgRows ); // if we're over, it's just last row
}


const char * CSphSource_PgSQL::SqlError ()
{
return PQerrorMessage ( m_tPgDriver );
}


bool CSphSource_PgSQL::Setup ( const CSphSourceParams_PgSQL & tParams )
{
// checks
CSphSource_SQL::Setup ( tParams );

m_sPgClientEncoding = tParams.m_sClientEncoding;
if ( !m_sPgClientEncoding.cstr() )
m_sPgClientEncoding = "";

// build and store DSN for error reporting
char sBuf [ 1024 ];
snprintf ( sBuf, sizeof(sBuf), "pgsql%s", m_sSqlDSN.cstr()+3 );
m_sSqlDSN = sBuf;

return true;
}


bool CSphSource_PgSQL::SqlConnect ()
{
char sPort[64];
snprintf ( sPort, sizeof(sPort), "%d", m_tParams.m_iPort );
m_tPgDriver = PQsetdbLogin ( m_tParams.m_sHost.cstr(), sPort, NULL, NULL,
m_tParams.m_sDB.cstr(), m_tParams.m_sUser.cstr(),
m_tParams.m_sPass.cstr() );

if ( PQstatus ( m_tPgDriver )==CONNECTION_BAD )
return false;

// set client encoding
if ( !m_sPgClientEncoding.IsEmpty() )
if ( -1==PQsetClientEncoding ( m_tPgDriver,
m_sPgClientEncoding.cstr() ) )
{
SqlDisconnect ();
return false;
}

return true;
}


void CSphSource_PgSQL::SqlDisconnect ()
{
PQfinish ( m_tPgDriver );
}


bool CSphSource_PgSQL::SqlQuery ( const char * sQuery )
{
m_iPgRow = -1;
m_iPgRows = 0;

m_pPgResult = PQexec ( m_tPgDriver, sQuery );

ExecStatusType eRes = PQresultStatus ( m_pPgResult );
if ( ( eRes!=PGRES_COMMAND_OK ) && ( eRes!=PGRES_TUPLES_OK ) )
return false;

m_iPgRows = PQntuples ( m_pPgResult );
return true;
}


void CSphSource_PgSQL::SqlDismissResult ()
{
if ( !m_pPgResult )
return;

PQclear ( m_pPgResult );
m_pPgResult = NULL;
}


int CSphSource_PgSQL::SqlNumFields ()
{
if ( !m_pPgResult )
return -1;

return PQnfields ( m_pPgResult );
}


const char * CSphSource_PgSQL::SqlColumn ( int iIndex )
{
if ( !m_pPgResult )
return NULL;

return PQgetvalue ( m_pPgResult, m_iPgRow, iIndex );
}


const char * CSphSource_PgSQL::SqlFieldName ( int iIndex )
{
if ( !m_pPgResult )
return NULL;

return PQfname ( m_pPgResult, iIndex );
}


bool CSphSource_PgSQL::SqlFetchRow ()
{
if ( !m_pPgResult )
return false;
return ( ++m_iPgRow<m_iPgRows );
}

#endif // USE_PGSQL

What I'm looking for is a way to have such a simple API to get the
contents of a column :

const char * CSphSource_PgSQL::SqlColumn ( int iIndex )
{
if ( !m_pPgResult )
return NULL;

return PQgetvalue ( m_pPgResult, m_iPgRow, iIndex );
}

Its prototype in libpq-fe.h is :

extern char *PQgetvalue(const PGresult *res, int tup_num, int field_num);

But I don't know how relevant this prototype is regarding Firebird
client API.

For comparison, here's what I did for Firebird connector :

Sphinx.h

#if USE_FIREBIRD
/// Firebird specific source params
struct CSphSourceParams_Firebird : CSphSourceParams_SQL
{
CSphString m_sClientEncoding;
CSphSourceParams_Firebird ();
};


/// Firebird source implementation
/// multi-field plain-text documents fetched from given query
struct CSphSource_Firebird : CSphSource_SQL
{
CSphSource_Firebird ( const char * sName );
bool Setup ( const CSphSourceParams_Firebird & pParams );

protected:
isc_db_handle m_tFbDbHandle; // the database handle
isc_tr_handle m_tFbTrHandle; // the transaction handle
isc_stmt_handle m_tFbStmtHandle; // the statement handle
XSQLDA * m_tFbSQLDA; // where the results lies
short m_iFbNumFields; // the number of returned fields
bool m_bFbEOF; // whether we have reached the end of the
resultset
ISC_STATUS m_tFbLastResult; // STATUS value of the last
API call
ISC_STATUS_ARRAY m_tFbLastStatus; // STATUS VECTOR of the last API call

CSphString m_sFbClientEncoding;

protected:
virtual void SqlDismissResult ();
virtual bool SqlQuery ( const char * sQuery );
virtual bool SqlIsError ();
virtual const char * SqlError ();
virtual bool SqlConnect ();
virtual void SqlDisconnect ();
virtual int SqlNumFields();
virtual bool SqlFetchRow();
virtual const char * SqlColumn ( int iIndex );
virtual const char * SqlFieldName ( int iIndex );
};
#endif // USE_FIREBIRD

Sphinx.cpp

/////////////////////////////////////////////////////////////////////////////
// FIREBIRD SOURCE
/////////////////////////////////////////////////////////////////////////////

#if USE_FIREBIRD

CSphSourceParams_Firebird::CSphSourceParams_Firebird ()
{
m_iRangeStep = 1024;
m_iPort = 3050;
}


CSphSource_Firebird::CSphSource_Firebird ( const char * sName )
: CSphSource_SQL ( sName )
, m_tFbDbHandle ( NULL )
, m_tFbTrHandle ( NULL )
, m_tFbStmtHandle ( NULL )
, m_tFbSQLDA ( NULL )
, m_bFbEOF ( false )
{

}


bool CSphSource_Firebird::SqlIsError ()
{
// All Firebird API calls returns 0 is success, something else when
an error occured
// except when isc_dsql_fetch returned 100L that means we reached the
end of the current
// resultset
if (m_bFbEOF)
return (m_tFbLastResult != 100L);
else
return (m_tFbLastResult != 0);
}


const char * CSphSource_Firebird::SqlError ()
{
return NULL;
}


bool CSphSource_Firebird::Setup ( const CSphSourceParams_Firebird &
tParams )
{
// checks
CSphSource_SQL::Setup ( tParams );

m_sFbClientEncoding = tParams.m_sClientEncoding;
if ( !m_sFbClientEncoding.cstr() )
m_sFbClientEncoding = "";

// build and store DSN for error reporting
char sBuf [ 1024 ];
snprintf ( sBuf, sizeof(sBuf), "firebird%s", m_sSqlDSN.cstr()+3 );
m_sSqlDSN = sBuf;

return true;
}


bool CSphSource_Firebird::SqlConnect ()
{
char sDbPath[1024];
char *dpb;
short dpb_length = 0;

snprintf ( sDbPath, sizeof(sDbPath), "%s/%d:%s",
m_tParams.m_sHost.cstr(), m_tParams.m_iPort, m_tParams.m_sDB.cstr() );

dpb = (char *)malloc(1);
*dpb = isc_dpb_version1;
dpb_length = 1;

isc_modify_dpb( &dpb, (short *) &dpb_length,
isc_dpb_user_name, m_tParams.m_sUser.cstr(), (short) strlen(
m_tParams.m_sUser.cstr() ) );
isc_modify_dpb( &dpb, (short *) &dpb_length,
isc_dpb_password, m_tParams.m_sPass.cstr(), (short) strlen(
m_tParams.m_sPass.cstr() ) );

if ( !m_sFbClientEncoding.IsEmpty() )
isc_modify_dpb( &dpb, (short *) &dpb_length,
isc_dpb_lc_ctype, m_sFbClientEncoding.cstr(), (short) strlen(
m_sFbClientEncoding.cstr() ) );

m_tFbLastResult = isc_attach_database( m_tFbLastStatus, 0, sDbPath,
&m_tFbDbHandle, dpb_length, dpb );

isc_free( dpb );

return m_tFbLastResult == 0;
}


void CSphSource_Firebird::SqlDisconnect ()
{
if ( m_tFbStmtHandle )
m_tFbLastResult = isc_dsql_free_statement( m_tFbLastStatus,
&m_tFbStmtHandle, DSQL_close );

if ( m_tFbTrHandle )
m_tFbLastResult = isc_commit_transaction( m_tFbLastStatus,
&m_tFbTrHandle );

if ( m_tFbDbHandle )
m_tFbLastResult = isc_detach_database( m_tFbLastStatus,
&m_tFbDbHandle );
}


bool CSphSource_Firebird::SqlQuery ( const char * sQuery )
{
int buffer[1024];
XSQLVAR *var;
short i;
short length, alignment, type, offset;

m_tFbSQLDA = (XSQLDA *) malloc(XSQLDA_LENGTH(1));
m_tFbSQLDA->sqln = 1;
m_tFbSQLDA->version = 1;

m_tFbLastResult = isc_start_transaction( m_tFbLastStatus,
&m_tFbTrHandle, 1, &m_tFbDbHandle, 0, NULL);
if (m_tFbLastResult != 0)
return false;

m_tFbLastResult = isc_dsql_allocate_statement( m_tFbLastStatus,
&m_tFbDbHandle, &m_tFbStmtHandle);
if (m_tFbLastResult != 0)
return false;

m_tFbLastResult = isc_dsql_prepare( m_tFbLastStatus, &m_tFbTrHandle,
&m_tFbStmtHandle,
0, sQuery, SQL_DIALECT_V6, m_tFbSQLDA);
if (m_tFbLastResult != 0)
return false;

m_tFbLastResult = isc_dsql_describe( m_tFbLastStatus,
&m_tFbStmtHandle, 1, m_tFbSQLDA);
if (m_tFbLastResult != 0)
return false;

m_iFbNumFields = m_tFbSQLDA->sqld;
if (m_tFbSQLDA->sqln < m_tFbSQLDA->sqld)
{
m_tFbSQLDA = (XSQLDA *) malloc(XSQLDA_LENGTH(m_iFbNumFields));
m_tFbSQLDA->sqln = m_iFbNumFields;
m_tFbSQLDA->version = 1;

m_tFbLastResult = isc_dsql_describe( m_tFbLastStatus,
&m_tFbStmtHandle, 1, m_tFbSQLDA);
if (m_tFbLastResult != 0)
return false;

m_iFbNumFields = m_tFbSQLDA->sqld;
}

for (var = m_tFbSQLDA->sqlvar, offset = 0, i = 0; i < m_iFbNumFields;
var++, i++)
{
length = alignment = var->sqllen;
type = var->sqltype & ~1;

if (type == SQL_TEXT)
alignment = 1;
else if (type == SQL_VARYING)
{
length += sizeof (short) + 1;
alignment = sizeof (short);
}
/* RISC machines are finicky about word alignment
** So the output buffer values must be placed on
** word boundaries where appropriate
*/
offset = (short)FB_ALIGN(offset, alignment);
var->sqldata = (char *) buffer + offset;
offset += length;
offset = FB_ALIGN(offset, sizeof (short));
var->sqlind = (short*) ((char *) buffer + offset);
offset += sizeof (short);
}

m_tFbLastResult = isc_dsql_execute( m_tFbLastStatus, &m_tFbTrHandle,
&m_tFbStmtHandle, SQL_DIALECT_V6, NULL);

return (m_tFbLastResult == 0);
}


void CSphSource_Firebird::SqlDismissResult ()
{
if ( !m_tFbStmtHandle )
return;

m_tFbLastResult = isc_dsql_free_statement( m_tFbLastStatus,
&m_tFbStmtHandle, DSQL_close );
free ( m_tFbSQLDA );
}


int CSphSource_Firebird::SqlNumFields ()
{
if ( !m_tFbStmtHandle )
return -1;

return m_iFbNumFields;
}


const char * CSphSource_Firebird::SqlColumn ( int iIndex )
{
if ( !m_tFbStmtHandle )
return NULL;

XSQLVAR *var = (XSQLVAR *) &m_tFbSQLDA->sqlvar[iIndex];

short dtype;
char data[1024], *p;
char blob_s[20], date_s[25];
PARAMVARY *vary2;
struct tm times;
ISC_QUAD bid;

dtype = var->sqltype & ~1;
p = data;

/* Null handling. If the column is nullable and null */
if ((var->sqltype & 1) && (*var->sqlind < 0))
{
return NULL;
}
else
{
switch (dtype)
{
case SQL_TEXT:
sprintf(p, "%.*s ", var->sqllen, var->sqldata);
break;
case SQL_VARYING:
vary2 = (PARAMVARY*) var->sqldata;
vary2->vary_string[vary2->vary_length] = '\0';
sprintf(p, "%-*s ", var->sqllen, vary2->vary_string);
break;
case SQL_SHORT:
case SQL_LONG:
case SQL_INT64:
{
ISC_INT64 value;
short field_width;
short dscale;
switch (dtype)
{
case SQL_SHORT:
value = (ISC_INT64) *(short *) var->sqldata;
field_width = 6;
break;
case SQL_LONG:
value = (ISC_INT64) *(int *) var->sqldata;
field_width = 11;
break;
case SQL_INT64:
value = (ISC_INT64) *(ISC_INT64 *) var->sqldata;
field_width = 21;
break;
}
dscale = var->sqlscale;
if (dscale < 0)
{
ISC_INT64 tens;
short i;

tens = 1;
for (i = 0; i > dscale; i--)
tens *= 10;

if (value >= 0)
sprintf (p, "%*" ISC_INT64_FORMAT "d.%0*" ISC_INT64_FORMAT "d",
field_width - 1 + dscale,
(ISC_INT64) value / tens,
-dscale,
(ISC_INT64) value % tens);
else if ((value / tens) != 0)
sprintf (p, "%*" ISC_INT64_FORMAT "d.%0*" ISC_INT64_FORMAT "d",
field_width - 1 + dscale,
(ISC_INT64) (value / tens),
-dscale,
(ISC_INT64) -(value % tens));
else
sprintf (p, "%*s.%0*" ISC_INT64_FORMAT "d",
field_width - 1 + dscale,
"-0",
-dscale,
(ISC_INT64) -(value % tens));
}
else if (dscale)
sprintf (p, "%*" ISC_INT64_FORMAT "d%0*d",
field_width,
(ISC_INT64) value,
dscale, 0);
else
sprintf (p, "%*" ISC_INT64_FORMAT "d%",
field_width,
(ISC_INT64) value);
}
break;

case SQL_FLOAT:
sprintf(p, "%15g ", *(float *) (var->sqldata));
break;

case SQL_DOUBLE:
sprintf(p, "%24f ", *(double *) (var->sqldata));
break;

case SQL_TIMESTAMP:
isc_decode_timestamp((ISC_TIMESTAMP *)var->sqldata, ×);
sprintf(date_s, "%04d-%02d-%02d %02d:%02d:%02d.%04d",
times.tm_year + 1900,
times.tm_mon+1,
times.tm_mday,
times.tm_hour,
times.tm_min,
times.tm_sec,
((ISC_TIMESTAMP *)var->sqldata)->timestamp_time % 10000);
sprintf(p, "%*s ", 24, date_s);
break;

case SQL_TYPE_DATE:
isc_decode_sql_date((ISC_DATE *)var->sqldata, ×);
sprintf(date_s, "%04d-%02d-%02d",
times.tm_year + 1900,
times.tm_mon+1,
times.tm_mday);
sprintf(p, "%*s ", 10, date_s);
break;

case SQL_TYPE_TIME:
isc_decode_sql_time((ISC_TIME *)var->sqldata, ×);
sprintf(date_s, "%02d:%02d:%02d.%04d",
times.tm_hour,
times.tm_min,
times.tm_sec,
(*((ISC_TIME *)var->sqldata)) % 10000);
sprintf(p, "%*s ", 13, date_s);
break;

case SQL_BLOB:
case SQL_ARRAY:
/* Print the blob id on blobs or arrays */
bid = *(ISC_QUAD *) var->sqldata;
sprintf(blob_s, "%08x:%08x", bid.gds_quad_high, bid.gds_quad_low);
sprintf(p, "%17s ", blob_s);
break;

default:
break;
}
}

return p;
}

const char * CSphSource_Firebird::SqlFieldName ( int iIndex )
{
if ( !m_tFbStmtHandle )
return NULL;

XSQLVAR *var = (XSQLVAR *) &m_tFbSQLDA->sqlvar[iIndex];

return var->aliasname;
}


bool CSphSource_Firebird::SqlFetchRow ()
{
if ( !m_tFbStmtHandle )
return false;

m_tFbLastResult = isc_dsql_fetch( m_tFbLastStatus, &m_tFbStmtHandle,
SQL_DIALECT_V6, m_tFbSQLDA );

m_bFbEOF = ( m_tFbLastResult == 100 );

return !m_bFbEOF;
}

#endif // USE_FIREBIRD

It is not just 10x longer, I also don't work since I never achieved
the blob handling code.

Thanks for your interrest.

Best regards,

--
Pierre Yager