Moving from 2.0 beta to 2.0 prime time

[Anne Verrinder]

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

[Anne Verrinder]

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.

[jcompagner]

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..

[Anne Verrinder]

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!

[jcompagner]

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.

[Anne Verrinder]

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

[jcompagner]

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.

[Anne Verrinder]

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

[jcompagner]

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.

[Anne Verrinder]

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

[jcompagner]

why not just install besides youre current install in another directory..
You can have multiple installs on one pc.

[david]

Couple of import notes:

1) Importing example data is limited to the first 10,000 records. Useful for initializing solutions and for sending test data.

2) 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.

3) Stay away from reserved words

- David

[Anne Verrinder]

Couple of import notes:

1) Importing example data is limited to the first 10,000 records. Useful for initializing solutions and for sending test data.

2) 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.

3) Stay away from reserved words

- David

Hi David,

1) 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!

2) 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?

3) "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

[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)

[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).

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,

- David