Configuration Problem with oracle database

[anjumsohail]

Plz help, i read all threads and attempted many options but error persists.

<entry key='database.driverFile'>C:\Program Files\Traccar\lib\ojdbc7.jar</entry>
<entry key='database.driver'>oracle.jdbc.OracleDriver</entry>
<entry key='database.url'>jdbc:oracle:thin:trcaa/trcaa@localhost:1521:trcaa</entry>
<entry key='database.user'>trcaa</entry>
<entry key='database.password'>trcaa</entry>

2019-12-10 00:24:42 ERROR: Main method error - 
java.lang.reflect.InaccessibleObjectException: Unable to make void jdk.internal.loader.ClassLoaders$AppClassLoader.appendToClassPathForInstrumentation(java.lang.String) accessible: module java.base does not "opens jdk.internal.loader" to unnamed module @71d06a3
	at java.base/java.lang.reflect.AccessibleObject.checkCanSetAccessible(AccessibleObject.java:340)
	at java.base/java.lang.reflect.AccessibleObject.checkCanSetAccessible(AccessibleObject.java:280)
	at java.base/java.lang.reflect.Method.checkCanSetAccessible(Method.java:198)
	at java.base/java.lang.reflect.Method.setAccessible(Method.java:192)
	at org.traccar.database.DataManager.initDatabase(DataManager.java:113)
	at org.traccar.database.DataManager.<init>(DataManager.java:89)
	at org.traccar.Context.init(Context.java:292)
	at org.traccar.Main.run(Main.java:110)
	at org.traccar.Main$1.run(Main.java:88)
	at org.traccar.WindowsService$ServiceMain.callback(WindowsService.java:191)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at com.sun.jna.CallbackReference$DefaultCallbackProxy.invokeCallback(CallbackReference.java:520)
	at com.sun.jna.CallbackReference$DefaultCallbackProxy.callback(CallbackReference.java:551)

2019-12-10 00:24:42  WARN: JNA: Callback org.traccar.WindowsService$ServiceMain@15d49048 threw the following exception - 
java.lang.RuntimeException: java.lang.reflect.InaccessibleObjectException: Unable to make void jdk.internal.loader.ClassLoaders$AppClassLoader.appendToClassPathForInstrumentation(java.lang.String) accessible: module java.base does not "opens jdk.internal.loader" to unnamed module @71d06a3
	at org.traccar.Main.run(Main.java:152)
	at org.traccar.Main$1.run(Main.java:88)
	at org.traccar.WindowsService$ServiceMain.callback(WindowsService.java:191)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at com.sun.jna.CallbackReference$DefaultCallbackProxy.invokeCallback(CallbackReference.java:520)
	at com.sun.jna.CallbackReference$DefaultCallbackProxy.callback(CallbackReference.java:551)
Caused by: java.lang.reflect.InaccessibleObjectException: Unable to make void jdk.internal.loader.ClassLoaders$AppClassLoader.appendToClassPathForInstrumentation(java.lang.String) accessible: module java.base does not "opens jdk.internal.loader" to unnamed module @71d06a3
	at java.base/java.lang.reflect.AccessibleObject.checkCanSetAccessible(AccessibleObject.java:340)
	at java.base/java.lang.reflect.AccessibleObject.checkCanSetAccessible(AccessibleObject.java:280)
	at java.base/java.lang.reflect.Method.checkCanSetAccessible(Method.java:198)
	at java.base/java.lang.reflect.Method.setAccessible(Method.java:192)
	at org.traccar.database.DataManager.initDatabase(DataManager.java:113)
	at org.traccar.database.DataManager.<init>(DataManager.java:89)
	at org.traccar.Context.init(Context.java:292)
	at org.traccar.Main.run(Main.java:110)
	... 8 more

[anjumsohail]

https://www.codejava.net/java-se/jdbc/connect-to-oracle-database-via-jdbc

[Nitishkhadaria]

@anjumsohail I am describing all steps for Configuration Traccar with Oracle Database. These steps are working for me. I would love if this information comes in handy
Step 1: Edit pom.xml
-- Add dependency of OJDBC 7 is working fine with Traccar

	<dependency>
	    <groupId>com.oracle</groupId>
	    <artifactId>ojdbc7</artifactId>
	    <version>12.1.0.2</version>
	</dependency>

