Re: 1206 error when connected to 'remote' INFORMIXSERVER

Any help, comments?? On Tue, 20 May 2003 16:27:05 +0200, Nebojsa Sevo <DELETEmips@zg.tel.hr> wrote: >I am working on host server1 and my INFORMIXSERVER is local1. >In my SQLHOSTS file there is entry for remote INFORMIXSERVER remote1 located on >host server2. > >I write select statement in dbaccess > select * from datab@remote1:posdne where postbr='88999' and danas = '' >and what I get is 1206 error.> >I log on server2 and run > onstat -g sql ##>and here is what I get: > >select x0.danas ,x0.postbr ,x0.prtime ,x0.odtime ,x0.aktpos ,x0.aktsal > ,x0.kradan ,x0.numipr ,x0.numiod ,x0.glabla ,x0.prijave ,x0.dnevni > ,x0.dnerad ,x0.tkpren ,x0.dodsw1 ,x0.dodsw2 ,x0.dodsw3 ,x0.dodsw4 > ,x0.dodsw5 ,x0.dodsw6 ,x0.statis ,x0.zbrojr ,x0.zbrojz from > datab:"informix".posdne x0 where ((x0.danas = DATE ('NULL' ) ) AND > (x0.postbr = '88999' ) )> >Empty string from my statement is converted to DATE('NULL') and that is the >reason for error. > >But if I connect to INFOMRIXSERVER remote1 (dbaccess -> Connection -> Connect -> >remote1) and run the query there is no error and > onstat -g sql ##>returns > select * from datab@remote1:posdne where postbr='88999' and danas = ''> >Is there any way to avoid that behavior? Env. variable or some other trick? > >Nebojsa >------------------------------------ >Remove spam block (DELETE) to reply ------------------------------------ Remove spam block (DELETE) to reply

Nebojsa Sevo wrote: > Any help, comments?? Tut, tut - patience! Evidently not. Which tools are you using? Which server version are you using? Which platform are you using? I'm not sure whether the server would ever make that translation -- though it might. What happens on the local machine if you do: SELECT * FROM Systables WHERE Created = DATE('NULL'); What about DATE(NULL)? What about DATE('')? Why didn't you write "AND danas IS NULL" in the first place to avoid the angst of repeated postings? > On Tue, 20 May 2003 16:27:05 +0200, Nebojsa Sevo <DELETEmips@zg.tel.hr> wrote: >>I am working on host server1 and my INFORMIXSERVER is local1. >>In my SQLHOSTS file there is entry for remote INFORMIXSERVER remote1 located on >>host server2. >> >>I write select statement in dbaccess >> select * from datab@remote1:posdne where postbr='88999' and danas = '' >>and what I get is 1206 error.>> >>I log on server2 and run >> onstat -g sql ##>>and here is what I get: >> >>select x0.danas ,x0.postbr ,x0.prtime ,x0.odtime ,x0.aktpos ,x0.aktsal >> ,x0.kradan ,x0.numipr ,x0.numiod ,x0.glabla ,x0.prijave ,x0.dnevni >> ,x0.dnerad ,x0.tkpren ,x0.dodsw1 ,x0.dodsw2 ,x0.dodsw3 ,x0.dodsw4 >> ,x0.dodsw5 ,x0.dodsw6 ,x0.statis ,x0.zbrojr ,x0.zbrojz from >> datab:"informix".posdne x0 where ((x0.danas = DATE ('NULL' ) ) AND >> (x0.postbr = '88999' ) )>> >>Empty string from my statement is converted to DATE('NULL') and that is the >>reason for error. >> >>But if I connect to INFOMRIXSERVER remote1 (dbaccess -> Connection -> Connect -> >>remote1) and run the query there is no error and >> onstat -g sql ##>>returns >> select * from datab@remote1:posdne where postbr='88999' and danas = ''>> >>Is there any way to avoid that behavior? Env. variable or some other trick? Why would anyone want another environment variable? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

On Thu, 22 May 2003 03:08:43 GMT, Jonathan Leffler <jleffler@earthlink.net> wrote: >Nebojsa Sevo wrote: >> Any help, comments?? > >Tut, tut - patience! > I apologize but I am not sure that my english is enough good and that people on CDI can understand what I am asking about. Don't want to mention problem with "lost messages" that happened sometimes. >Evidently not. Which tools are you using? Which server version are >you using? Which platform are you using? I'm not sure whether the >server would ever make that translation -- though it might. What >happens on the local machine if you do: I am on RH7.2 IDS 9.30.UC2E3. It is bug in our application (tool is Panther): programmer doesn't check if there is value for querying date column so when the date value is not set it produces SELECT statement with empty string. Problem is that there is a lot of places in our application with that bug and everything is working on "local" connection. So I tested that in dbaccess. Here is "copy" of my screen: MODIFY: ESC = Done editing CTRL-A = Typeover/Insert CTRL-R = Redraw CTRL-X = Delete character CTRL-D = Delete rest of line ----------------------- hptdb@odin_tcp --------- Press CTRL-W for Help -------- select * from systables where created=''; select * from hptdb@zgdp2tcp:systables where created=''; First statement returns "No rows found" and second returns "1206: Invalid day in date" (I have DBDATE=DMY4. When I unset DBDATE error is 1205: Invalid month in date). Error happened because statement is translated on remote server to: select ....... where creates=DATE('NULL') It happenes with integers, smallints, decs.. but not in inserts. There are no errors in for example: insert into db1@remoteinf1:tab1(date1) values('') but there is error in: select * from db1@remotein1:tab1 where date1='' > >SELECT * FROM Systables WHERE Created = DATE('NULL');> >What about DATE(NULL)? What about DATE('')? > >Why didn't you write "AND danas IS NULL" in the first place to avoid >the angst of repeated postings? As I say ------------------------------------ Remove spam block (DELETE) to reply