Re: [INTERFACES] pgsql ODBC driver and Visual Foxpro - Mailing list pgsql-interfaces

Willy De la Court wrote:
> 
> Hi,
> 
> I'v got the following problem with Visual Foxpro (VFP) and using the pgsql ODBC driver.
> 
> With Visual Foxpro it is possible to create parametrized views it looks something like this.
> 
> create sql view xxx remote connection bms as select * from peo where peo_name = 'A' and peo_code = ?ncode
> 
> where '?ncode' is the parameter. Normaly the ?ncode is replaced before the select statement is send to the backend.
>  visual foxpro will try first to send the query unmodified to the backend.
> and always it gets an error from the backend. Here is a piece of the log.
> 
>                  statement_type=0, statement='select * from peo where peo_name  = 'A' and peo_code = ?'
>                  stmt_with_params='select * from peo where peo_name  = 'A' and peo_code = '
> 
> Then VFP even after it got an error it will try to get the number of rows returned.
> 
>   STATEMENT ERROR: func=SQLNumResultCols, desc='', errnum=3, errmsg='No query has been executed with that handle'
> 
> This error message is also normal.
> 
> Then VFP tries to execute an SQLExecDirect with "select * from peo  WHERE 0=1"
> and this fails also which is not normal.
> 
>   STATEMENT ERROR: func=SQLExecute, desc='', errnum=2, errmsg='The handle does not point to a statement that is ready
tobe executed'
 
> 
> AND LOOK AT THESE 2 LINES IN THE LOGFILE.
> 
>                  statement_type=0, statement='select * from peo  WHERE 1=0'
>                  stmt_with_params='select * from peo where peo_name  = 'A' and peo_code = '
> 
> What happend here???????
> 
> Can somebody please help me with this I need the odbc driver to work with visual foxpro.
> PS normal SQL statements without the functionality of parameters work great.
> 
> Included are both the sql.log and the psqlodbc.log
> 
> 
> 
>               Name: SQL.LOG
>    SQL.LOG    Type: LOG File (application/x-unknown-content-type-log_auto_file)
>           Encoding: x-uuencode
> 
>                    Name: psqlodbc.log
>    psqlodbc.log    Type: LOG File (application/x-unknown-content-type-log_auto_file)
>                Encoding: x-uuencode
> 
> ************


OK, 

The real problem appears to be an escape sequence in your query:

select * from peo where peo_name  = 'A' and peo_code = ?\ 0

The backend responds with:

ERROR from backend during send_query: 'ERROR:  parser: parse error at or
near ""'

Since Postgres still does not have prepared statements, the driver can
not execute this statement and does not return this error in the
SQLPrepare, but in the next call, which is SQLNumResultCols.   I'm not
sure if foxpro is really prepared for this but according to odbc specs,
it is supposed to handle this possibility for drivers that can not
execute prepared statements right away.

Foxpro then executes another query, which also has the bad escape
sequence:

select * from peo  WHERE 1=0\ 0

However, this query never even gets to the backend because of a
state-transition problem in the driver, *which I have fixed in
v.0009*.   If you try the new driver, it will still fail because of the
bad escape sequences.

Bottom line, if you can get rid of the escapes everything should work.


Byron