search

lbitonti / liquibase-hana

Liquibase Hana extension
Apache License 2.0
11 stars 11 forks source link

How can I connect HANADB with liquibase? #7

Closed haipengchen01 closed 7 years ago

haipengchen01 commented 8 years ago

Hi everyone,

image I use this JDBC url to connect to HANADB but it returns to failure.And I don't know how can I select my schema. image Could you please me help me out?Or if you can give me some examples.

calle2010 commented 8 years ago

Hi 739823891!

This is actually not a question or even issue for Liquibase but for HANA Cloud Platform (HCP) or HANA in general, I guess.

You need to pass the database username and password, not the HCP user. The schema is not passed at the URL (currentschema) but will be the default schema of the passed user.

Bear with me if you don't use HCP but it looks like and you failed to provide any more details.

haipengchen01 commented 8 years ago

@calle2010 Actually, I've tried many different ways,but them don't work. 1.I use the database user and password image 2.I use the ngdbc.jar to connect image 3.I add user and password in jdbc url image Maybe you can show me some examples.

calle2010 commented 8 years ago

Hi,

This works for me:

$ ./liquibase --driver=com.sap.db.jdbc.Driver --classpath=ngdbc.jar:liquibase-hana.jar --changeLogFile=testcsc20.xml --url="jdbc:sap://localhost:30015/" --username=DATABASE_USER -
-password='DATABASE_PASSWORD' generateChangeLog
Liquibase 'generateChangeLog' Successful

