dluettel
Elite Observer

Zugriff auf externe Datenbank

Hallo Zusammen,

wir versuchen gerade bei einem Kunden auf eine externe DB zuzugreifen. Wir benutzen eine Microsoft SQL 2016 (Express).

Folgendes SChema haben wir:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

          <xs:element dbName="dbo" name="dbo" temporal="0" version="4.0">

                    <xs:complexType dbName="CUSTTSAUSERCLASSEXTENSION" name="Custtsauserclassextension" readonly="1">

                              <xs:sequence>

                              <xs:element dbName="AREA" javaType="java.lang.String" length="36" name="Area" nullable="1" type="xs:string"/>

                              <xs:element dbName="COMMENT" javaType="java.lang.String" length="2147483647" name="Comment" nullable="1" type="xs:string"/>

                              <xs:element dbName="COMPETENCE1" javaType="java.lang.String" length="400" name="Competence1" nullable="1" type="xs:string"/>

                              <xs:element dbName="COMPETENCE2" javaType="java.lang.String" length="400" name="Competence2" nullable="1" type="xs:string"/>

                              <xs:element dbName="EXPRESSION-OBJECTID" javaType="java.lang.String" length="36" name="Expression-objectid" nullable="1" type="xs:string"/>

                              <xs:element dbName="GENENERICTASKSDESCRIPTION" javaType="java.lang.String" length="1000" name="Genenerictasksdescription" nullable="1" type="xs:string"/>

                              <xs:element dbName="ID" javaType="java.lang.String" length="36" name="Id" type="xs:string"/>

                              <xs:element dbName="NAMEANONYMIZATION" javaType="java.lang.Integer" length="3" name="Nameanonymization" nullable="1" type="xs:integer"/>

                              <xs:element dbName="NAMERESPONSIBILITY" javaType="java.lang.String" length="50" name="Nameresponsibility" nullable="1" type="xs:string"/>

                              <xs:element dbName="NAVIGATORVISIBILITY" javaType="java.lang.String" length="50" name="Navigatorvisibility" nullable="1" type="xs:string"/>

                              <xs:element dbName="PREVIOUSNAMES" javaType="java.lang.String" length="100" name="Previousnames" nullable="1" type="xs:string"/>

                              <xs:element dbName="TIMESTAMP" javaType="[B" length="8" name="Timestamp" nullable="1" type="binary"/>

                              <xs:element dbName="TSA_ID" javaType="java.lang.String" length="50" name="TsaId" nullable="1" type="xs:string"/>

                              <xs:element dbName="USEDINTYPESPSUSERTYPE" javaType="java.lang.String" length="36" name="Usedintypespsusertype" nullable="1" type="xs:string"/>

                              <xs:element dbName="WORKINGMODEL" javaType="java.lang.Integer" length="10" name="Workingmodel" nullable="1" type="xs:integer"/>

                              </xs:sequence>

                    </xs:complexType>

          </xs:element>

</xs:schema>

Bildschirmfoto 2016-09-21 um 15.45.58.png

Sobald ich die Datenquele dazu anlege, erhalte ich die folgende Fehlermeldung. Kann es sein, dass ich keine - Striche (EXPRESSION-OBJECTID) benutzen darf?

Client Version: 5.2.311.72449

Java Version: 1.8.0_101 Oracle Corporation

FSVersion=5.2.311.72449#5117;JDK=1.8.0_101 64bit Oracle Corporation;OS=Mac OS X 10.11.5 x86_64;Date=21.09.2016 15:45:17

