Operaciones con fechas en ADO

[Pedro]

Hola a todos
Necesito hacer operaciones con fechas en ADO, búsqueda blanda (tipo SoftSeek), fitros de un campo y dos fechas, etc.
Alguno de ustedes podría tirarme un cable?
Les quedaría muy agradecido

[Rick Lipkin]

Pedro

If I understand your question correctly ( google translate ) .. there is no good SQL way of simulating an xbase 'softseek' with SQL and Date\time fields. The Sql LIKE operator only works on character fields.

Your best solution would be the use of the BETWEEN operator to find a range of dates high and low.

http://www.w3schools.com/sql/sql_between.asp

Rick Lipkin

[Pedro]

Rick
Thank you very much for the tip, but could you tell me how to put this?
SELECT FECHA, FACTURA, CODIGO,NOMBRE,VENCTO,IMPORTE FROM VENCTOS WERE VENCTO BETWEEN DFECHAINICIO AND DFECHAFINAL
I've tried these ways.

1.-

Code: Select all  Expand view

Local cSql2  := "SELECT FECHA,FACTURA,CODIGO,NOMBRE,VENCTO,IMPORTE, FROM VENCTOS WERE VENCTO BETWEEN "+Dtoc(dInicio)+" AND "+Dtoc(dFinal)

2.-

Code: Select all  Expand view

Local dFIni := "#"+PADL(Month(dInicio),2,"0")+"/"+PADL(Day(dInicio),2,"0")+"/"+STR(YEAR(dInicio))+"#"
Local dFEnd := "#"+PADL(Month(dFinal),2,"0")+"/"+PADL(Day(dFinal),2,"0")+"/"+STR(YEAR(dFinal))+"#"
Local cSql1  := "SELECT FECHA,FACTURA,CODIGO,NOMBRE,VENCTO,IMPORTE FROM VENCTOS WERE VENCTO BETWEEN '"+dFIni+"'"+" AND '"+dFEnd+"'"+" ORDER BY VENCTO"

Of all the ways I try, the recordset is nil, that is not open. Tell you that I'm using an Acces database

[Rick Lipkin]

Pedro

Ms Access ( unlike any other sql database ) does not treat dates as character... Trick is to use hash marks # instead of ' to delimit your dates .. you are on the right track with your second query but you still have to remove the ' .. also remove the last comma before the FROM in the first query .. make sure Century is on and designate str(year ..., 4 )

Try your query like this :

Code: Select all  Expand view

Local cSql2,dFIni,dFEnd

// make sure century is on ( in your main startup )
// SET Century On

// added ctod() to match dtoc() in query

dFIni :=   ctod(PADL(Month(dInicio),2,"0")+"/"+PADL(Day(dInicio),2,"0")+"/"+STR(YEAR(dInicio,4)) )
dFEnd := ctod(PADL(Month(dFinal),2,"0")+"/"+PADL(Day(dFinal),2,"0")+"/"+STR(YEAR(dFinal,4)) )

// verify dates look correct
MsgInfo( dFIni )
MsgInfo( dFEnd )


cSql2 := "SELECT FECHA,FACTURA,CODIGO,NOMBRE,VENCTO,IMPORTE "
cSql2 += "FROM VENCTOS WERE VENCTO "
cSql2 += "BETWEEN #"+Dtoc(dFIni)+"# AND #"+Dtoc(dFEnd)+"#"

 

Also .. if you are using a recent build of (x)Harbour .. DateTime values now have a valtype of "T" not "D" .. This only comes into play when you create a date variable from a SQL Field

dDate := oRs:Fields("Date"):Value // valtype dDate = T not D result "03/10/2013 12:03 PM"
dDate := TtoDate( dDate ) // valtype dDate = D and can be manipulated as expected

// add this function to your user defined functions in your program

Code: Select all  Expand view

//--------------------------
Function TtoDate( tDate )

If empty( tDate)
   Return( ctod("00/00/00"))
Endif

If ValType( tDate ) = "D"
   Return(tDate )
Endif

