Topic
  • 7 replies
  • Latest Post - ‏2013-06-21T18:28:41Z by KshitijRaval
KshitijRaval
KshitijRaval
5 Posts

Pinned topic Strange build time error with SQL Call while having date as a parameter

‏2013-06-09T07:01:56Z |

We are facing a strange build time error with SQL Call while having date as a parameter.  Similar query (1) on a different SQL Call works and (2) lets says if we remove 'in" clause from the problematic SQL Call builder then also it works OR (3) lets say if we hard code the dates as shown in the query below then it works.  How come same date parameter would work for one parameter and not for another!

We deleted this SQL Call and created again, did regen, also did clean, build and deploy, we even recreated the whole project. - but nothing has worked.  We fail to understand whats going on here.

Details:

SQL Call Builder


Query as mentioned in SQL Statement section:

select A.MON, A.YR, A.SLA, A.severity,
case A.MON when 'JAN' Then 1 when 'FEB' Then 2 when 'MAR' Then 3 when 'APR' Then 4 when 'MAY' Then 5 when 'JUN' Then 6
when 'JUL' Then 7 when 'AUG' Then 8 when 'SEP' Then 9 when 'OCT' Then 10 when 'NOV' Then 11 when 'DEC' Then 12 End indx
from  
(select month1 MON, year1 YR, cast(sum(TOTALSRWITHSLA) as double)/cast(sum(TOTALSRRESOLVED) as double) * 100 SLA, SRSEVLEVEL severity
from TACSCHEMA.CATCMASTER de where (MONTH1 = upper(substr(monthname(date('2013-03-01')),1,3)) or
MONTH1 = upper(substr(monthname(date('2013-03-01') - 1 months),1,3)) or
MONTH1 = upper(substr(monthname(date('2013-03-01') - 2 months),1,3))) and
year1 in (year(current date), year(date('2013-03-01') - 1 years)) AND  trim(de.SECTOR) in (select distinct trim(SECTORALIAS) from TACSCHEMA.SECTORMASTER where
SECTOR_ID=?)
group by MONTH1, year1, SRSEVLEVEL having sum(TOTALSRWITHSLA)>0) as A where A.YR = year(date('2013-03-01')) or (A.YR = year(date(''|| ? ||'') - 1 years ) and A.MON in ('JAN','FEB'))
order by A.YR, indx, A.severity

We are passing date and other values in Parameters section as shown in the attached image

For this query we are getting build time error in general.txt when we click on Save:

[6/9/13 12:16:13:735 IST] 00000035 SystemOut O *-- TIME: [2013-06-09 12:16:13,735] --*
Category: bowstreet.system.server.logging.event.criterion.abnormal
Priority: ERROR
Msg: Component: MODEL
com.ibm.db2.jcc.b.jm: The syntax of the string representation of a datetime value is incorrect.. SQLCODE=-180, SQLSTATE=22007, DRIVER=3.50.152
at com.ibm.db2.jcc.b.wc.a(wc.java:571)
at com.ibm.db2.jcc.b.wc.a(wc.java:57)
at com.ibm.db2.jcc.b.wc.a(wc.java:126)
at com.ibm.db2.jcc.b.jk.b(jk.java:3578)
at com.ibm.db2.jcc.b.jk.a(jk.java:3560)
at com.ibm.db2.jcc.t4.db.a(db.java:730)
at com.ibm.db2.jcc.t4.db.m(db.java:719)
at com.ibm.db2.jcc.t4.db.i(db.java:244)
at com.ibm.db2.jcc.t4.db.c(db.java:52)
at com.ibm.db2.jcc.t4.t.c(t.java:44)
at com.ibm.db2.jcc.t4.sb.i(sb.java:153)
at com.ibm.db2.jcc.b.tk.P(tk.java:1438)

