[Seasar-user:16433] Re: Can DBflute support SQL functions

thomas [E-MAIL ADDRESS DELETED]
2008年 12月 3日 (水) 10:51:27 JST 

kubo さん              Thank you for your kind reply...
I got solution for my problem after your instrunctions....


On Tue, Dec 2, 2008 at 6:02 PM, kubo <[E-MAIL ADDRESS DELETED]> wrote:

> 久保(jflute)です。
>
> Hello, John
>
> > I am new to dbflute.
> Thank you for your using DBFlute!
>
> I answer by both English and Japanese
> because I am NOT good at English.
> So if you have someone who understands Japanese,
> please request him to read the Japanese answer.
>
>
> [English Answer]
> The creating SQL functions in outside-SQL is unsupported now.
> ('creating' means DDL 'CREATE OR REPLACE FUNCTION')
>
> Which is your purpose about SQL functions in outside-SQL
>  Creating or Calling?
>
> If it's Calling(Maybe I think it's Calling),
> Please create the SQL function at the timing of creating database schema?
>
> 1. prepare DDL contains SQL functions
> 2. run ReplaceSchema
> 3. run JDBC and Generate and Sql2Entity
> 4. call SQL function by behavior's outside-SQL
>
> Please check out 'dbflute-postgresql-example' from SVN repogitry.
> And refer to it. This example project has the example of the SQL functions
> for PostgreSQL.
> https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example
>
> A. Please watch the SQL(DDL) for replace-schema that contains creating
> SQL functions.
>
> https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/dbflute_exampledb/playsql/replace-schema-vendorcheck.sql
>
> B. Please watch the setting for stored procedures of outside-SQL.
>
> https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/dbflute_exampledb/dfprop/outsideSqlDefinitionMap.dfprop
>
> C. Please watch the class 'VendorCheckTest.java' that contains calling
> SQL functions.
>
> https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/src/test/java/com/example/dbflute/postgresql/dbflute/various/VendorCheckTest.java
>
> If you have doubt about detail again, please question again.
>
>
> [Japanese Answer]
> 現在は外だしSQLの中でファンクションを作成することはサポートされません。
> (ここでいう作成するというのは'CREATE OR REPLACE FUNCTION'のDDL文ことです)
>
> 外だしSQLの中でファンクションを扱う目的は、
> 「作成すること」でしょうか？それとも「呼び出すこと」でしょうか？
>
> もし、「呼び出すこと」なのであれば(多分「呼び出すこと」だと思いますが)、
> データベーススキーマを作成する時点でそのファンクションを作成して下さい。
>
> 1. ファンクションを含んだDDLを準備
> 2. ReplaceSchemaを実行
> 3. JDBCとGenerateとSql2Entityを実行
> 4. Behaviorのoutside-SQLからファンクションを呼び出す
>
> SVNリポジトリから'dbflute-postgresql-example'をチェックアウトしてそれを参考にして下さい。
> このExampleプロジェクトにはPostgreSQLのファンクションに関するExampleがあります。
> https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example
>
> 1. ファンクションを作成しているReplaceSchema用のSQL(DDL)をご覧ください。
>
> https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/dbflute_exampledb/playsql/replace-schema-vendorcheck.sql
>
> 2. 外だしSQLのストアドプロシージャの設定をご覧ください。
>
> https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/dbflute_exampledb/dfprop/outsideSqlDefinitionMap.dfprop
>
> 3. ファンクションを呼び出している'VendorCheckTest.java'クラスをご覧ください。
>
> https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/src/test/java/com/example/dbflute/postgresql/dbflute/various/VendorCheckTest.java
>
> もし、細かいところで疑問がありましたら再度ご質問ください。
>
>
>
>
> 2008/12/2 John Peter <[E-MAIL ADDRESS DELETED]>:
> > Hai all,
> >
> >           I am new to dbflute.
> > I have one doubt.can dbflute support SQL functions.
> >
> > i wrote one SQL functions,but i have to use this function in dbflute
> >
> > can any body help me in this.....
> >
> >
> > Here i attached my sql Function
> >
> >
> > -- #GrowthRateDealerFunction#
> > -- !GrowthRateDealerFunctionPmb!
> > -- !!String Nsccode!!
> > -- !!Date fromDate1!!
> > -- !!Date toDate1!!
> > -- !!Date fromDate2!!
> > -- !!Date toDate2!!
> >
> > CREATE OR REPLACE FUNCTION get_GrowthRate_dealer()
> >   RETURNS SETOF growth_dealer_type
> >   AS '
> > DECLARE
> >   growth growth_dealer_type;
> >   dealer RECORD;
> > BEGIN
> >   FOR dealer IN
> >     select distinct tdealer.dealercode from mnsc,tdealer,tdos where
> > tdealer.nsccode=mnsc.nsccode
> > and tdealer.dealercode=tdos.dealercode and
> > /*IF GrowthRateDealerFunctionPmb.nsccode!= null*/mnsc.nsccode=/*IF
> > GrowthRateDealerFunctionPmb.nsccode*/''China''
> >
> >   LOOP
> >
> >     growth.dealercode:=dealer.dealercode;
> >     growth.periodone:=(select count(*) from tdos,tdealer,mnsc,mstandard
> > where tdos.standardcode = mstandard.standardcode and
> > tdos.dealercode=tdealer.dealercode and
> > tdealer.nsccode = mnsc.nsccode and
> > tdos.dosresult = 1 and
> > tdealer.dealercode=dealer.dealercode and
> > tdos.dosnakbn = 0 and
> > tdos.dosdatersv1 between
> > /*GrowthRateDealerFunctionPmb.fromDate1*/''2006/11/1'' and
> > /*GrowthRateDealerFunctionPmb.toDate1*/ ''2007/4/1'');
> > growth.periodtwo:=(select count(*) from tdos,tdealer,mnsc,mstandard
> > where tdos.standardcode = mstandard.standardcode and
> > tdos.dealercode=tdealer.dealercode and
> > tdealer.nsccode = mnsc.nsccode and
> > tdealer.dealercode=dealer.dealercode and
> > tdos.dosresult = 1 and
> > tdos.dosnakbn = 0 and
> > tdos.dosdatersv1 between
> > /*GrowthRateDealerFunctionPmb.fromDate2*/''2007/4/1'' and
> > /*GrowthRateDealerFunctionPmb.toDate2*/''2008/11/1'');
> > if(growth.periodtwo!=0) then
> > growth.growth_rate =
> >
> (round((growth.periodtwo-growth.periodone)*100/(1.0*growth.periodtwo),0));
> > else
> > growth.growth_rate =0;
> > end if;
> >     RETURN NEXT growth;
> >   END LOOP;
> >   RETURN;
> > END;
> > ' LANGUAGE plpgsql STRICT;
> >
> > This is my SQL function.
> >
> > if i run SQL2Entity,it shows following Error..
> >
> > BUILD FAILED
> >
> C:\AgnieWork\workspace\nsdos\mydbflute\dbflute-0.8.4\build-torque.xml:323:
> > org.s
> > easar.dbflute.helper.jdbc.sqlfile.DfSQLExecutionFailureException: Look!
> Read
> > the
> >  message below.
> > /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
> > It failed to execute the SQL!
> >
> > [SQL File]
> > ..\src\main\resources\sql\growthrate\growthrateDealer.sql
> >
> > [Executed SQL]
> > -- #GrowthRateDealerFunction#
> > -- !GrowthRateDealerFunctionPmb!
> > -- !!String Nsccode!!
> > -- !!Date fromDate1!!
> > -- !!Date toDate1!!
> > -- !!Date fromDate2!!
> > -- !!Date toDate2!!
> >
> > CREATE OR REPLACE FUNCTION get_GrowthRate_dealer()
> >   RETURNS SETOF growth_dealer_type
> >   AS '
> > DECLARE
> >   growth growth_dealer_type
> >
> > [SQLState]
> > 42601
> >
> > [ErrorCode]
> > 0
> >
> > [SQLException]
> > org.postgresql.util.PSQLException
> > ERROR: unterminated quoted string at or near "'
> > DECLARE
> >   growth growth_dealer_type"
> > * * * * * * * * * */
> >
> > Thanks in advance
> > トマス
> >
> > _______________________________________________
> > Seasar-user mailing list
> > [E-MAIL ADDRESS DELETED]
> > https://ml.seasar.org/mailman/listinfo/seasar-user
> >
> >
> _______________________________________________
> Seasar-user mailing list
> [E-MAIL ADDRESS DELETED]
> https://ml.seasar.org/mailman/listinfo/seasar-user
>
-------------- next part --------------
HTMLの添付ファイルを保管しました...
URL: <http://ml.seasar.org/archives/seasar-user/attachments/20081203/67b7b115/attachment-0001.html>

Seasar-user メーリングリストの案内