[Seasar-user:16427] Re: Can DBflute support SQL functions
kubo
[E-MAIL ADDRESS DELETED]
2008年 12月 2日 (火) 18:02:11 JST
久保(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 メーリングリストの案内