Please do let me know if you need any other information to help you, help us.  We are using WEF V8.0. Thanks.

  • DGawron
    DGawron
    580 Posts
    ACCEPTED ANSWER

    Re: Strange build time error with SQL Call while having date as a parameter

    ‏2013-06-11T17:22:18Z  

    We see this error in the Designer when we mention the 4th date parameter and click on Apply / Save button.  Designer is not free of error if we want to specify 4th date parameter and onwards rest of the parameters.  Only if we remove those rest of the ? marks in the query and hard code them with dates - only then Designer becomes error free

    Have you used the Alt SQL input as recommended?  If not, then try that.  As I mentioned earlier SQL Call has some difficulty generating a schema from SQL that uses positional parameters that have a date type.  The Alt SQL input lets you work around this.

    When using the Alt SQL input the builder doesn't care if the number of positional parameters declared in the SQL Statement input matches the number of specified parameters.  It will try to keep the two in sync in some cases, but if they are out of sync it should only emit a warning at regen.

  • DGawron
    DGawron
    580 Posts

    Re: Strange build time error with SQL Call while having date as a parameter

    ‏2013-06-10T23:37:38Z  

    The sample SQL and pictures appear to show that you have specified too many positional parameters.  I counted only two actual parameters in the SQL statement, but the parameters builder input picture shows 3.  These need to match.

    There is no "build time" in WEF.  You probably mean regen if the error is from a Designer regen.  If this is the case, then the most likely reason regen is failing with the one version of the SQL is because SQL Call has a problem automatically generating an XSD  schema for the result when there is a date as a positional parameter.  The way to work around this is to use the Alternative SQL Statement input and provide a simplified SQL statement without any positional parameters.  The simplified SQL needs to generate the same result set.

  • KshitijRaval
    KshitijRaval
    5 Posts

    Re: Strange build time error with SQL Call while having date as a parameter

    ‏2013-06-11T11:43:24Z  
    • DGawron
    • ‏2013-06-10T23:37:38Z

    The sample SQL and pictures appear to show that you have specified too many positional parameters.  I counted only two actual parameters in the SQL statement, but the parameters builder input picture shows 3.  These need to match.

    There is no "build time" in WEF.  You probably mean regen if the error is from a Designer regen.  If this is the case, then the most likely reason regen is failing with the one version of the SQL is because SQL Call has a problem automatically generating an XSD  schema for the result when there is a date as a positional parameter.  The way to work around this is to use the Alternative SQL Statement input and provide a simplified SQL statement without any positional parameters.  The simplified SQL needs to generate the same result set.

    So in the SQL Call builder under "Schema Generation" we'll select "From Alternate SQL Statement" and then

    under Alternate SQL we'll pass the actual parameterized queries (with ? marks)

    and in SQL Statement section we'll have a simple (yet similar) query so that same schema is generated, right?

    Now in this case how are the parameters passed to query in Alternate SQL?

     

    Regarding mismatch in parameters that is exactly a strange issue we are facing: Query has several ? marks and when we mention the 4th date parameter in Parameter section below we start getting that error - so we are unable to enter more parameters which are required there.

    More strange because similar query is working fine - just that it is from a different table.  We checked the data type of both the tables - structure / data type of both tables are same.  Attaching table structure for reference.

  • DGawron
    DGawron
    580 Posts

    Re: Strange build time error with SQL Call while having date as a parameter

    ‏2013-06-11T14:59:48Z  

    So in the SQL Call builder under "Schema Generation" we'll select "From Alternate SQL Statement" and then

    under Alternate SQL we'll pass the actual parameterized queries (with ? marks)

    and in SQL Statement section we'll have a simple (yet similar) query so that same schema is generated, right?

    Now in this case how are the parameters passed to query in Alternate SQL?

     

    Regarding mismatch in parameters that is exactly a strange issue we are facing: Query has several ? marks and when we mention the 4th date parameter in Parameter section below we start getting that error - so we are unable to enter more parameters which are required there.

    More strange because similar query is working fine - just that it is from a different table.  We checked the data type of both the tables - structure / data type of both tables are same.  Attaching table structure for reference.

    You have the first part reversed.  The simplified SQL without parameters goes into the Alt SQL input; this statement will be the one used for generating the XSD schema at Designer regen-time.  The actual parameterized SQL to be executed at run-time on the server is always specified in the SQL Statement input.

    For the second part when you are seeing the error?  Is it in Designer from a regen, on the server from server regen, or at run-time when the model is running and trying to execute the parameterized SQL?  Assuming that regen in Designer is free of errors I would expect the error to be from the run-time indicating you may be passing an incorrectly formatted date as one of the parameters.

  • DGawron
    DGawron
    580 Posts

    Re: Strange build time error with SQL Call while having date as a parameter

    ‏2013-06-11T15:01:39Z  

    So in the SQL Call builder under "Schema Generation" we'll select "From Alternate SQL Statement" and then

    under Alternate SQL we'll pass the actual parameterized queries (with ? marks)

    and in SQL Statement section we'll have a simple (yet similar) query so that same schema is generated, right?

    Now in this case how are the parameters passed to query in Alternate SQL?

     

    Regarding mismatch in parameters that is exactly a strange issue we are facing: Query has several ? marks and when we mention the 4th date parameter in Parameter section below we start getting that error - so we are unable to enter more parameters which are required there.

    More strange because similar query is working fine - just that it is from a different table.  We checked the data type of both the tables - structure / data type of both tables are same.  Attaching table structure for reference.

    One more thought.  You should turn on SQL logging in the builder so you can see exactly what SQL is being executed at run-time and the actual values passed to the builder for all of the positional parameters.

  • KshitijRaval
    KshitijRaval
    5 Posts

    Re: Strange build time error with SQL Call while having date as a parameter

    ‏2013-06-11T16:45:18Z  
    • DGawron
    • ‏2013-06-11T14:59:48Z

    You have the first part reversed.  The simplified SQL without parameters goes into the Alt SQL input; this statement will be the one used for generating the XSD schema at Designer regen-time.  The actual parameterized SQL to be executed at run-time on the server is always specified in the SQL Statement input.

    For the second part when you are seeing the error?  Is it in Designer from a regen, on the server from server regen, or at run-time when the model is running and trying to execute the parameterized SQL?  Assuming that regen in Designer is free of errors I would expect the error to be from the run-time indicating you may be passing an incorrectly formatted date as one of the parameters.

    We see this error in the Designer when we mention the 4th date parameter and click on Apply / Save button.  Designer is not free of error if we want to specify 4th date parameter and onwards rest of the parameters.  Only if we remove those rest of the ? marks in the query and hard code them with dates - only then Designer becomes error free

  • DGawron
    DGawron
    580 Posts

    Re: Strange build time error with SQL Call while having date as a parameter

    ‏2013-06-11T17:22:18Z  

    We see this error in the Designer when we mention the 4th date parameter and click on Apply / Save button.  Designer is not free of error if we want to specify 4th date parameter and onwards rest of the parameters.  Only if we remove those rest of the ? marks in the query and hard code them with dates - only then Designer becomes error free

    Have you used the Alt SQL input as recommended?  If not, then try that.  As I mentioned earlier SQL Call has some difficulty generating a schema from SQL that uses positional parameters that have a date type.  The Alt SQL input lets you work around this.

    When using the Alt SQL input the builder doesn't care if the number of positional parameters declared in the SQL Statement input matches the number of specified parameters.  It will try to keep the two in sync in some cases, but if they are out of sync it should only emit a warning at regen.

  • KshitijRaval
    KshitijRaval
    5 Posts

    Re: Strange build time error with SQL Call while having date as a parameter

    ‏2013-06-21T18:28:41Z  
    • DGawron
    • ‏2013-06-11T17:22:18Z

    Have you used the Alt SQL input as recommended?  If not, then try that.  As I mentioned earlier SQL Call has some difficulty generating a schema from SQL that uses positional parameters that have a date type.  The Alt SQL input lets you work around this.

    When using the Alt SQL input the builder doesn't care if the number of positional parameters declared in the SQL Statement input matches the number of specified parameters.  It will try to keep the two in sync in some cases, but if they are out of sync it should only emit a warning at regen.

    Yes we used the Alt SQL as you recommended and it worked.  Thanks.