SQL/RepositoryException on Solution Import

[kwpsd]

Servoy version 7.4.3 -releaseNumber 2036
Repository version 44
java.version=1.7.0_45

I just updated a customers production server with Servoy 7.4.3 with no problems. Then, I attempted to import a newer version of the solution and received the following SQL error, but I have no idea why I am getting a repository exception or how to solve the problem. How do I go about finding what is causing this problem? Any help appreciated!

2015-02-07 14:45:41,995 ERROR [http-8080-2] com.servoy.j2db.util.Debug - Throwable [ ]
com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: Syntax error: Stopped parse at by(id93)
at com.servoy.j2db.server.Zc.Zdb.Za(Zdb.java:222)
at com.servoy.j2db.server.Zc.Zdb.importFromJarFile(Zdb.java:666)
at com.servoy.j2db.server.servlets.ConfigServlet.Zi(ConfigServlet.java:4401)
at com.servoy.j2db.server.servlets.ConfigServlet.service(ConfigServlet.java:2585)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:615)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Syntax error: Stopped parse at by(id93)
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.a.b.a(Unknown Source)
at com.hxtt.a.b.a(Unknown Source)
at com.hxtt.a.b.g(Unknown Source)
at com.hxtt.sql.b0.n(Unknown Source)
at com.hxtt.sql.b0.a(Unknown Source)
at com.hxtt.sql.am.a(Unknown Source)
at com.hxtt.sql.am.execute(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.servoy.j2db.datasource.Zg.invoke(Zg.java:8)
at com.sun.proxy.$Proxy14.execute(Unknown Source)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at com.servoy.j2db.server.Za.Zo.createMissingDBSequences(Zo.java:1563)
at com.servoy.j2db.server.Zc.Zj.importDatabaseInfo(Zj.java:461)
at com.servoy.j2db.server.Zc.Zdb.Za(Zdb.java:590)
... 17 more

[omar]

Did you try to run the repositoryUpgrade? https://wiki.servoy.com/display/Serv7/Servoy+Repository+Upgrade

[kwpsd]

Hi, Omar! Thanks for responding.

Yes, I did update the repository and posted the version in my original post. The repository version was 44 both before and after I did the repository update.

[omar]

Yes I saw that but I had a similar problem where I had the latest repository version but still got an error just like you. Are you using the same Java version on your development machine ( 1.7.0_45)? I took these steps and that solved it for me:

- upgrade Servoy to 2036 (you did that already)
- installed jre8u31 and jdk8u31
- ran repositoryUpgrade
- rebooted the server

[rgansevles]

Is there some more info in the logs, maybe showing the failed sql?

What database are you using?

Rob

[kwpsd]

@Omar: Yes, the production server and developer are running JRE 1.7.0_45. Are you saying updating to JRE 8 fixes the problem? I will try that. What is the purpose of installing the JDK on the production server?

@Rob: The production server is running MSSQL 2008 (and has been since 2012).

[omar]

Hi Kim,

Not necessarily, that's just the steps I took that solved my problem. The JDK should not be necessary either but I need it for a Java project that I am working on.

[kwpsd]

I am still unable to import a solution to the production server.

To test, on another computer in the office, I installed SQLEXPRESS 2012, JRE 1.8.0_32-b13, and Servoy Developer 7.4.3 (using the MS JDBC driver 4), and everything seems to be working. I attempted to import the same solution to this new set up and received an identical error as when I attempt to import the solution on the production server:

2015-02-13_11-37-11-2.png (12.42 KiB) Viewed 7577 times

There is nothing in the 'servoy_log.txt' file regarding this error, nor is there anything in the log section of the administrative web page. Thinking that the import file may have been corrupted, I exported the solution again, but I get the same results when I try to import it.

@Servoy engineers: How do I determine what the SQL exception is, and what do I need to do to get this solution to import?

[kwpsd]

After much wasted time, I figured out what is causing the non-descript error message in Servoy 7.4.3:

I went through many different installations/configurations (e.g. JRE 1.7 vs. 1.8, MSSQL native driver vs. JTDS driver, Servoy 7.4.2 vs. 7.4.3, different solution exports, etc.) before finding the culprit. Servoy 7.4.2 reports the error correctly while 7.4.3 does not. When importing a solution that uses DBF files, Servoy or the JDBC driver creates sequence files (of form seq_filename_pk.SEQ) in the VFP folder. These residual sequence files cause the error on subsequent imports. The solution is to simply delete these sequence files prior to importing.

Servoy 7.4.2 Error Message

import_error_message.png (14.41 KiB) Viewed 7435 times

So, Servoy, why is the error message missing in 7.4.3? I noticed the installation file size for 7.4.2 is ~311 MB whereas the file size for 7.4.3 is ~303 MB. Was something left out of the build?

[jcompagner]

but we just report there the sql exception
so it is not servoy that reports something different, its the database..

so everything that is "java.sql.SQLException: xxxxxxxx" is what the database gives us and we just relay that information in the dialog.

[kwpsd]

Johan,

Our solution successfully imports in 7.4.2 but not in 7.4.3, so something has changed between versions. I discovered that the SQL exception is occurring in the HXTT JDBC driver:

Caused by: java.sql.SQLException: Syntax error: Stopped parse at by(id93)
at com.hxtt.global.SQLState.SQLException(Unknown Source)

After reading through HXTT's support forum, a likely cause of this error is improper handling of SQL reserved words. Our VFP tables have a few column names that happen to be SQL reserved words. Could this be the problem?

[jcompagner]

But you mentioned above that it was a sequence problem? (which you could fix by cleaning them up first)
But i guess you still get the error on 7.4.3 (it was really a different error, not something servoy just reports differently)

If we could test your solution somehow (doing an import) then we could see what is really different.

the thing is that purely a solution import just changes our repository tables those don't have reserved stuff in them.
So are there somehow data model changes (like table creates or column additions) that need to be done that somehow fail?