Note how I put the password in single quotes: If you have special characters in the password this could be a problem in Bash (I'm using Git Bash on Windows).

Also note how the user/password are not appended to the URL.

I use ngdbc.jar from the SAP HANA Cloud SDK.

If you don't include the liquibase-hana.jar in the classpath you'll get a warning "liquibase: Unknown database: HDB".

So the login works.

The only problem is: The changelog is empty. I guess the extension is not yet implementing the required methods? But I haven't used generateChangeLog so far, so I'm not sure.

haipengchen01 commented 8 years ago

@calle2010 Thank you very much! I found that there is some difference between database and schema in HCP , I can connect a schema but I can't connect database.If it is schema, jdbc url will be like this: jdbc:sap://localhost:30015/?currentschema=**. image In HANA, Database is a instance, in instance a DB user will have a schema.

calle2010 commented 8 years ago

You have opened the tunnel for the database instance xs01 already. You don't append anything to the JDBC URL. The database username and password is the one you generated when you created the schema on the HANAXS instance. The schema name will be the same as the user name of this user (in my example above: DATABASE_USER)

But the real issue is: An empty changelog will be generated. With parameter --logLevel=DEBUG I get this in the output:

DEBUG 18.04.16 11:41: liquibase: Executing QUERY database command: SELECT SEQUENCE_NAME FROM SYS.SEQUENCES WHERE SCHEMA_NAME = 'DATABASE_USER'
INFO 18.04.16 11:41: liquibase: testcsc20.xml does not exist, creating
DEBUG 18.04.16 11:41: liquibase: MissingObjectChangeGenerator type order:     liquibase.structure.core.Catalog    liquibase.structure.core.Schema    liquibase.structure.c
ore.Sequence    liquibase.structure.core.StoredProcedure    liquibase.structure.core.Table    liquibase.structure.core.Column    liquibase.structure.core.PrimaryKey    li
quibase.structure.core.UniqueConstraint    liquibase.structure.core.Index    liquibase.structure.core.ForeignKey    liquibase.structure.core.View
DEBUG 18.04.16 11:41: liquibase: UnexpectedObjectChangeGenerator type order:     liquibase.structure.core.Catalog    liquibase.structure.core.ForeignKey    liquibase.stru
cture.core.Schema    liquibase.structure.core.StoredProcedure    liquibase.structure.core.UniqueConstraint    liquibase.structure.core.View    liquibase.structure.core.Ta
ble    liquibase.structure.core.PrimaryKey    liquibase.structure.core.Column    liquibase.structure.core.Index    liquibase.structure.core.Sequence
DEBUG 18.04.16 11:41: liquibase: ChangedObjectChangeGenerator type order:     liquibase.structure.core.Catalog    liquibase.structure.core.ForeignKey    liquibase.structu
re.core.Schema    liquibase.structure.core.Sequence    liquibase.structure.core.StoredProcedure    liquibase.structure.core.Table    liquibase.structure.core.Column    li
quibase.structure.core.PrimaryKey    liquibase.structure.core.UniqueConstraint    liquibase.structure.core.Index    liquibase.structure.core.View
Liquibase 'generateChangeLog' Successful
haipengchen01 commented 8 years ago

Hi calle2010 I connect a schema and generate a changelog successfully.But when I update,I got this in log:

WARNING 4/19/16 10:38 AM: liquibase: Unknown database: HDB DEBUG 4/19/16 10:38 AM: liquibase: Connected to DEV_F3A7MPFXWLT9S3JQPPYQFOS41@jdbc:sap://localhost:30015 DEBUG 4/19/16 10:38 AM: liquibase: Setting auto commit to false from true DEBUG 4/19/16 10:38 AM: liquibase: Executing QUERY database command: select count(*) from DEV_F3A7MPFXWLT9S3JQPPYQFOS41.DATABASECHANGELOGLOCK DEBUG 4/19/16 10:38 AM: liquibase: Create Database Lock Table DEBUG 4/19/16 10:38 AM: liquibase: Executing EXECUTE database command: CREATE TABLE DEV_F3A7MPFXWLT9S3JQPPYQFOS41.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED datetime, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID)) Unexpected error running Liquibase: SAP DBTech JDBC: [257](at 91): sql syntax error: this feature cannot be supported: line 1 col 91 (at pos 91) [Failed SQL: CREATE TABLE DEV_F3A7MPFXWLT9S3JQPPYQFOS41.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED datetime, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))]

SEVERE 4/19/16 10:38 AM: liquibase: SAP DBTech JDBC: [257](at 91): sql syntax error: this feature cannot be supported: line 1 col 91 (at pos 91) [Failed SQL: CREATE TABLE DEV_F3A7MPFXWLT9S3JQPPYQFOS41.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED datetime, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))] liquibase.exception.LockException: liquibase.exception.DatabaseException: SAP DBTech JDBC: [257](at 91): sql syntax error: this feature cannot be supported: line 1 col 91 (at pos 91) [Failed SQL: CREATE TABLE DEV_F3A7MPFXWLT9S3JQPPYQFOS41.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED datetime, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))] at liquibase.lockservice.StandardLockService.acquireLock(StandardLockService.java:225) at liquibase.lockservice.StandardLockService.waitForLock(StandardLockService.java:164) at liquibase.Liquibase.update(Liquibase.java:196) at liquibase.Liquibase.update(Liquibase.java:192) at liquibase.integration.commandline.Main.doMigration(Main.java:1096) at liquibase.integration.commandline.Main.run(Main.java:180) at liquibase.integration.commandline.Main.main(Main.java:99) Caused by: liquibase.exception.DatabaseException: SAP DBTech JDBC: [257](at 91): sql syntax error: this feature cannot be supported: line 1 col 91 (at pos 91) [Failed SQL: CREATE TABLE DEV_F3A7MPFXWLT9S3JQPPYQFOS41.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED datetime, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))] at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:301) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:107) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:97) at liquibase.lockservice.StandardLockService.init(StandardLockService.java:88) at liquibase.lockservice.StandardLockService.acquireLock(StandardLockService.java:198) ... 6 more Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [257](at 91): sql syntax error: this feature cannot be supported: line 1 col 91 (at pos 91) at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:345) at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:176) at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:102) at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1030) at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:820) at com.sap.db.jdbc.StatementSapDB.sendCommand(StatementSapDB.java:898) at com.sap.db.jdbc.StatementSapDB.sendSQL(StatementSapDB.java:947) at com.sap.db.jdbc.StatementSapDB.execute(StatementSapDB.java:256) at com.sap.db.jdbc.StatementSapDB.execute(StatementSapDB.java:228) at com.sap.db.jdbc.trace.Statement.execute(Statement.java:132) at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:299) ... 11 more

It seems that HANA doesn't support some SQL features that liquibase is using.

And I solve the error that I can't connect to database.The reason is I have a '! ' in my password. And I fix another issue too by updating my HANADB version,as far as I can see there is no issue anymore

calle2010 commented 8 years ago

Hi again,

no issue with Liquibase here. You have Unknown database: HDB in the output above. This means Liquibase doesn't pick up the liquibase-hana extension and generates SQL statements invalid for HANA.

I normally don't use Liquibase on the command line but embedded in the application. There we have the liquibase-hana extension jar deployed on the class path.

For the command line I used --classpath=ngdbc.jar:liquibase-hana.jar See comment above.

lbitonti commented 7 years ago

WARNING 4/19/16 10:38 AM: liquibase: Unknown database: HDB

Not a Liquibase issue I am afraid.