Hi all,
I’ve been working on a solution in R2 2.0 RC10.
I installed the released 2.0 on another computer, and attempted to import the solution (exported from R2 2.0 RC10). I’m getting the error “can’t import” and details says “reserved words used as column names”.
Don’t see a ReadMe, or any special instructions with the announcement, about moving solutions, other than “install in new folder”, which would require an export/import, I’m guessing.
Any ideas? What have I missed?
Anne
After some poking around, found the checkbox on the Import screen that allows ignoring of SQL reserved words. Now have a new error …
“com.servoy.j2db.persistence.RepositoryException: com.servoy.j2db.persistence.RepositoryException: com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -131: Syntax error near ‘userid’ on line 1
Warning: Reserved words used as table or column names: columns: primary level filter comment filter level filter level comment comment date comment level table select from number primary position password comment date filter comment level get object comment filter active position value date;”
The “reserved words” is, I assume, just a warning, since I checked the checkbox. It’s the ASA Error -131 that is the problem, I’m guessing. So the question now is: “line 1” of what?
Thanks for any suggestions.
what are you trying to do?
go from a firebird database to a sybase database by export->import solution?
Then you where using reserved words that did work on the firebird table but don’t work on a sybase table.
You could try to generated youre tables youreself first through sybase central.
Or else you first have to rename stuff..
Sorry, not enough info given. My goal was to move my solution to another computer so my daughter can work on the solution (taking turns since as I understand it, multi-user development not an option). Wanted to export it from mine, import on hers.
My repository is currently in Sybase. My dbServer is MS SQLServer. So I changed her servoy.properties to point to my IP for the data, which worked fine, and then tried to import the repository from my computer.
I did find the checkbox for “ignore reserved words”, and checked it, and then got the "ASA Error -131: Syntax error near ‘userid’ on line 1 ". So this is Sybase complaining about a problem with a file that came from Sybase. And this error is probably masking lots o’ other errors that are waiting in line behind it.
Is it safe to assume that the problem is in a method? I need to look at line 1 of every method that might have a ref to the column userid?
I successfully got her working by copying my whole Servoy folder to her computer and adjusting servoy.properties again, but I’m thinking I can’t move to the released 2.0 until I get this resolved.
Thanks!
first you say that youre db server == ms sql server
and then you say that sybase is complaining about a problem that came from sybase..
But this is not the case.
You have on youre db server that is build on MS SQL a table with a column that can’t be imported into a sybase server.
it is not a method on line1 or something it is the a sql query that is going wrong that is send to the database because sybase can’t handle a column name you have with ms sql db.
what you could try to do is make youre tables you have in youre ms sql database youre self on sybase through sybase central. If that doesn’t work then you have to rename some of youre column before you can import them into a sybase db.
Hi folks. Next installment of the same issue:
Recap: My repository was in Sybase, my data was in MSSQLServer. When exporting my solution and then trying to import it back, on another computer, Servoy rejected the import because the MSSQL database (the data) contained column/table names that were not allowable in Sybase.
[ I had recently converted the data to MSSQL from Sybase, and had no complaints at that point while working on the solution while it was still in Sybase, but apparently the import is fussier.]
So, I am attempting to convert the Repository to MSSQL, on the premise that an MSSQL repository won’t complain about the column/table names in the backend database, with tables/columns that were created in MSSQL to start with.
So, I exported my solution. I created a new empty database with Enterprise Manager, made and successfully tested a new connection to that would-be repository.
I highlighted the existing Sybase repository connection and removed it.
I created a new connection with the same name as the original repository, but changed the URL to use the MSSQL driver and point to the new empty MSSQL repository file, all of which were accepted.
I relaunched Servoy, and had an empty Repository. I clicked “Import” and pointed to my exported (Sybase) solution, used “import solution as” so I could name it, it ground away for about half a minute and then said it could not import the solution. Details: “com.servoy.j2db.persistence.RepositoryException: com.servoy.j2db.persistence.RepositoryException: Reserved words used as table or column names: columns: primary level filter comment filter level filter level comment comment date comment level table select from number primary position password comment date filter comment level get object comment filter active position value date;”
So I need to know what I’m doing wrong. I’m making the huge assumption that by removing the original repository, and making a new one of the same name, with a URL that points to an empty MSSQL file, that it is that name that identifies the Repository database to Servoy (because I can’t find anything else that tells Servoy what to use as the Repository when it launches) … in servoy.properties, this server looks like any other server …
Is there a feature I’m missing somewhere that tells Servoy which database is the Repository, or does it somehow latch onto the name first used when Servoy is first setup? I did scour the documentation looking for this principle, with no luck. If there is a document that describes moving backend and/or repository from one database platform to another, please point me at it and I’ll do my homework!
If I’ve done this right (using the same name as the previous repository), why might an MSSQL repository not like the column/table names that were originally built in MSSQL?
Thanks,
Anne
on the last import did you select: “Ignore reserved words” ?
Because you always need to check that..
That errors that you get is an error/warning servoy generates not an error of the database.
Hi Johan, thanks for following up!
In the Repository dialog, when I click “import”, I get a dialog titled “Import Solution”. It has:
Import solution as: (so I can name it)
Import solution sample data checkbox: (I don’t check this; not sure what “data” means in a repository context)
and
Activate release after import checkbox.
No choice about ignoring reserved words.
Anne
that is strange
What version do you exactly use of Servoy?
That doesn’t seem to be the final of 2.0
because there you have another option “Allow reserved SQL keywords…”
The data means that when you export a solution you can export some sample data of the data tables youre solution uses.So that you have the start data for a solution when exported to another developer or server.
Also very handy to use when sending us sample solutions.
log2.html.zip (35.2 KB)
Johan wrote:
What version do you exactly use of Servoy? That doesn’t seem to be the final of 2.0 because there you have another option “Allow reserved SQL keywords…”
2.0 beta RC 10 Build 269. I’m not willing to upgrade until I know I’ll be able to import my solution, because the one instruction I can find about moving to the released version says to start with a new install and then import. So here I am until I get this problem solved.
Not to be argumentative! – that “reserved words” error comes up in all cases? Initially, I had a Sybase repository rejecting MSSQL names. Maybe now that I’m trying to switch repositories, it’s the reverse: MSSQL won’t accept Sybase reserved words? Sounds like that checkbox should always be checked!
Maybe I could install released 2.0 on another computer, set up an empty MSSQL repository on that, and then move the export file over there? I think I’ll try that next.
Can you answer one of the earlier questions? – How does Servoy know which “dbserver” contains the repository? If I had removed my original Sybase “repository_server”, and then made a new one with the name “Clarence” and connected it to my new empty MSSQL file created for the new Repository, would Servoy have known that Clarence was the file to consider as the Repository?
Is the first install of Servoy the only time one can determine the name of the repository? [I have read all the documentation x times, and it has lots of click here/do that, but not much narrative about what’s actually going on, or what are the principles involved. Or, I have spring fever and my powers of induction are out rolling in the grass …]
Johan wrote:
The data means that when you export a solution you can export some sample data of the data tables your solution uses. So that you have the start data for a solution when exported to another developer or server.
Also very handy to use when sending us sample solutions.[/quote]
Thanks for that explanation. Great for starting from scratch! In this case the sample data would one more thing for the import to get excited about, and I have huge MSSQL data files full of lots of real data, so I’m good without that option.
[Sorry, don’t know how to use the “quote” feature. I hit that button but the new message looked just like a normal one with no quoted stuff to refer to … having a Stupid Week I guess.]
Thanks, Johan, for sticking with me on this !!!
Anne
why not just install besides youre current install in another directory..
You can have multiple installs on one pc.
Couple of import notes:
Importing example data is limited to the first 10,000 records. Useful for initializing solutions and for sending test data.
If a table already exists in the named connection, the data does not import. Example: you set up a named connection to a new database and import a solution for the first time. The appropriate tables are created and the solution data is imported. Any imports of the same solution after this will not import data because the tables are already created. Delete the tables in the data provider prior to import to get around this.
Stay away from reserved words
david:
Couple of import notes:
Importing example data is limited to the first 10,000 records. Useful for initializing solutions and for sending test data.
If a table already exists in the named connection, the data does not import. Example: you set up a named connection to a new database and import a solution for the first time. The appropriate tables are created and the solution data is imported. Any imports of the same solution after this will not import data because the tables are already created. Delete the tables in the data provider prior to import to get around this.
Stay away from reserved words
- David
Hi David,
I must have a big gap in my understanding somewhere. I think of the Repository as a database (generated magically within Servoy) that holds all the pieces of my solution (forms, methods, etc.). And I have a separate database that has the tables with all my data; it was actually created years ago by someone using Access Project; it has lots o’ data. It would never have occurred to me to consider importing data into the repository. In fact, I thought I saw a warning someplace about not making data tables in the same database as the repository. All I can picture is the conversion picking up some records from my data and reimporting them into my data, which sounds like a mess. I’m having trouble understanding why I would want <= 10,000 records out of the 80 or 90 tables in my MSSQL backend imported into my Repository. I know I’m missing some critical factoid here, but have given up trying to preserve any shred of pride on this thread!
What you’re saying here sounds like I wouldn’t have imported any data anyway, since I have lots of data tables with lots of data. But I’m curious – if I had a connection to a new empty database, and I import a solution (and the solution would have to already refer to that new empty database, or else be a no-forms solution), then … from whence does it get the sample data to import? With the “new database” you mention, you refer to importing “the solution data” – how would a new one have any data?
“Stay away from reserved words” … great advice, but about as useful as “stay away from muggers” … since muggers don’t have “mugger” tatooed on their foreheads. [where’s the “wiseass” emoticon? I’m sure there are lists of reserved words to be had …]
Call me literal-minded, but if I have a solution that refers to existing MSSQL columns/tables that MSSQL has been playing with nicely for years, and I’m trying to import that solution into a brand spanky new empty MSSQL database that I hope Servoy will somehow see as my new repository (Johan didn’t answer that question), then I’m Apples-To-Apples in my eyes, and Servoy is being Chicken Little.
Thanks!
Anne
where are we saying that example data does have something to do with repository data??
Example data is just data from youre Data server that you can transfer with solution data file.
It gets imported into youre data server when the table is new or doesn’t contain any data yet.
This is very usefull if you are developing a new solution wherey you have some database valuelist for example (the values of youre valuelist are comming out of youre data server tables)
OK, I just read through the entire post to see what the issues are. It seems to me that you have the right idea for the most part but the errors are occurring when you switch your data source from one database platform to another (mssql ↔ sybase).
One solution then is to point both of your computers to the same repository database and the same data database.
On the second (slave) computer, point all of the named database connections to your (master) computer. At minimum, this means the slave repository needs to point to the master sybase installation and the slave data connectio to the master mssql installation by changing the IP address in the named connections on the slave computer.
This solution has a few issues if both developers are working on the same solution at the same time… But very useful otherwise.
The next way to do it is have duplicate repositories on the master and slave computers and point both of them to the same data source. In this scenario, you would have to export and import solutions back and forth between the computers to keep the repository database up to date on the computer currently being used. You should not have any problems with solutions not importing in this scenario due to reserved words.
The last option is to have duplicate repositories and duplicate data sources on both computers. If either the repository or data source are from different vendors on the different computers, you can run into reserved word issues when you initially set up the second computer with a solution exported from the first computer. In this case, identifying and fixing the column names before export and import of the solution will do the trick.
I use all three scenarios all the time due to developing with three different computers, multiple client computers, multiple operating systems, multiple database vendors, and whether I’m on a plane, at home or at a client. It gets confusing!
Hope this helps,
jcompagner:
where are we saying that example data does have something to do with repository data??
Example data is just data from youre Data server that you can transfer with solution data file.
It gets imported into youre data server when the table is new or doesn’t contain any data yet.
This is very usefull if you are developing a new solution wherey you have some database valuelist for example (the values of youre valuelist are comming out of youre data server tables)
Waahhhh. Maybe you aren’t saying that, Johan; I am both new at this and apparently incredibly thick.
I was in the act of attempting to import a solution into a repository, and being given the option of including “sample data”. Beats me why that option is there or why Servoy is asking me about data when I’m trying to work on a repository!!
In my mind, what I was doing had nothing to do with my data. I know that my solution refers to data tables, and I now understand (Thank you, David) that I could have checked off that box and Servoy would have ignored that I had checked it (since the data tables my solution refers to aren’t empty). Maybe this would be less confusing to stupid people if Servoy didn’t offer this option until a later dialog, after it’s looked to see if the solution refers to empty or full tables, and not offer it in situations in which it’s not going to apply and going to be ignored anyway.
[putting dunce cap on and going back to the dunce stool in the corner of the room.]
Anne
david:
OK, I just read through the entire post to see what the issues are. It seems to me that you have the right idea for the most part but the errors are occurring when you switch your data source from one database platform to another (mssql ↔ sybase).Hi again, David.
Actually, switching the data source from Sybase to MSSQL went without a hitch, a month ago. Repository was still in Sybase, and I would have been happy to stay that way. We had created, in MSSQL, an exact mirror of my Sybase data tables (long story, don’t ask – this project is years old and Servoy is only recently being introduced to it). So switching backends just meant repointing the data connection in dbservers, using the same name for the dbserver.
Computer #1 was/is hosting the MSSQL data/database; no problem there. Repository was still Sybase, and no problem there.
Then I wanted to allow a cohort to also work on the solution, and figured I would export the solution, and import it into the repository on the other computer, changing the URL for the data to my IP number. We don’t need to work simultaneously.
When I attempted to import into the repository on Computer #2, I got “reserved words” errors, and was told that the Sybase repository didn’t like the column and/or table names that the solution referred to (in the MSSQL data tables).
So, my workaround, which we are using as I speak, was to copy my whole bloody Servoy folder onto Computer #2, switch the URL in servoy.properties for the data source to refer to my IP number instead of “localhost”, and let my helper go-forr-it. That works; I’m happy.
[Except that even with the .bat file modified to get more memory, it slows to a crawl on her computer after a while … but that’s another thread; a reboot gets her back.]
Then I realized that it’ll take an export/import to get myself off the beta and up to the released 2.0. To enable an import, I was advised to convert my repository to also be MSSQL.
That brings us up to my first post of today, which can really be summarized into two issues:
My original as-yet unanswered question: how does Servoy know which dbserver is the repository. Somehow it knows, because it automatically tries to “use” this dbserver when it launches (or forces you to create one on a new install). I am currently assuming that if one wants to switch the backend for an existing repository, one’s only option is to use the identical name as one’s original repository dbserver had. If I’m not right on that, then I might have a much bigger problem than “reserved words”; and until someone takes mercy on me and answers that question, we all may be blowing smoke at each other.
I have already created an empty MSSQL database that I’m hoping Servoy is seeing as the new repository (see No. 1). I have exported my solution (which refers to MSSQL tables) from the now-defunct old Sybase repository, and would like to import it into the new MSSQL wannabe-repository. I’m still getting reserved words errors, MSSQL to MSSQL.
So Johan says to use the checkbox for ignoring reserved words. I’m left with the impression that Servoy will issue that complaint regardless of circumstances, and that it’s a checkbox that should always be checked. No problem with that.
I’ll install the 2.0 final release on the other computer (or here, according to Johan), so I can check off that “ignore” checkbox.
And then I’ll have my fingers crossed that what I think is a new MSSQL repository actually is.
And if you aren’t asleep yet …
… thanks!
Anne
Anne,
- “Stay away from reserved words” … great advice, but about as useful as “stay away from muggers” … since muggers don’t have “mugger” tatooed on their foreheads. [where’s the “wiseass” emoticon? I’m sure there are lists of reserved words to be had …]
Just to let you know, in the online help (F1) of the release version of Servoy 2.0 Developer, we have added an Appendix that contains a section called “Servoy reserved words”. This complete listing of words includes terms reserved by Servoy as well as the most common SQL reserved words.
The appendix is also included in the Servoy Developer Edition Volume 2: Reference Guide.
This book is available as a downloadable pdf file from the Servoy developers/documentation page or can be purchased in printed book form from the Servoy Store.
Marc Norman
Servoy
Downloading that manual.
Would it be possible/practical for versions of documentation to be on the web site, so that when there’s a rev, it’s clear (or do you announce?). I notice the download has a very specific rev on it.
Thanks for all your work, and to everyone who helped on this thread.
Anne
Actually, switching the data source from Sybase to MSSQL went without a hitch, a month ago. Repository was still in Sybase, and I would have been happy to stay that way. We had created, in MSSQL, an exact mirror of my Sybase data tables (long story, don’t ask – this project is years old and Servoy is only recently being introduced to it). So switching backends just meant repointing the data connection in dbservers, using the same name for the dbserver.
Could be an export/import bug with the RC versions that were coming out in the past two months with rapid regularity. Could also be a simple user error. I have worked with all kinds of setups in the past and have turned myself royally inside out on a number of ocassions with all the options Servoy gives the developer. Sounds like you got it worked out.
- My original as-yet unanswered question: how does Servoy know which dbserver is the repository. Somehow it knows, because it automatically tries to “use” this dbserver when it launches (or forces you to create one on a new install)
Yea, the named connection “repository_server” in the database connections tab in preferences is how Servoy knows. Delete this connection and Servoy doesn’t run.
- I have already created an empty MSSQL database that I’m hoping Servoy is seeing as the new repository (see No. 1). I have exported my solution (which refers to MSSQL tables) from the now-defunct old Sybase repository, and would like to import it into the new MSSQL wannabe-repository. I’m still getting reserved words errors, MSSQL to MSSQL.
Have you checked out Clint Robert’s article on using MS SQL 2000 as the repository? Link
Cheers,