Firebird EXTRACT(DAY FROM D)

Posts   
 
    
Posts: 94
Joined: 26-Feb-2006
# Posted on: 20-May-2007 14:59:05   

Hey folks,

I am currently trying to create a typed list to retrieve today's birthday list from a firebird db: Using a DB view or Proc is not viable though I am not the owner of the SCHEMA.

the following SQL works in EMS Firebird manager but can not find a way to define it via a DBFunction call :

SELECT "UMO301"."NAAMWEERGAVE" AS "NAME", "UMO301"."GEBOORTEDATUM" AS "GEBDATUM", "UMO301"."UMO001NR", EXTRACT(DAY FROM UMO301.geboortedatum) AS "GEBDAY", EXTRACT(MONTH FROM umo301.geboortedatum) AS "GEBMONTH", "UMO301"."GESLACHT" AS "GESCHLECHT", "UMO301"."LOCATIE", "UMO301"."NUMMER" FROM "UMO301" WHERE ( ( "UMO301"."UMO001NR" = 1))

My Code looks like this:


     Dim table As DataTable = New DataTable
        Dim fields As ResultsetFields


        Using adapter As DataAccess.DatabaseSpecific.DataAccessAdapter = New DataAccess.DatabaseSpecific.DataAccessAdapter
            Try 'fetch typedlist 

                table = New DataTable

                ' define fields
                fields = New ResultsetFields(8)

                fields.DefineField(Umo301FieldIndex.Naamweergave, 0, "NAME")
                fields.DefineField(Umo301FieldIndex.Geboortedatum, 1, "GEBDATUM")
                fields.DefineField(Umo301FieldIndex.Umo001Nr, 2, "UMO001NR")

                fields.DefineField(Umo301FieldIndex.Geboortedatum, 3, "GEBDAY")
                fields.DefineField(Umo301FieldIndex.Geboortedatum, 4, "GEBMONTH")


                fields.DefineField(Umo301FieldIndex.Geslacht, 5, "GESCHLECHT")
                fields.DefineField(Umo301FieldIndex.Locatie, 6, "LOCATIE")
                fields.DefineField(Umo301FieldIndex.Nummer, 7, "NUMMER")

    
                ' expression to apply for DBFunctionCall
                fields(3).ExpressionToApply = New DbFunctionCall("EXTRACT", New Object() {"DAY FROM UMO301.GEBOORTEDATUM"})
                fields(4).ExpressionToApply = New DbFunctionCall("EXTRACT", New Object() {"MONTH FROM UMO301.GEBOORTEDATUM"})


                ' groupBys
                Dim groupBy As New GroupByCollection()
                groupBy.Add(fields(1))

                ' filter on dbfuntionCall field
                Dim filterbucket As IRelationPredicateBucket = New RelationPredicateBucket()
                filterbucket.PredicateExpression.Add(New FieldCompareValuePredicate(fields(2), Nothing, ComparisonOperator.Equal, umo001nr))
                'filterbucket.PredicateExpression.Add(New FieldCompareValuePredicate(fields(3), Nothing, ComparisonOperator.Equal, Today.Date.Day))
                'filterbucket.PredicateExpression.Add(New FieldCompareValuePredicate(fields(4), Nothing, ComparisonOperator.Equal, Today.Date.Month))


                adapter.FetchTypedList(fields, table, filterbucket, 0, Nothing, False)
                table.TableName = Guid.NewGuid.ToString

            Catch ex As Exception

                DebugHelper.PrintException(ex)

            End Try
        End Using
        Return table

The Problem seems to be in the EXTRACT( DAY FROM D) Firebird specific function to resolve the day from the DATETIME field as the "Parameter" is actually part of the SQL..

The ORM generated SQL is this:

Generated Sql query: 
    Query: SELECT DISTINCT "UMO301"."NAAMWEERGAVE" AS "NAME", "UMO301"."GEBOORTEDATUM" AS "GEBDATUM", "UMO301"."UMO001NR", EXTRACT(@LOd661426a1) AS "GEBDAY", EXTRACT(@LO5504bd0a2) AS "GEBMONTH", "UMO301"."GESLACHT" AS "GESCHLECHT", "UMO301"."LOCATIE", "UMO301"."NUMMER" FROM "UMO301" WHERE ( ( "UMO301"."UMO001NR" = @UMO001NR3))
    Parameter: @LOd661426a1 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "DAY FROM UMO301.GEBOORTEDATUM".
    Parameter: @LO5504bd0a2 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "MONTH FROM UMO301.GEBOORTEDATUM".
    Parameter: @UMO001NR3 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.

LLBLGen/ORMSupport 2 (latest release)

Anyone a Idea how to solve this since EXTRACT(DAY FROM D) seems to be a important function in Firebird to handle date time retrieval..

Cheers

Adrian

Posts: 94
Joined: 26-Feb-2006
# Posted on: 20-May-2007 17:32:50   

solved!

Ok here is the solution for documentation:

the Firebird DB Function EXTRACT (DAY FROM D) could be described as a Function with an IExpression object as Parameter..

           ' expression to apply for DBFunctionCall
                Dim iexp1 As IExpression = New DbFunctionCall("DAY FROM", New Object() {Umo301Fields.Geboortedatum})
                Dim iexp2 As IExpression = New DbFunctionCall("MONTH FROM", New Object() {Umo301Fields.Geboortedatum})

                fields(3).ExpressionToApply = New DbFunctionCall("EXTRACT", New Object() {iexp1})
                fields(4).ExpressionToApply = New DbFunctionCall("EXTRACT", New Object() {iexp2})