- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Firebird EXTRACT(DAY FROM D)
Joined: 26-Feb-2006
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
Joined: 26-Feb-2006
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})