DATETIME: Field of an unknoun type error

Hi to all, I got a "Field '00' is of an unknown type" error when I use this expression: "SELECT * FROM my_table WHERE my_date >= DATETIME(2010-12-01 11:00) YEAR TO MINUTE;" But if I use this one it works fine: "SELECT * FROM my_table WHERE my_date >= DATETIME(2010-12-01 11:00) YEAR TO HOUR;" I can't understand this behavior. What am I doing wrong? Any help will be appreciated. Hernando

Try quoting the datetime string. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 30, 2011 at 12:58 PM, HERNANDO DUQUE <hduquec@hotmail.com>wrote: > Hi to all, > > I got a "Field '00' is of an unknown type" error when I use this > expression: > > "SELECT * FROM my_table > WHERE my_date >= DATETIME(2010-12-01 11:00) YEAR TO MINUTE;" > > But if I use this one it works fine: > > "SELECT * FROM my_table > WHERE my_date >= DATETIME(2010-12-01 11:00) YEAR TO HOUR;" > > I can't understand this behavior. What am I doing wrong? > > Any help will be appreciated. > > Hernando > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf307f329205b781049fb65045

Hi, If I quote the datetime string I got a "Non-numeric character in datetime or interval" error :( Hernando.

Try: "2011-03-30 11:00"::datetime year to minute Art On Mar 30, 2011 1:32 PM, "HERNANDO DUQUE" <hduquec@hotmail.com> wrote: > Hi, > > If I quote the datetime string I got a "Non-numeric character in datetime or > interval" error :( > > Hernando. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --20cf307c9c3cb694e5049fb69ce7

> > "2011-03-30 11:00"::datetime year to minute > SELECT * FROM my_table WHERE my_date >= "2011-03-30 11:00"::datetime year to minute; Shows a Syntax Error

On Wed, Mar 30, 2011 at 09:58, HERNANDO DUQUE <hduquec@hotmail.com> wrote: > I got a "Field '00' is of an unknown type" error when I use this > expression: > > "SELECT * FROM my_table > WHERE my_date >= DATETIME(2010-12-01 11:00) YEAR TO MINUTE;" > This one should work. If you run the SQL in DB-Access or an equivalent, does it work? > But if I use this one it works fine: > > "SELECT * FROM my_table > WHERE my_date >= DATETIME(2010-12-01 11:00) YEAR TO HOUR;" > This one should fail. If you run it in DB-Access, what is the result? > I can't understand this behavior. What am I doing wrong? > Let's get down to some basics...the usual basics: * Which platform are you running on? * Which version of IDS are you using? * Which language are you using when you run into the problem? This data can start to help us guess where the problem is. It might be that there's enough code between what you wrote and the ASF layer that sends it to the server that it has all been manged beyond recognition. We may end up using SQLIDEBUG and sqliprint to see what is being sent to IDS. Note that 'field 00' message is similar to what a naïve program might produce if they have an Oracle-ish origin and parse the SQL and think that the ':00' is meant to be placeholder 00. The 'is of an unknown type' message suports this hypothesis. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --0016e68fc5be967f8d049fba45be

Thank you for your post. > >* Which platform are you running on? > PC with Windows Vista Professional, Local conection > >* Which version of IDS are you using? > Informix Developper 11.7 Client Sdk 3.70.TC1DE > >* Which language are you using when you run into the problem? > Client Locale en_US.CP1252 Database Locale en_US.819 > I realised that if I execute the expresion standalone from within dbaccess it works as expected, but fails if I execute it from within a Delphi 2007 application using Iformix DbExpres driver 3.0. I'm guessing that it might be that it is a posible bug into de driver. ¿!? Hernando.

Either that or Delphi is syntax checking the SQL and doesn't recognize something. IBM's ISQL had that problem with newer SQL syntax that it didn't recognize even though the engine that it was connected to would have accepted the query. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Mar 31, 2011 at 3:59 PM, HERNANDO DUQUE <hduquec@hotmail.com> wrote: > Thank you for your post. > > > > >* Which platform are you running on? > > > > PC with Windows Vista Professional, Local conection > > > > >* Which version of IDS are you using? > > > > Informix Developper 11.7 > Client Sdk 3.70.TC1DE > > > > >* Which language are you using when you run into the problem? > > > Client Locale en_US.CP1252 > Database Locale en_US.819 > > > > I realised that if I execute the expresion standalone from within dbaccess > it > works as expected, but fails if I execute it from within a Delphi 2007 > application using Iformix DbExpres driver 3.0. I'm guessing that it might > be > that it is a posible bug into de driver. !? > > Hernando. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf307d033884131a049fcd5c4a

Since some of the context is lost...the problem is that a specific SQL statement containing (roughly) SELECT * FROM SomeTable WHERE SomeColumn > DATETIME(2011-01-01 00:00)YEAR TO MINUTE is generating an error about 'Field 00 has not been given a type' or thereabouts. The previous missive (referenced by 'thank you for your post') suggested that the problem might be in the client software being used to access the database, rather than in the database itself, and asked some questions... On Thu, Mar 31, 2011 at 12:59, HERNANDO DUQUE <hduquec@hotmail.com> wrote: > Thank you for your post. > > >* Which platform are you running on? > > PC with Windows Vista Professional, Local conection > > >* Which version of IDS are you using? > > Informix Developer 11.7 > Client Sdk 3.70.TC1DE > > >* Which language are you using when you run into the problem? > > Client Locale en_US.CP1252 > Database Locale en_US.819 > I was referring to programming language rather than 'locale' language, but you give that information below. I realised that if I execute the expression standalone from within dbaccess > it > works as expected, but fails if I execute it from within a Delphi 2007 > application using Informix DbExpress driver 3.0. So, Delphi 2007 is the language. The driver is the IBM DRDA Driver, then? I'm not familiar with the designation 'DbExpress', but that probably means I've been Rip Van Winkling for a while. > I'm guessing that it might be that it is a possible bug into de driver. !? > I'm inclined to think that there is a bug or maladaptation in the driver. Especially since the code works correctly when passed to IDS unmolested (as in DB-Access). If DbExpress is a DRDA driver, you might be able to set DRDADEBUG=2:mine as an environment variable and then run the failing program. I think it will show that the query giving the error is not being sent to IDS at all; the error is detected (erroneously) by the client. Failing that, you might need to turn on DRDA tracing at the server end - fiddlier (not recommended for production systems). If DbExpress is a SQLI-based driver, then you'll be able to use SQLIDEBUG=2:mine instead. The advantage of this technique is that you get an almost transparent view of what is sent to IDS and what is sent by IDS. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --000e0cd22d4444f8f3049fcf3f32

Hello Hernando, the error is created by delphi itself. The ":" is interpreted as an introduction to any parameter. The TSQL-component has a property ParamCheck, maybe it is worth setting it to FALSE. You can safely set ParamCHeck to TRUE, write Your SQL like SELECT * FROM mytable WHERE my_date >= :myvalue and then assign the value You like to any TDateTime-Variable, which in turncan be assigned to ParamByName('myvalue').AsDateTime. wolfgang >> >> "2011-03-30 11:00"::datetime year to minute >> > SELECT * FROM my_table > WHERE my_date >= "2011-03-30 11:00"::datetime year to minute; > Shows a Syntax Error

wolfgang, You are absolutely right, the issue was being generated by Delhpi. When setting ParamCheck to False the problem was gone. Thank you, Hernando.