Return( stod( substr( ttos( tDate ), 1, 8 ) ))

 

[Simon]

yo lo hago asi:

Code: Select all  Expand view

cQuery := "SELECT FECHA,FACTURA,CODIGO,NOMBRE,VENCTO,IMPORTE, FROM VENCTOS WERE VENCTO >= '"+DtoS( dInicio )+"' AND VENCTO <= '"+DtoS( dFinal ) + "'"
 

uso dtos en vez de dtoc para convertirlo a string y usar la fecha como ansi "yyyymmdd", asi no tienes problemas con el codigo sql, corre en sql server y mysql.

[Pedro]

Simon
No funciona, el recordset sigue viniendo a Nil

Rick

Este es el código que uso para hacer el recordset

Code: Select all  Expand view

*-------------------------------------------------------------------------------
 Function REPLISVTO(nOption,dInicio,dFinal,oRsVctos)
*-------------------------------------------------------------------------------
Local nReg  := oRsVctos:BookMark()
Local aVtos := {}
Local dFIni := Ctod(PADL(Month(dInicio),2,"0")+"/"+PADL(Day(dInicio),2,"0")+"/"+STR(YEAR(dInicio)))
Local dFEnd := Ctod(PADL(Month(dFinal),2,"0")+"/"+PADL(Day(dFinal),2,"0")+"/"+STR(YEAR(dFinal)))
Local cSql1  
Local cSql2    
Local cSql
Local oRsLisVto

     cSql1 := "SELECT FECHA,FACTURA,CODIGO,NOMBRE,VENCTO,IMPORTE "
     cSql1 += "FROM VENCTOS WERE VENCTO "
     cSql1 += "BETWEEN #"+Dtoc(dFIni)+"# AND #"+Dtoc(dFEnd)+"#"
     cSql2 := "SELECT FECHA,FACTURA,CODIGO,NOMBRE,VENCTO,IMPORTE,FPAGO,TIPOPAGO "
     cSql2 += "FROM VENCTOS WERE VENCTO "
     cSql2 += "BETWEEN #"+Dtoc(dFIni)+"# AND #"+Dtoc(dFEnd)+"#"
         
     If Dtos(dInicio)> Dtos(dFinal)
        MsgStop("Parámetros de fechas incorrectos","ATENCION")
        Return nil
     EndIf  
     IIf(nOption== 1, cSql := cSql1, cSql := cSql2)
   
     oRsLisVto := FW_OpenRecordSet(oConex,cSql,3)  //<------------(Parametros, oConex = Conexión, cSql = Fuente de datos, 3 = LockType) Esta función está en fwh/source/functions/olefuncs.prg

     Msginfo(oRsLisVto) //<-----------------( oRsLisVto --> Nil)
........

 

Este error es provocado para saber si los parámetros y las variables locales son correctas

Compiler version: xHarbour build 1.2.1 Intl. (SimpLex) (Rev. 9656)
FiveWin Version: FWHX 12.06
Windows version: 6.1, Build 7600

Time from start: 0 hours 1 mins 54 secs
Error occurred at: 11-03-2013, 17:30:30
Error description: Error BASE/1004 Class: 'NIL' has no exported method: EOF
Args:
[ 1] = U

Stack Calls
===========
Called from: => EOF( 0 )
Called from: D:\PROYEC~4\Pizarro\SOURCE\gesh0600.prg => REPLISVTO( 295 )
Called from: D:\PROYEC~4\Pizarro\SOURCE\gesh0600.prg => (b)IMPRIMEVENCTO( 257 )
Called from: .\source\classes\BUTTON.PRG => TBUTTONBMP:CLICK( 157 )
.........................................

Aqui las variables usadas