de.espirit.or.QueryException: Error code: 207, state: S0001

          at de.espirit.or.impl.AbstractSessionHandler.fetchByKeys(AbstractSessionHandler.java:217)

          at de.espirit.firstspirit.content.ContentManagerImpl.fetchByKeys(ContentManagerImpl.java:556)

          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

          at java.lang.reflect.Method.invoke(Method.java:498)

          at de.espirit.firstspirit.io.DefaultServerHandler.callManager(DefaultServerHandler.java:67)

          at de.espirit.firstspirit.server.io.handler.ManagerCall.doCall(ManagerCall.java:132)

          at de.espirit.firstspirit.server.io.handler.CompactCall.handle(CompactCall.java:73)

          at de.espirit.firstspirit.server.io.MessageWorker.run(MessageWorker.java:31)

          at de.espirit.firstspirit.server.ExecutionManagerImpl$RunnableWrapper.call(ExecutionManagerImpl.java:634)

          at de.espirit.firstspirit.server.ExecutionManagerImpl$ExtendedCallable.call(ExecutionManagerImpl.java:600)

          at java.util.concurrent.FutureTask.run(FutureTask.java:266)

          at de.espirit.common.util.BoundedExecutorService$RunnableWrapper.run(BoundedExecutorService.java:436)

          at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

          at java.util.concurrent.FutureTask.run(FutureTask.java:266)

          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

          at java.lang.Thread.run(Thread.java:745)

          at de.espirit.common.util.SuspendableThread.run(SuspendableThread.java:55)

Caused by: java.sql.SQLException: Ungültiger Spaltenname "EXPRESSION".

          at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)

          at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)

          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)

          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)

          at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)

          at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2444)

          at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)

          at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)

          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:297)

          at de.espirit.or.impl.AbstractSessionHandler.fetchByKeys(AbstractSessionHandler.java:186)

          at de.espirit.firstspirit.content.ContentManagerImpl.fetchByKeys(ContentManagerImpl.java:556)

          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

          at java.lang.reflect.Method.invoke(Method.java:498)

          at de.espirit.firstspirit.io.DefaultServerHandler.callManager(DefaultServerHandler.java:67)

          at de.espirit.firstspirit.server.io.handler.ManagerCall.doCall(ManagerCall.java:132)

          at de.espirit.firstspirit.server.io.handler.CompactCall.handle(CompactCall.java:73)

          at de.espirit.firstspirit.server.io.MessageWorker.run(MessageWorker.java:31)

          at de.espirit.firstspirit.server.ExecutionManagerImpl$RunnableWrapper.call(ExecutionManagerImpl.java:634)

          at de.espirit.firstspirit.server.ExecutionManagerImpl$ExtendedCallable.call(ExecutionManagerImpl.java:600)

          at java.util.concurrent.FutureTask.run(FutureTask.java:266)

          at de.espirit.common.util.BoundedExecutorService$RunnableWrapper.run(BoundedExecutorService.java:436)

          at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

          at java.util.concurrent.FutureTask.run(FutureTask.java:266)

          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

          at de.espirit.firstspirit.client.io.ServerCaller.callManager(ServerCaller.java:477)

          at de.espirit.firstspirit.io.DefaultClientHandler.callManager(DefaultClientHandler.java:58)

          at de.espirit.firstspirit.client.io.RemoteManagerCaller.invoke(RemoteManagerCaller.java:53)

          at de.espirit.firstspirit.server.$Proxy49.fetchByKeys(Unknown Source)

          at de.espirit.firstspirit.store.access.templatestore.SessionHandlerImpl.fetchByKeys(SessionHandlerImpl.java:75)

          at de.espirit.or.impl.AbstractSession$EntityListImpl.fetch(AbstractSession.java:1120)

          at de.espirit.or.impl.AbstractSession$EntityListImpl.fetch(AbstractSession.java:1083)

          at de.espirit.common.util.AbstractLazyList.loadIndex(AbstractLazyList.java:197)

          at de.espirit.common.util.AbstractLazyList.get(AbstractLazyList.java:169)

          at de.espirit.or.impl.AbstractSession$EntityListImpl.get(AbstractSession.java:1108)

          at de.espirit.or.impl.AbstractSession$EntityListImpl.get(AbstractSession.java:1083)

          at de.espirit.firstspirit.store.access.contentstore.DatasetList.get(DatasetList.java:49)

          at de.espirit.firstspirit.store.access.contentstore.DatasetList.get(DatasetList.java:15)

          at de.espirit.firstspirit.ui.views.swing.contentstore.table.ContentTableModel.getValueAt(ContentTableModel.java:90)

          at javax.swing.JTable.getValueAt(JTable.java:2717)

          at de.espirit.firstspirit.ui.views.swing.contentstore.ContentView$3.valueChanged(ContentView.java:246)

          at javax.swing.DefaultListSelectionModel.fireValueChanged(DefaultListSelectionModel.java:184)

          at javax.swing.DefaultListSelectionModel.fireValueChanged(DefaultListSelectionModel.java:164)

          at javax.swing.DefaultListSelectionModel.fireValueChanged(DefaultListSelectionModel.java:211)

          at javax.swing.DefaultListSelectionModel.changeSelection(DefaultListSelectionModel.java:405)

          at javax.swing.DefaultListSelectionModel.changeSelection(DefaultListSelectionModel.java:415)

          at javax.swing.DefaultListSelectionModel.setSelectionInterval(DefaultListSelectionModel.java:459)

          at javax.swing.JTable.setRowSelectionInterval(JTable.java:2166)

          at de.espirit.firstspirit.ui.views.swing.contentstore.ContentView$18$2.run(ContentView.java:803)

          at de.espirit.common.gui.EventDispatchThreadHelper$2.run(EventDispatchThreadHelper.java:92)

          at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311)

          at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:756)

          at java.awt.EventQueue.access$500(EventQueue.java:97)

          at java.awt.EventQueue$3.run(EventQueue.java:709)

          at java.awt.EventQueue$3.run(EventQueue.java:703)

          at java.security.AccessController.doPrivileged(Native Method)

          at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)

          at java.awt.EventQueue.dispatchEvent(EventQueue.java:726)

          at de.espirit.firstspirit.client.AWTDispatchingEventQueue.defaultDispatchEvent(AWTDispatchingEventQueue.java:161)

          at de.espirit.firstspirit.client.AWTDispatchingEventQueue._dispatchEvent(AWTDispatchingEventQueue.java:145)

          at de.espirit.firstspirit.client.AWTDispatchingEventQueue.dispatchEvent(AWTDispatchingEventQueue.java:131)

          at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)

          at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)

          at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)

          at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)

          at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)

