search

dbflute / dbflute-howto

How-to project for DBFlute
Apache License 2.0
0 stars 3 forks source link

there is no insert or update bhv in dbflute1.1.3 ?? #11

Open michael2043 opened 7 years ago

michael2043 commented 7 years ago

db:postgresql9.4 dbflute1.1.3 there is no insert or update method in bhv class,how can i update data with bhv

jflute commented 7 years ago

Hello, michael.

Thank you for your trying DBFlute.

For example... If your table has no primary key, the behavior does not have insert(), update(). (because it cannot update without PK) Confirm your DB schema.

The behaviors in dbflute-howto project have primary keys. https://github.com/dbflute/dbflute-howto/blob/master/src/main/java/org/docksidestage/howto/dbflute/bsentity/BsMember.java#L37 https://github.com/dbflute/dbflute-howto/blob/master/src/main/java/org/docksidestage/howto/dbflute/bsbhv/BsMemberBhv.java#L832

michael2043 commented 7 years ago

when i add pk to a table,then execute jdbc.bat file,error message happened

[Database Product] PostgreSQL 9.4.12

[JDBC Driver] PostgreSQL JDBC Driver 42.1.1.jre6 for JDBC 4.0

[Advice] DBFlute executes the SQL to get auto-increment meta data. The table might not exist on your schema. Or the schema to be set at 'dfprop' might be mistake in the first place. (and other points can be causes, for example, authentication) So check your settings and environments.

[Table] TEST_POSTGRE.TBL_TEST.TBL_01

[PrimaryKey] COL01

[SQL for getting] select COL01 from TBL_01 where 0 = 1

jflute commented 7 years ago

Please show me TBL_01's DDL (create table TBL_01...). And DBFlute client (dbflute_xxxdb)'s littleAdjustmentMap.dfprop.

Is isTableSqlNameUpperCase true in the dfprop? PostgreSQL's table is on lower case basis, so the property should be false (commented out).

michael2043 commented 7 years ago

littleAdjustmentMap.txt ddl.txt

I tried isTableSqlNameUpperCase=false,but the same error

jflute commented 7 years ago

Thanks.

Please show me whole dbflute.log in DBFlute clinet log directory (dbflute_xxxdb/log). I will analyze the problem by the log.

There are many DBFlute users with PostgreSQL, so small settings may cause it. And test project is here: https://github.com/dbflute-test/dbflute-test-dbms-postgresql

michael2043 commented 7 years ago

dbflute (1).log.txt

jflute commented 7 years ago

Please do two action.

  1. execute this SQL by your PostgreSQL tool (not DBFlute) "select COL01 from TBL_01 where 0 = 1" error? success? (show me error message if error)

  2. Switch DBFlute engine (mydbflute/dbflute-1.1.3) to downloaded engine Download page is here, click dbflute-1.1.3.zip link: (sorry...Japanese page) http://dbflute.seasar.org/ja/environment/newest.html#java8engine

I mistook exception handling (dropped SQLException message sorry...) I fixed it so retry.

michael2043 commented 7 years ago

aaaaa... there are errors below

ERROR: relation "tbl_01" does not exist LINE 1: select COL01 FROM TBL_01 WHERE 0=1 ^

** エラー **

ERROR: relation "tbl_01" does not exist SQLステート:42P01 文字:19

michael2043 commented 7 years ago

I download the new dbflute-1.1.3.zip and run ,error below appeared ERROR: relation "tbl_01" does not exist

jflute commented 7 years ago

Thanks.

Probably you can execute it if table name is quoted...

select COL01 FROM "TBL_01" WHERE 0=1

I will try to fix DBFlute later. (Sorry, I must go work... I will implement it after finishing work)

While... PostgreSQL is lower case basis. But upper case is allowed if quoted in create table statement.

CREATE TABLE "TBL_TEST"."TBL_01"

Please try to change to lower case

CREATE TABLE tbl_test.tbl_01

If you have no special reason about upper case, I think lower case is better in PostgreSQL...

michael2043 commented 7 years ago

thank you very much for answer,but another problem happend after I change everhthing to lowercase.but jdbc.bat ok,generate.bat ok.

when i use code below Tbl01 tbl01 = new Tbl01(); tbl01.setCol01(1); tbl01.setCol02("a"); bhv.insert(tbl01); after execute,"relation "tbl_01" does not exist" happened

[SQLException] org.seasar.framework.exception.SSQLException [ESSR0072]SQLで例外(SQL=[insert into tbl_01 (col01, col02) values (?, ?)], Message=[0], ErrorCode=42P01, SQLState={3})が発生しました

[NextException] org.postgresql.util.PSQLException ERROR: relation "tbl_01" does not exist ポジション: 13

[Behavior] SampleAction.showSample():26 -> Tbl01Bhv.insert()

[Display SQL] insert into tbl_01 (col01, col02) values (1, 'a')

when i execute below with postgre tool,the same error insert into tbl_01 (col01, col02) values (1, 'a')

but execute below with postgre tool OK insert into tbl_test02.tbl_01 (col01, col02) values (1, 'a')

jflute commented 7 years ago

Table names on SQL may need to have schema prefix 'tbl_test02' in non-public schema of PostgreSQL.

Please show me your databaseInfoMap.dfprop. (Is the schema property 'tbl_test02'?)

https://github.com/dbflute-test/dbflute-test-dbms-postgresql/blob/master/dbflute_maihamadb/dfprop/databaseInfoMap.dfprop#L17

michael2043 commented 7 years ago

databaseInfoMap.dfprop.txt

even "set search_path to tbl_test02,pulibic" the same error

jflute commented 7 years ago

Thanks.

even "set search_path to tbl_test02,pulibic" the same error Does the error have its exception message on dbflute.log? (Please show me)

And show me your application database settings. The dfprop settings are for only DBFlute task, so it is not related to application runtime environment.

Application runtime environment uses DI container's settings. For example seasar: jdbc.xml spring: application.properties lasta di: [app]_env.properties

The 'tbl_test02' should be set as schema in the application settings.

michael2043 commented 7 years ago

jdbc-srapi.dicon.txt dbflute.dicon.txt

in jdbc-srapi.dicon,when i set property name="schema""tbl_test02"/property exception schemaが見つかりません

jflute commented 7 years ago

Thanks.

I'm searching PostgreSQL schema settings in jdbc.xml... but not found for now.

First solution: Make new PostgreSQL user for 'tbl_test02' schema that the user's default schema is 'tbl_test02'. And use instead of 'postgres' user.

<property name="URL">"jdbc:postgresql://xxxx.xx.xx.xx:5432/test_postgre02"</property>
<property name="user">"test02user"</property>
<property name="password">"password"</property>

(Maybe) It does not need schema prefix on SQL. You can keep SQL simple. It may be best practice when you use non-public schema...

Second solution: Set littleAdjustmentMap.dfprop's isAvailableAddingSchemaToTableSqlName true and re-genearte classes (by DBFlute task). DBFlute add schema prefix to all SQL's tables.

But SQL is complex, so this is second solution.

michael2043 commented 7 years ago

thank you very much,i try it tomorrow