Variables in use
================
Procedure Type Value
==========================
EOF
Local 1: A Len: 0
Local 2: U
Local 3: U
REPLISVTO
Param 1: N 1
Param 2: D 01-02-2012
Param 3: D 11-03-2013
Param 4: O Class: TOLEAUTO
Local 1: N 1.00
Local 2: A Len: 0
Local 3: D 02-01-2012
Local 4: D 03-11-2013
Local 5: C "SELECT FECHA,FACTURA,CODIGO,NOMBRE,VENCTO,IMPORTE FROM VENCTOS WERE VENCTO BETWEEN #02-01-2012# AND #03-11-2013#"
Local 6: C "SELECT FECHA,FACTURA,CODIGO,NOMBRE,VENCTO,IMPORTE,FPAGO,TIPOPAGO FROM VENCTOS WERE VENCTO BETWEEN #02-01-2012# AND #03-11-2013#"
Local 7: C "SELECT FECHA,FACTURA,CODIGO,NOMBRE,VENCTO,IMPORTE FROM VENCTOS WERE VENCTO BETWEEN #02-01-2012# AND #03-11-2013#"
Local 8: U <--------------Esto debería ser oRsLisVto un objeto de la clase TOLEAUTO

[Simon]

Pedro con que motor de base de datos estas trabajando ?

tips.
1.- debes tener donde probar los querys, si usas sql server debes tener SQL SERVER MANAGAMENT STUDIO, si usas mysql yo uso EMS SQL MANAGER2005 for MySQL, pero hay muchos y gratis.
2.- una vez generes los querys pruebalos, muestralos com memodit lo copias y los pegas en el programa anteriormente mencionado y verificas la sintaxis SQL ejecutandolo, yo al principio cuando no sabia nada de sintaxis SQL lo use muchisssimo asi.
3.- hace rato deje de usar / en las fechas, porque va a depender de la persona que hizo la instalacion del servidor y tienes que estar pendiente de cambiarlo, tuve 2 clientes y les instale la misma aplicacion y en uno corria bien y en otro no, daba errores por las fechas, y comence a usarlas en formato ANSI YYYYMMDD talcual como las devuelve DTOS y ya no estoy pendiente de las fechas.

este codigo lo acabo de probar en mysql y corre perfecto

Code: Select all  Expand view

select * from facturas where factura.emision between '20130101' AND '20130131'

 

ahora lo aplico con harbour

Code: Select all  Expand view

Function main()
Local oAdoConnector := adoConnector()
Local cQuery             := "select * from facturas where factura.emision between '20130101' AND '20130131'"

//memoedit( cQuery ) //para cortar y pegar en el manejador sql

adorecordset(  oAdoConnector,  cQuery )

Return nil

Function ADOConnector()

   Local cStr     := "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Port=3306;Database=" + "BASE_DE_DATOS" + ";User=" + "USUARIO" + ";Password=" + "12345"+ ";Option=3;"
   Local oReturn  := tOleAuto():New("ADODB.connection")

   oReturn:ConnectionSTring  := cStr

   try
      oReturn:Open()
   catch
      oReturn := nil      
   end try

Return oReturn



Function ADORecordSet( oConnect, cSql, lConnect )

   Local oRs
   Local cError := "No se ha podido crear el objeto contenedor RECORDSET !"

   try
      oRs := CreateObject("ADODB.RecordSet")
      oRs:CursorLocation  := 3
      oRs:LockType        := 4
      oRs:ActiveConnection:= oConnect
      oRs:source := cSql
      oRs:Open()
      lConnect := .t.
   catch
      //MsgStop( cError + CRLF + CRLF + "Query : " + cSql,"Error de Datos (ADO)"  )
      msadoerror( cSql, "Error de Conexion con servidor" )
      return nil
   end try

Return oRS

 

[Rick Lipkin]

Pedro

Your code fails because you are not setting up your Recordset. Here is a sample of a MS Access connection string stored to the Public variable xConnect.

Again, this is for Ms Access .. you did say you were using MS Access ?

Rick Lipkin

Code: Select all  Expand view

// Ms Access connection string

// place this in your top Main.prg
// modify xSource to your database name
Local xProvider,xSource,xPassword,cFile,mStart,cDefa
Public xConnect

// where .exe started from is default directory //

