Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: error en Select odbc firebird

  1. #1
    Member
    Join Date
    Feb 2015
    Posts
    36

    error en Select odbc firebird

    Error de Sintaxis en Select cuando uso fechas (date)


    fini: 01/09/2011 12:00:00 AM ffin: 01/09/2015 12:00:00 AM

    SELECT * FROM AUXILIAR1401 WHERE tipo_poli='Dr' AND FECHA_POL >= ?fini AND FECHA_POL <= ?ffin AND (substring(concep_po from 1 for 2)='F-' OR substring(concep_po from 1 for 3)='NC-')


    La conexion del ODBC esta funcionado bien y esta misma sintaxis del SELECT
    la tengo en visual foxpro y trabaja bien. No se porque en Lianja me manda error.
    si quito las fechas si funciona.

    Saludos
    Rodolfo

  2. #2
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658
    Hi Rodolfo,

    Sorry for the english reply.

    I do not believe you can use a ? in an sql statement for substitution.
    Lianja usually views the ? as a command for the console.
    You may need to change to an ampersand (&).

    Example:
    Code:
    ...  FECHA_POL >= '&fini' ...
    Cory

  3. #3
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    1,941
    Hi Cory,

    the ? before a variable on the right side of an equality operator marks the variables as a parameter that should be substituted. Beisdes, Rodofo notes that the ODBC SQL works fine from VFP.

    Try this from the console or in a prg:

    Code:
    open data southwind
    lccust = "ALFKI"
    select * from customers where customerID = ?lccust

    Hi Rodolfo,

    What driver are you using for your ODBC connection? It might accept ODBC time format like this:

    Code:
    {ts '2015-09-01 18:09:26'}
    or be looking for universal time format like this:

    Code:
    {20150901 18:09:46}
    I doubt it would take the time format you show. VFP is likely converting what is the system time format to the ODBC time format.

    Hank

  4. #4
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658
    Hi Hank,

    Thank you for the correction.
    My apologies Rodolfo.

    Cory

  5. #5
    Member
    Join Date
    Feb 2015
    Posts
    36
    odbc is firebird interbase

    and this is the correct syntax for date fields:

    SELECT * FROM AUXILIAR1401 WHERE tipo_poli='Dr' AND FECHA_POL >= {ts '01/28/2014 00:00:00'} AND FECHA_POL <= {ts '01/29/2014 00:00:00'} AND (substring(concep_po from 1 for 2)='F-' OR substring(concep_po from 1 for 3)='NC-')
    Proceso Terminado Total de Registros: 281

    thanks Cory and Hank great help

  6. #6
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    1,941
    Hi Rodolfo,

    that's great. I thought it might work: I had to use ODBC date format when working with the Advantage driver against VFP tables, so thought it might work here.

    Hank

  7. #7
    Member
    Join Date
    Feb 2015
    Posts
    36
    know how the syntax for dates with Visual FoxPro to ODBC lianja.
    I'm trying several but do not work:
    ffin=date()
    myselect="SELECT * FROM FACTDIG WHERE FCHFACTURA<={?ffin}"
    myselect="SELECT * FROM FACTDIG WHERE FCHFACTURA<=?ffin"
    myselect="SELECT * FROM FACTDIG WHERE FCHFACTURA<={ts '01/29/2015 00:00:00'}"


    if lnConnHandle > 0
    sqlexec(lnConnHandle,myselect,"FACTDIG")
    else
    messagebox("Sin Conexion","Error")
    RETURN .F.
    endif

  8. #8
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    1,941
    Hi Rodolfo,

    try this:

    ffin = datetime(year(date()),month(date()),day(date()))

    This will produce a datetime that is set to the starting time of date(), but will do so as a true datetime() variable. That should work with your second example using <= ?ffin.

    VFP does that date() to datetime() conversion transparently when it knows to do that; but would not know to do that inside a sqlexec().

    Good luck,

    Hank

  9. #9
    Member
    Join Date
    Feb 2015
    Posts
    36
    no work:

    ffin = datetime(year(date()),month(date()),day(date()))
    myselect="SELECT * FROM FACTDIG WHERE FCHFACTURA<=?FFIN"

    any other idea?
    Thanks

  10. #10
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,769
    Have a look at the TTOC() function.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

Page 1 of 2 12 LastLast

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us