Step 2: Edit in debug.xml and traccar.xml

<entry key="database.driver">oracle.jdbc.OracleDriver</entry>
<entry key='database.url'>jdbc:oracle:thin:@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx</entry> 
<entry key='database.user'>user</entry>
<entry key='database.password'>passs</entry>

Step 3 : Edit DataManager.java
Already Code :-
hikariConfig.setConnectionInitSql(config.getString("database.checkConnection", "SELECT 1"));
Edited Code :-
hikariConfig.setConnectionInitSql(config.getString("database.checkConnection", "SELECT 1 from dual"));

Step 4: Now we need to change Many Schema/Changelog files

Step 4.1 Change size of VARCHAR in 4000

Already code - <column name="attributes" type="VARCHAR(4096)">
Edited Code - <column name="attributes" type="VARCHAR(4000)"> 

For all Column

Step 4.2 Change Length of constraints Name ( Oracle maximum constraints name length is 30.)

Already Code:- 
constraintName="fk_device_notification_deviceid" (length 31)
Edited Code:- 
constraintName="fk_device_notifiction_deviceid" (now Length 30)

For all ConstraintName.

Step 4.3 Remove "autoIncrement="true" " because its not support in oracle

Step 4.4 Create Sequence for all table that has an auto-increment. In Traccar we need to declare tow types of Sequence.
a) Sequence for that table those insert value then the Traccar server will be started.

<createSequence sequenceName="seq_user" startValue="1" incrementBy="1" />
<createSequence sequenceName="seq_server" startValue="1" incrementBy="1" />

Bind with Table

<insert tableName="tc_users">
      **<column name="id" valueComputed="seq_user.NEXTVAL"/>**
      <column name="name" value="admin" />
      <column name="email" value="admin" />
      <column name="hashedpassword" value="D33DCA55ABD4CC5BC76F2BC0B4E603FE2C6F61F4C1EF2D47" />
      <column name="salt" value="000000000000000000000000000000000000000000000000" />
      <column name="administrator" valueBoolean="true" />
    </insert>

b) We need also to declare Sequence for all that table whose Insert after start the traccar server.

<createSequence incrementBy="1" startValue="1000"
			sequenceName="seq_tc_calendars" />
		<sql endDelimiter="/">
			CREATE OR REPLACE TRIGGER TRIGGER_calendars
			BEFORE INSERT ON tc_calendars
			FOR EACH ROW
			WHEN (new.ID IS NULL)
			BEGIN
			:new.ID := seq_tc_calendars.NEXTVAL;
			END;
		</sql>

Step 5: Most important Change in Java Side.
In Traaccar if I will add a new driver then the driver id also insert into the tc_user_driver.
so we need a change in QueryBuilder.java
Method name : executeUpdate(); in QueryBuilder Class
getGeneratedKeys() method of JDBC is not working in Oracle so change into executeQuery(); method of JDBC. executeQuery() return last insert value.

Example: In my code, I have create a new method name with executeUpdate(Class<?> class) in QueryBuilder.java

public long executeUpdate(Class<?> clazz) throws SQLException {
		if (query != null) {
			try {
				statement.execute();
				if (returnGeneratedKeys) {
					PreparedStatement ps = null;
					if (clazz.equals(Device.class)) {
						ps = connection.prepareStatement("select seq_tc_devices.currval from dual");
					} else if (clazz.equals(Calendar.class)) {
						ps = connection.prepareStatement("select seq_tc_calendars.currval from dual");
					}
					ResultSet resultSet = ps.executeQuery();
					if (resultSet.next()) {
						return resultSet.getLong(1);
					}
				}
			} finally {
				statement.close();
				connection.close();
			}
		}
		return 0;
	}

According to this method change in DataManager.java

if you implement all changes in your code then successfully configuration with Oracle Database.

[stavoxnetworks]

Do you need someone to update this? I can add this feature if you like. I would like to jump into the project and help as much as possible.

[tananaev]

Contributions are always welcome.