cFILE  := GetModuleFileName( GetInstance() )
mSTART := RAT( "\", cFILE )
cDEFA  := SUBSTR(cFILE,1,mSTART-1)

SET DEFA to ( cDEFA )

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE   := cDEFA+"\Travel.mdb"   // ms access table
xPASSWORD := "yourpassword"
xCONNECT  := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD

//--
Local oRs,cSql,oErr,dFIni,dFEnd

dFIni := Ctod(PADL(Month(dInicio),2,"0")+"/"+PADL(Day(dInicio),2,"0")+"/"+STR(YEAR(dInicio)))
dFEnd := Ctod(PADL(Month(dFinal),2,"0")+"/"+PADL(Day(dFinal),2,"0")+"/"+STR(YEAR(dFinal)))


//Open the recordset

oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic

cSql := "SELECT FECHA,FACTURA,CODIGO,NOMBRE,VENCTO,IMPORTE "
cSql += "FROM VENCTOS WERE VENCTO "
cSql += "BETWEEN #"+Dtoc(dFIni)+"# AND #"+Dtoc(dFEnd)+"#"

TRY
   oRs:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening  table" )
   RETURN(.f.)
END TRY

xBrowse( oRs )

oRs:CLose()

[Pedro]

Simon, como ya he puesto antes, esto es con una tabla de ACCESS, no estoy usando MySql, ni SQL Server, eso lo dejo para un poco más adelante, cuando domine mejor ADO,
aunque esto mismo sirve para otras bases de datos.
El problema que se produce, creo yo, es que al establecer la apertura del recordset con esa sentencia, no general el recordset.
El query que tu pones queda muy bien con fechas fijas, pero no cuando las fechas las elije el usuario y tu has de pasar al query las variables que contienen los valores elegidos.

[Simon]

ah access.

las fechas funcionan de manera distinta a los servidores sql, una vez hice algo muy parecido para un control de acceso biometrico y la base de datos era mdb, hice varios reportes y debia buscar los registros tal cual lo necesitas tu por rango de fechas, dejame buscarlo y te paso el ejemplo.

[Simon]

ya lo encontre espero te sirva

Code: Select all  Expand view

dDate  := date()
cDate1 := "01/01/" + NTOC( str( year( dDate ) - 2, 4, 0 ), 4 )
cDate2 := NTOC( str( month( dDate ), 2, 0 ), 2 ) + "/" + NTOC( str( day( dDate ), 2, 0 ), 2) + "/" + NTOC( str( year( dDate ), 4, 0 ), 4 )
cQuery := "select checkinout.userid, userinfo.name, checkinout.checkTime, checkinout.checktype from checkinout " + CRLF + ;
                "left join userinfo on checkinout.userid = userinfo.userid " + CRLF + ;
                "where checkinout.checkTime >= #" + cDate1 + "#  and checkinout.checkTime <= #" + cDate2 + "# " + CRLF + ;
                "order by checkinout.userid, checkinout.checktime"

oDbf   := Adorecordset( oApp:oAdoConnector, cQuery )

xbrowse( oDbf )

 

en el ejemplo hay 2 tablas una checkinout donde se almacenan los registros de entrada/salida de los trabajadores y userinfo donde se almacena la informacion de los trabajadores en el ejemplo traigo todos los registros de los trabajadores del año en curso hasta la fecha.

[Pedro]

Rick
In my module MAIN open the connection to the database. The variable connection (oConex) is public and I see all the sections, part I check that the connection is not closed. Below I'll put as the connection to the database.
The problem is that when trying to open the recordset, this does not open, FW_OpenRecordSet function (StringConect, Source, LokType) returns nil, so the recordset failed to create, and this is because the SQL statement to opening. Perhaps the issue is given by the type of field in the table, and there is no way to change it, go try it today start date and end date to TimeStamp to see if that way is created recordset.
This function is performed only choose the work area, the database with which we will work, because this program works with multiple databases.
Each work area is in a different folder

I hope you forgive me for using the Google translator

Code: Select all  Expand view

Function Main()

   Local aCorH[2]
   Local hDlib
   Local oBtnBal1,oBtnBal2,oBtnBal3,oBtnBal4,oBtnBal5
   Local oPopBal1,oPopBal2,oPopBal3,oPopBal4,oPopBal5
 
   Public  oWMain, oFont,oIcon,oConex
   Public  cAreaTemp, cArea := "", aDatEmp[0],cTrabajo,aIva[7,3]
...........................................
   DEFINE WINDOW oWMain MDI FROM 02, 02 TO ( MaxRow() - 2 ), ( MaxCol() - 10 );
.............................................
      ACTIVATE WINDOW oWMain MAXIMIZED ;
               ON INIT (EscogeArea(),VerificaConfig()) ;   //<-----------------EscogeArea is where you choose the work area
.........................................................
*===============================================================================
function EscogeArea()            
*===============================================================================
....................................
      DEFINE DIALOG oDlgEmp RESOURCE "INICIO";
             COLOR RGB(0,0,0), RGB(153,204,255)
 
      REDEFINE BITMAP oBmp ID 101 OF oDlgEmp ;
               FILENAME ".\BMP\LOGO2.BMP" ;
               ADJUST;
               UPDATE;
               TRANSPARENT
      REDEFINE BUTTONBMP ID 301 OF oDlgEmp BITMAP "ACEPTAR" TEXTRIGHT;
                 ACTION  lSeguir := PonArea(oDEmpr,oLbx,oDlgEmp)
           
                   
      oLbx := TXBrowse():New(oDlgEmp)
.....................................................
      for nFor := 1 to len(aCols)
          oLbx:aCols[ nFor ]:blDClickData  := {|| (lSeguir := PonArea(oDEmpr,oLbx,oDlgEmp),oLbx:Refresh()) }
          oLbx:aCols[ nFor ]:bRClickData   := {|| (lSeguir := PonArea(oDEmpr,oLbx,oDlgEmp),oLbx:Refresh()) }
          oLbx:aCols[ nFor ]:bRClickHeader := {|r,c,f,o| NIL }
      next
.................................................................
//----------------------------------------------------------------------------//
function PonArea(oDEmpr,oLbx,oDlgEmp)
//----------------------------------------------------------------------------//
Local cCarpeta, cEstacion
Local cDir := cArea
Local cSitio,lSeguir

    Sysrefresh()
    ASIZE(aDatEmp, EMP->( FCOUNT() ) )
    AFILL(aDatEmp,.T.)

    EMP->( IniCampo(@aDatEmp) )
    EMP->( TabCampo(@aDatEmp,0) )
    cEstacion := NetName(.t.)
    cCarpeta  := cDir+"\"+cEstacion
    cSitio    := cDir+"\"+oDEmpr:Codigo+"\"
    cArea     := cSitio
    cAreaTemp := cCarpeta
    cNomArea  := oDEmpr:Nombre+"  "+Str(oDEmpr:Ejercicio)
    cTrabajo  := "[ "+oDEmpr:codigo+"  "+oDEmpr:nombre+" ]"
    oWMain:SetText("Gestión Comercial  "+cTrabajo)
    oWMain:Refresh()
    Set Path to (cArea)
    Set Default to (cArea)
    cEjercicio := Str(oDEmpr:Ejercicio)
    lSeguir := AbreTablas()          //<<=========================Here
    If lSeguir
       CARGAIVA()
    EndIf
    oLbx:Destroy()
    oDlgEmp:End()

       
Return( lSeguir )

*-------------------------------------------------------------------------------
FUNCTION AbreTABLAS()
*-------------------------------------------------------------------------------
Local cServer  := "localhost"
Local cUser    := "root"
Local cPass    := ""
Local lConecta := .T.
Local cSource  := cArea+"Datos.mdb"
 
* Para aplicaciones en MySQL

*      oConex := TOLEAUTO():new("adodb.connection")
*      oConex:ConnectionString:="Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=LBRINQUIS;User='root'; Password='';Option=3;"
*      TRY
*         oConex:Open()
*         MSGINFO("CONECTADO")
*      CATCH
*         MSGSTOP("NO SE CONECTO")
*         RETURN NIL
*     END

* Para aplicaciones con ACCES
 
*       oConex :=  TOLEAUTO():NEW("ADODB.connection")
       oConex :=  CREATEOBJECT("ADODB.connection")      
       oConex:open("Provider= MicroSoft.Jet.OLEDB.4.0;Data Source="+cSource+";")
                 
       If Empty(oConex)
          MsgAlert(" No se Pudo Establecer la Conexion Con La Base De Datos..","Atencion")
          lConecta := .F.
       EndIf
       If lConecta
*          AbreBrochas()
       EndIf
Return(lConecta)

Failure to connect to the database the program does not continue, if you disconnect the program ends

[Pedro]

ya lo encontre espero te sirva

Code: Select all  Expand view

dDate  := date()
cDate1 := "01/01/" + NTOC( str( year( dDate ) - 2, 4, 0 ), 4 )
cDate2 := NTOC( str( month( dDate ), 2, 0 ), 2 ) + "/" + NTOC( str( day( dDate ), 2, 0 ), 2) + "/" + NTOC( str( year( dDate ), 4, 0 ), 4 )
cQuery := "select checkinout.userid, userinfo.name, checkinout.checkTime, checkinout.checktype from checkinout " + CRLF + ;
                "left join userinfo on checkinout.userid = userinfo.userid " + CRLF + ;
                "where checkinout.checkTime >= #" + cDate1 + "#  and checkinout.checkTime <= #" + cDate2 + "# " + CRLF + ;
                "order by checkinout.userid, checkinout.checktime"

oDbf   := Adorecordset( oApp:oAdoConnector, cQuery )

xbrowse( oDbf )

 

en el ejemplo hay 2 tablas una checkinout donde se almacenan los registros de entrada/salida de los trabajadores y userinfo donde se almacena la informacion de los trabajadores en el ejemplo traigo todos los registros de los trabajadores del año en curso hasta la fecha.

El problema, Simon, como ya te comentaba, es (comentando tu ejemplo) que oDbf   := Adorecordset( oApp:oAdoConnector, cQuery ) = Nil, es decir oDbf no se ha creado correctamente por la sentencia cQuery (o cSql en mi caso) .
Fijate un poco más arriba, donde expongo el error CLASS NIL has no exported METHOD Eof(), provocado al saber que no se va a crear el recordset, verifico que las variables tengan los valores correctos, y si los tienen. Si te fijas tu cDate2 tiene la misma forma que cSql1 y cSql, salvo que yo uso como separadores el guión y no la barra, pero eso no creo que sea relevante.
Como le indico a Rick Lipkin voy a intentar pasar los parámetros DATE a TimeStamp a ver si así crea el recordset

[Rick Lipkin]

Pedro

What is the code behind this connection Adorecordset()

oDbf := Adorecordset( oApp:oAdoConnector, cQuery )

Rick

[Pedro]

Rick
These functions are not mine, but as Simon has put the copy you.
Are practically the same that is in FWH / sources / function / olefuncs.prg

Code: Select all  Expand view

Function ADOConnector()

   Local cStr     := "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Port=3306;Database=" + "BASE_DE_DATOS" + ";User=" + "USUARIO" + ";Password=" + "12345"+ ";Option=3;"
   Local oReturn  := tOleAuto():New("ADODB.connection")

   oReturn:ConnectionSTring  := cStr

   try
      oReturn:Open()
   catch
      oReturn := nil      
   end try

Return oReturn



Function ADORecordSet( oConnect, cSql, lConnect )

   Local oRs
   Local cError := "No se ha podido crear el objeto contenedor RECORDSET !"

   try
      oRs := CreateObject("ADODB.RecordSet")
      oRs:CursorLocation  := 3
      oRs:LockType        := 4
      oRs:ActiveConnection:= oConnect
      oRs:source := cSql
      oRs:Open()
      lConnect := .t.
   catch
      //MsgStop( cError + CRLF + CRLF + "Query : " + cSql,"Error de Datos (ADO)"  )
      msadoerror( cSql, "Error de Conexion con servidor" )
      return nil
   end try

Return oRS