Hibernate Dialect for SA12 with Wavemaker

[Former Member]

Hello,

I try to import a SA12 DB into Wavemaker with Hibernate, There seems to be problem when importing from SQL Anywhere. Hibernate interprets the foreign key as Primerykey. Does anyone have a work a round.

Thank's for help Karl

[jeff_albion]

Hi Karl,

I've noticed that you have also posted this question over here on the WaveMaker forums.

I have now tested WaveMaker 6.5.1 against SQL Anywhere 12.0.1.3797 with the 'Demo' database, via jConnect 7.0.7 ESD #4, with Glenn Paulley's Hibernate 'SQLAnywhere11Dialect' and have not seen this behaviour - the primary keys are listed correctly, with the correct foreign key mappings.

---

Here are the steps I needed to use in order to import the SQL Anywhere 12 "demo" database into WaveMaker successfully:

1. Install VMWare WaveMaker 6.5.1
2. Download jConnect 7.07 ESD #4
3. Download the SQLAnywhere 11 Hibernate Dialect (Compatible with version 12 -- note that we are still looking into releasing an updated Hibernate dialect for SQL Anywhere 12 as per the other thread)
4. Copy jconn4.jar and SQLAnywhereDialect.jar to the WaveMaker \\studio\\WEB-INF\\lib program sub-directory (e.g. C:\\Program Files\\WaveMaker\\studio\\WEB-INF\\lib)
5. Start the sample database: Programs > SQL Anywhere 12 > SQL Anywhere > Network Server Sample
6. [Note: These next two steps are required because the SQL Anywhere 'demo' database has duplicated names for foreign-key relationships, which isn't allowed while importing into WaveMaker when using the DefaultRevengNamingStrategy] Connect to the 'demo' database using Interactive SQL: dbisql -c "UID=dba;PWD=sql;DSN=SQL Anywhere 12 Demo"

7. Run the following SQL code against the demo database:

   ALTER FOREIGN KEY "FK_CustomerID_ID" ON "GROUPO"."SalesOrders" RENAME TO "FK_SO_CustomerID_ID";

   ALTER FOREIGN KEY "FK_CustomerID_ID" ON "GROUPO"."Contacts" RENAME TO "FK_C_CustomerID_ID";

   ALTER FOREIGN KEY "FK_ProductID_ID" ON "GROUPO"."MarketingInformation" RENAME TO "FK_MI_ProductID_ID";

   ALTER FOREIGN KEY "FK_ProductID_ID" ON "GROUPO"."SalesOrderItems" RENAME TO "FK_SOI_ProductID_ID";

You can now import the demo database into WaveMaker:

1. Start WaveMaker
2. Services > Import Database

[Basic Options]

• Database System: Other
• Host: localhost
• Username: dba
• Password: sql
• Database: demo

[Advanced Options]

• Service Name: demoDB
• Connection URL: jdbc:sybase:Tds:localhost:2638
• Java Package: com.demodb
• Driver Class: com.sybase.jdbc4.jdbc.SybDriver
• Dialect: org.hibernate.dialect.SQLAnywhere11Dialect
• Naming Strategy: com.wavemaker.tools.data.reveng.DefaultRevengNamingStrategy

---

Here are the rest of my general notes while working with SQL Anywhere / WaveMaker:

• The Type-2 native SQL Anywhere JDBC driver will NOT work with Wavemaker (See the WaveMaker 'UntooledDatabase' reference here and the requirement for a Type-3/Type-4 driver), and I would imagine attempting to deploy the non-native JDBC component to devices would be extremely difficult (and not supported for all deployment platforms). You will likely see this error described here instead when attempting this.
• There are some old newsgroup threads about this behaviour where I had done some experimentation with WaveMaker 3.x and SQL Anywhere 10. Thankfully, almost all of the workarounds that I had previously detailed are no longer required when working with current versions of the SQL Anywhere software
  • "Wavemaker - howto using SQL Anywhere 10"
  • "Jconnect installation fails to find isqlApp class - Wavemaker Installation"
• There are some threads in the WaveMaker community forums about working with SQL Anywhere also
  • "Native Library already loaded in another classloader (SQL Anywhere 11/12)"
  • "Questions About Wave Maker"
  • "Import Sybase ASA 9 database"

• There was an issue I had described previously with how jConnect named primary keys back to a JDBC application incorrectly - this previously required a fix to the jConnect catalog in SQL Anywhere. When encountering this error, WaveMaker would fail the import with: Error: [Import failed : Duplicate names found for primarykey.] This was fixed in CR #631443, and is present in versions jConnect 7.0 ESD #7 and up.

• The "demo" database that ships with SQL Anywhere has foreign key relationships that are given the same name (and are required to be renamed, as shown above). I have requested to change this requirement in the future via CR #725184, to be implemented in a future version of SQL Anywhere. If you have a similar situation in your database, you may have to create a new Naming Strategy to add a unique identifier to each object (prepend the table name, for instance).

I hope that helps!

[former_SQLA_member1694868]

Hi Jeff, Just to clarify, if we receive the error Import failed: Duplicate names found for primarykey. Existing name: plan_id JDBC name: start_time on table org.hibernate.mapping.Table(testdb.dbo.maint_plan_report) See wm.log for compiler output

That is fixed in CR #631143 which will be in a future build at some point correct?

[jeff_albion]

This was fixed in CR #631443, and is present in versions jConnect 7.00 ESD #7 and up.

i.e. This should be a fix for jConnect, not SQL Anywhere (and I believe it is present in all of the jConnect 7.07 builds currently posted).

See the jConnect download link.

What I can certainly attest to is that the demo database import operation was successful for me when I used jConnect 7.07 ESD #4, SQL Anywhere 12.0.1.3797, on WaveMaker 6.5.1. There may be lower combinations of versions that work, but I haven't tried other operations (my initial attempts on the 10.0.1 software were not successful, in general - but both WaveMaker and SQL Anywhere have been upgraded since then).

Which builds are you trying?

---

I also just noted that if you have deployed the MobiLink system tables to a database, this also fails the import in WaveMaker. If you're performing data modelling for a remote database application, I would generally recommend using the remote database in order to generate the WaveMaker model.

If you're trying to model a database with the MobiLink consolidated tables installed, it is possible that you will have to rename some of the foreign key relationships (as we had to do in the Demo database) due to index name duplication again.

Here's the fix for the demo database:

ALTER FOREIGN KEY "ml_database" ON "ml_server"."ml_passthrough_status" RENAME TO "ml_database_status";
ALTER FOREIGN KEY "ml_database" ON "ml_server"."ml_passthrough" RENAME TO "ml_database_passthrough";
ALTER FOREIGN KEY "ml_device" ON "ml_server"."ml_device_address" RENAME TO "ml_device_address_fk";
ALTER FOREIGN KEY "ml_passthrough_script" ON "ml_server"."ml_passthrough_repair" RENAME TO "ml_passthrough_script_repair";
ALTER FOREIGN KEY "aid" ON "ml_server"."ml_ra_agent_property" RENAME TO "aid_ml_ra_agent_property";
ALTER FOREIGN KEY "aid" ON "ml_server"."ml_ra_deployed_task" RENAME TO "aid_ml_ra_deployed_task";
ALTER FOREIGN KEY "schema_name" ON "ml_server"."ml_ra_managed_remote" RENAME TO "schema_name_ml_ra_managed_remote";
ALTER FOREIGN KEY "task" ON "ml_server"."ml_ra_deployed_task" RENAME TO "ml_ra_deployed_task_task";
ALTER FOREIGN KEY "task" ON "ml_server"."ml_ra_task_command" RENAME TO "ml_ra_task_command_task";
ALTER FOREIGN KEY "ml_script" ON "ml_server"."ml_connection_script" RENAME TO "ml_connection_script_ml_script";
ALTER FOREIGN KEY "ml_table" ON "ml_server"."ml_column" RENAME TO "ml_column_ml_table";
ALTER FOREIGN KEY "ml_script_version" ON "ml_server"."ml_column" RENAME TO "ml_column_ml_script_version";

---

As I also mentioned, if you're finding that your initial naming strategy of objects in your database has duplicate names across tables (which are not allowed by the default strategy), you may have to write your own Naming Strategy in order to resolve these issues to ensure uniqueness. (e.g. You could append the table name to each object before returning it to the framework).

Writing a new Naming Strategy is really a WaveMaker-specific issue and there are existing threads on the WaveMaker forums regarding that topic. The Naming Strategy you create will likely be specific to your existing database schema, which is why I haven't attempted writing one of my own yet to work around this issue in a generic manner (as this will affect how the database objects are named inside the framework, and is a style choice for most people).

[former_SQLA_member1694868]

I am using this combination:

WaveMaker 6.5.1

SQLA 12.0.1.3436

jConnect for JDBC - EBF 20144: 7.07 ESD #4 (there is a #5 listed but your orginal example referenced #4 so that's what I grabbed. Also I'm assuming that 7.07 esd # X is more up to date than 7.00 #7 you just referenced)

Hibernate - the file you linked in the original example.

I do not think I have any Mobilink structures in this database as it is our basic test setup.

This was really more of an exercise in curiosity. Our current web apps are hand coded php referencing the generic pdo odbc (rather than the php sql anywhere extension, as that connection was already setup before I started).

The WaveMaker tools look like they could make my life easier if A) I can get the database connection working B) WaveMaker then functions correctly Two big ifs at this point, but I'm intrigued enough by the possibility to at least check it out.

As this is my first foray into anything Java, JConnect, or Hibernate, I'm having to learn some new vocabulary just to figure out what needs to happen next, such as how to write my own "Naming Strategy."

Thanks for your help