[seasar-dotnet:1724] Re: [dbflute Procedure Generation Error]

2010年 6月 12日 (土) 13:24:37 JST

Hello, thomas

OK, Please try DBFlute-0.9.7.1-09-SNAPSHOT
which sets java.sql.Date to a DATE type parameter
in that procedure execution process.

(In other DBMS, java.sql.Timestamp is allowed...)

And, please tell me that what is program type
on an entity class for the "date" type column
of SQLServer.

TABLE: FOO
o FOO_ID integer -> java.lang.Integer
o FOO_NAME varchar -> java.lang.String
o FOO_DATE date  -> ???
o FOO_DATETIME datetime  -> java.sql.Timestamp

If the type is not java.util.Date,
09-SNAPSHOT may not work...

2010/6/12 thomas <[E-MAIL ADDRESS DELETED]>:
> Hello Kubo さん、
>
> Good Afternoon....
>
> I tried with dbflute-0.9.7.1-08-SNAPSHOT.Now it is assigning 2010-03-31
> 12:34:56.0 for date format.
>
> I think in Sqlserver the date format is different from datetime format.So
> its giving following exception.
>
> 2010-06-12 12:07:41,937 [main] INFO
> (DfProcedureExecutionMetaExtractor#doExtractExecutionMetaData():109) -
> ...Calling: {? = call PMS_DRP_MaintainDeliveryData_Tran_SP(?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?)}
> 2010-06-12 12:07:41,953 [main] INFO
> (DfProcedureExecutionMetaExtractor#doExtractExecutionMetaData():132) -   (x)
> retry: ? = call PMS_DRP_MaintainDeliveryData_Tran_SP(?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?)
> 2010-06-12 12:07:41,953 [main] INFO
> (DfProcedureExecutionMetaExtractor#doExtractExecutionMetaData():186) -
> *Failed to execute the procedure for getting meta data:
>  {? = call PMS_DRP_MaintainDeliveryData_Tran_SP(?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?)}
>    @RETURN_VALUE: int(10) as Return
>    @User_Id: varchar(10) as In
>    @Customer_Code: varchar(6) as In
>    @Delivery_Port_No: varchar(3) as In
>    @Part_Code: varchar(34) as In
>    @Delivery_Truck_No: varchar(6) as In
>    @Delivery_Date: date(10) as In
>    @Delivery_Time: varchar(20) as In
>    @Departure_Time: varchar(20) as In
>    @Port_Close_Time: varchar(20) as In
>    @Port_Open_DateTime: varchar(20) as In
>    @modeflag: char(1) as In
>    @sid: int(10) as In
>  test values = [0, 0, 0, 0, 0, 2010-03-31 12:34:56.0, 0, 0, 0, 0, 0, 0]
>  Conversion failed when converting date and/or time from character string.
>
> Thanks,
> Thomas
>
>
> On Fri, Jun 11, 2010 at 6:30 PM, kubo <[E-MAIL ADDRESS DELETED]> wrote:
>>
>> Good evening, thomas
>>
>> Thank you for your showing me log messages.
>> I see, you use SQLServer 2008.
>> The type "date" is new data type from 2008.
>>
>> Please try DBFlute-0.9.7.1-08-SNAPSHOT
>> which is fixed about it.
>> http://dbflute.sandbox.seasar.org/ja/environment/newest.html
>>
>> However, my environment is SQLServer 2005.
>> So I cannot test it. (cannot use "date" type)
>> Please try it and then after tell me its result.
>>
>> Thanks
>> Kubo (jflute)
>>
>>
>> 2010/6/11 thomas <[E-MAIL ADDRESS DELETED]>:
>> > Hello Kubo さん、
>> >
>> > In the following link its mentioned that the test values for date type
>> > is
>> > passed as
>> > '2010-03-31 12:34:56'
>> >
>> >
>> > http://dbflute.sandbox.seasar.org/ja/manual/function/generator/task/sql2entity/procedurepmb.html#resultsethandling
>> >
>> >
>> > when i run Sql2Entity its trying to assign 0 for date parameter.so i got
>> > "converting data type nvarchar to date" Error.
>> >
>> > Is this my procedure problem?Or i have to do some other settings?
>> >
>> > Thanks,
>> > Thomas
>> >
>> >
>> >
>> > 2010-06-11 14:56:41,578 [main] INFO
>> > (DfProcedureExecutionMetaExtractor#doExtractExecutionMetaData():100) -
>> > ...Calling: {? = call PMS_COM_MONTH_CLOSE_CHECK_FN(?, ?, ?)}
>> > 2010-06-11 14:56:41,578 [main] INFO
>> > (DfProcedureExecutionMetaExtractor#doExtractExecutionMetaData():123) -
>> > (x)
>> > retry: ? = call PMS_COM_MONTH_CLOSE_CHECK_FN(?, ?, ?)
>> > 2010-06-11 14:56:41,578 [main] INFO
>> > (DfProcedureExecutionMetaExtractor#doExtractExecutionMetaData():177) -
>> > *Failed to execute the procedure for getting meta data:
>> >  {? = call PMS_COM_MONTH_CLOSE_CHECK_FN(?, ?, ?)}
>> >    @RETURN_VALUE: int(10) as Return
>> >    @Date: date(10) as In
>> >    @Fiscal_year: int(10) as In
>> >    @Fiscal_month: int(10) as In
>> >  test values = [0, 0, 0]
>> >  Error converting data type nvarchar to date.
>> >
>> >
>> >
>> >
>> > On Fri, Jun 11, 2010 at 2:40 PM, kubo <[E-MAIL ADDRESS DELETED]> wrote:
>> >>
>> >> Good afternoon, thomas
>> >>
>> >> Success:
>> >>
>> >>  DBFLUTE_CLIENT: {dfclient}
>> >>    database  = mssql (Microsoft SQL Server 10.00.1600)
>> >>
>> >> Failure:
>> >>
>> >>  DBFLUTE_CLIENT: {dfclient}
>> >>    database  = h2 (Microsoft SQL Server 10.00.1600)
>> >>
>> >> Change the database type "h2 to mssql"
>> >> at "dfprop/basicInfoMap.dfprop".
>> >>
>> >> And when you upgrade DBFlute,
>> >> you don't need to remake DBFlute client directory.
>> >>
>> >> {Pattern 1}
>> >> /= = = = = = = = = = = = = = = = = = = = = = = = = =
>> >> dbflute_dfclient
>> >>  |-dfprop             // no change
>> >>  |-_project.bat     // *change the path to new version
>> >>  |-_project.sh      // *change the path to new version
>> >>  |-...others          // no change
>> >>
>> >> mydbflute
>> >>  |-dbflute-0.9.7.1-06-SNAPSHOT
>> >>  |-dbflute-0.9.7.1-07-SNAPSHOT // *put new module here
>> >>
>> >> ex) _project.sh
>> >> export DBFLUTE_HOME=../mydbflute/dbflute-0.9.7.1-06-SNAPSHOT
>> >>  to
>> >> export DBFLUTE_HOME=../mydbflute/dbflute-0.9.7.1-07-SNAPSHOT
>> >> = = = = = = = = = =/
>> >>
>> >> {Pattern 2}
>> >> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> >> dbflute_dfclient
>> >>  |-dfprop             // no change
>> >>  |-_project.bat     // no change (to 0.9.7.1)
>> >>  |-_project.sh      // no change (to 0.9.7.1)
>> >>  |-...others          // no change
>> >>
>> >> mydbflute
>> >>  |-dbflute-0.9.7.1 // *override all files
>> >> = = = = = = = = = =/
>> >>
>> >> 2010/6/11 thomas <[E-MAIL ADDRESS DELETED]>:
>> >> > Hello Kubo さん、
>> >> >
>> >> > I downloaded the DBFlute-0.9.7.1-07-SNAPSHOT and i tried,its not
>> >> > connected
>> >> > to the database itself.
>> >> >
>> >> > while running jdbc.bat file the connection failed.
>> >> >
>> >> > If i use DBFlute-0.9.7.1-06-SNAPSHOT it is connecting.I am using same
>> >> > connection String
>> >> >
>> >> > jdbc:sqlserver://localhost;DatabaseName=MYTEST
>> >> >
>> >> > and i am using sqljdbc4.jar.
>> >> >
>> >> > I have attached the log file for both versions.
>> >> >
>> >> > What i am doing wrong here?
>> >> >
>> >> > Thanks,
>> >> > Tomas.
>> >> >
>> >> > 2010/6/11 kubo <[E-MAIL ADDRESS DELETED]>
>> >> >>
>> >> >> Good morning, thomas
>> >> >>
>> >> >> I've got it! Thank you.
>> >> >> I remembered SQLServer's function
>> >> >> and it can return a table type.
>> >> >>
>> >> >> I could get the same exception in my environment.
>> >> >> I'm surprised:
>> >> >>
>> >> >> The method, getString("DATA_TYPE")
>> >> >> of DatabaseMetaData.getProcedureColumns(),
>> >> >> throws NumberFormatException from JDBC driver
>> >> >> (getInt("DATA_TYPE") throws NullPointerException)
>> >> >> if it's SQLServer's function that returns a table type.
>> >> >>
>> >> >> Please use DBFlute-0.9.7.1-07-SNAPSHOT
>> >> >> which is fixed about it.
>> >> >> (if the Exception occurred, DBFlute ignores it)
>> >> >> And also DBFlute.NET-0.8.9.17-07-SNAPSHOT
>> >> >> is fixed about it.
>> >> >>
>> >> >> By the way, I remembered how to call procedures through outside-SQL.
>> >> >> If a procedure has no OUT parameter, you can call like this:
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >> https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-sqlserver-example/src/main/resources/com/example/dbflute/sqlserver/dbflute/exbhv/various/vendorcheck/VendorCheckBhv_callProcReturnResultSetMore.sql
>> >> >> /= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> >> >> -- #df:entity#
>> >> >>
>> >> >> -- !df:pmb!
>> >> >> -- !!String memberStatusCode:cls(MemberStatus)!!
>> >> >>
>> >> >> SP_RETURN_RESULT_SET_MORE @statusCode=/*$pmb.memberStatusCode*/'WDL'
>> >> >> = = = = = = = = = =/
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >> https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-sqlserver-example/src/main/resources/com/example/dbflute/sqlserver/dbflute/exbhv/various/vendorcheck/VendorCheckBhv_selectSimpleTableFunction.sql
>> >> >> /= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> >> >> -- #df:entity#
>> >> >>
>> >> >> -- !df:pmb!
>> >> >> -- !!String memberStatusCode:cls(MemberStatus)!!
>> >> >>
>> >> >> select * from FN_RETURN_TABLE(/*pmb.memberStatusCode*/'FML')
>> >> >> = = = = = = = = = =/
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >> https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-sqlserver-example/src/test/java/com/example/dbflute/sqlserver/dbflute/topic/ProcedureTest.java
>> >> >> /= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> >> >> String path =
>> >> >> VendorCheckBhv.PATH_various_vendorcheck_callProcReturnResultSetMore;
>> >> >> ProcReturnResultSetMorePmb pmb = new ProcReturnResultSetMorePmb();
>> >> >> pmb.setMemberStatusCode_Formalized();
>> >> >> Class<ProcReturnResultSetMore> entityType =
>> >> >> ProcReturnResultSetMore.class;
>> >> >>
>> >> >> ListResultBean<ProcReturnResultSetMore> memberList
>> >> >>    = vendorCheckBhv.outsideSql().selectList(path, pmb, entityType);
>> >> >>
>> >> >> // If you don't use OUT parameter, you can call procedures like
>> >> >> this.
>> >> >> // But you cannot get second result set. (first result set only)
>> >> >> = = = = = = = = = =/
>> >> >> /= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> >> >> String path =
>> >> >> VendorCheckBhv.PATH_various_vendorcheck_callProcReturnResultSetMore;
>> >> >> SimpleTableFunctionPmb pmb = new SimpleTableFunctionPmb();
>> >> >> pmb.setMemberStatusCode_Formalized();
>> >> >> Class<SimpleTableFunction> entityType = SimpleTableFunction.class;
>> >> >>
>> >> >> // ## Act ##
>> >> >> ListResultBean<SimpleTableFunction> memberList =
>> >> >> vendorCheckBhv.outsideSql().selectList(path, pmb, entityType);
>> >> >> = = = = = = = = = =/
>> >> >>
>> >> >> However I didn't confirm it in DBFlute.NET environment.
>> >> >> (It works in DBFlute(Java) environment)
>> >> >>
>> >> >>
>> >> >> Thanks,
>> >> >> Kubo (jflute)
>> >> >> _______________________________________________
>> >> >> seasar-dotnet mailing list
>> >> >> [E-MAIL ADDRESS DELETED]
>> >> >> https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>> >> >
>> >> >
>> >> > _______________________________________________
>> >> > seasar-dotnet mailing list
>> >> > [E-MAIL ADDRESS DELETED]
>> >> > https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>> >> >
>> >> >
>> >> _______________________________________________
>> >> seasar-dotnet mailing list
>> >> [E-MAIL ADDRESS DELETED]
>> >> https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>> >
>> >
>> > _______________________________________________
>> > seasar-dotnet mailing list
>> > [E-MAIL ADDRESS DELETED]
>> > https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>> >
>> >
>> _______________________________________________
>> seasar-dotnet mailing list
>> [E-MAIL ADDRESS DELETED]
>> https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>
>
> _______________________________________________
> seasar-dotnet mailing list
> [E-MAIL ADDRESS DELETED]
> https://ml.seasar.org/mailman/listinfo/seasar-dotnet
>
>