Caused by: de.espirit.common.base.control.CallerStack: called from

          at de.espirit.common.gui.EventDispatchThreadHelper.invokeLater(EventDispatchThreadHelper.java:88)

          at de.espirit.firstspirit.ui.views.swing.contentstore.ContentView$18.call(ContentView.java:788)

          at de.espirit.firstspirit.ui.views.swing.contentstore.ContentView$18.call(ContentView.java:774)

          at de.espirit.firstspirit.client.gui.util.GuiUtil$CallableWrapper.call(GuiUtil.java:1984)

          at java.util.concurrent.FutureTask.run(FutureTask.java:266)

          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

          at java.lang.Thread.run(Thread.java:745)

Gruß

Daniel Lüttel

0 Kudos
3 Replies
Peter_Jodeleit
Crownpeak employee

Die Vermtung scheint richtig, in anderen Datenbanksystemen sind solche Namen überhaupt nicht erlaubt. In  Microsoft SQL anscheinend schon, allerdings müssen solche Namen dann 'escaped' werden, was in FirstSpirit aber nicht gemacht wird.
Besteht die Möglichkeit, den Spalten-Namen zu ändern?

LG, Peter

Peter

Hallo Daniel,

falls Du den Spaltennamen in der Tabelle nicht ändern kannst oder willst, dann erzeuge doch einfach eine Datenbank-View auf Grundlage der Tabelle. Zum einen kannst Du die Anzhal der Spalten reduzieren und dann diesen Namen vergeben, mit denen  Java bzw. JDBC zurecht kommt.

Grüße Marian

0 Kudos
MichaelaReydt
Community Manager

Hallo Daniel,

ist dieses Posting noch aktuell? Benötigst du noch weitere Hilfe oder konnten Peter und Marian dir bereits weiterhelfen? In diesem Fall wäre es super, wenn du die "richtige Antwort" entsprechend markierst.

Solltest du zwischenzeitlich eine eigene Lösung gefunden haben, wäre es toll, wenn du sie hier bereitstellst.

Viele Grüße

Michaela

0 Kudos