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

Thanx Byron

version 0009 works perfectly now with remote views on VFP it seems the escaped character don't interfere with
anything.

I think the guys at mickeysoft don't even know how to use the drivers properly.
It seems that the oracle odbc driver from oracle themselfs has the same problem.

THANK YOU VERY MUCH

On maandag 28 februari 2000 1:17, Byron Nikolaidis [SMTP:byron.nikolaidis@home.com] wrote:
> 
> 
> 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
readyto be 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
> 
> ************