I’m trying to find an easy setup to allow frequent synchronisation between the production server running MySQl and my development machine running Sybase anywhere.
I’m using Sybase Central v11 which offers a Migration Wizard to help in setting up a one way transfer, which is OK to get going. My current difficulty is that the wizard wants me to set up an ODBC connection between my machine and the production server. It invokes another wizard “Create Remote Server” to help in this process and this is actually where I’m stuck.
Under that wizard’s influence I have created and successfully tested an ODBC datasource using the Mac ODBC Admin Utility but then the “connection information” I enter in the wizard isn’t correct or something. It doesn’t help that its requirement for the field is phrased ambiguously. I’ve worked my way through most of the recent documentation in the Sybase help system and website but on this point there is nothing clear enough to follow and no useful examples either.
This screen shot shows the datasource and what I interpret the wizard wants me to insert in the Connection Information field. Unfortunately it doesn’t work.
[attachment=1]Picture 4.png[/attachment]
The error generated is :
[attachment=0]Picture 6.png[/attachment]
I’ve checked for the presence of the dll and it exists exactly where it ought so right now I’m out of clues.
Any suggestions are welcome.
Lee
Servoy 5.1.2, Sybase Central v11.0.1, MacOSX 10.5.8
david:
Run MySQL on your development machine. It’s a simple dump/restore from the server to your development machine to move data across.
Actually, after expending yet more fruitless time and energy pandering to Sybase Central’s needs for attention, I did just that. I’m new to this SQL stuff and I just discovered that transferring schemas between different brands is no walk in the park and as I really need a two way thing I’m glad I set it up as you suggested.
Why don’t you use Servoy to move your schema and data over ?
Just export the solution that uses these tables with all data. Then import it back into your Servoy+Sybase environment. Just make sure you created the empty databases and corresponding database connections in Servoy.
If you however want more fine-grained control over the datatypes in your tables then perhaps you should look at Liquibase, a Java based database refactoring tool.
Why don’t you use Servoy to move your schema and data over ?
Just export the solution that uses these tables with all data. Then import it back into your Servoy+Sybase environment. Just make sure you created the empty databases and corresponding database connections in Servoy.
I believe there is a problem importing data in that way in the current version Robert - I recall McCourt raised an issue about it a couple of weeks ago.
We’ve had to resort to putting MySql on the server instead of Sybase and using SQL Dump to get the data up. The import errors continually with Sybase as the Server Db.
It doesn’t create the schema correctly ? Or are those import errors perhaps related data and specifically to dates ?
MySQL is infamous for allowing invalid dates (00-00-0000 for instance) something that any other RDBMS (including Sybase) wouldn’t allow in a date/datetime/timestamp column…
It doesn’t create the schema correctly ? Or are those import errors perhaps related data and specifically to dates ?
MySQL is infamous for allowing invalid dates (00-00-0000 for instance) something that any other RDBMS (including Sybase) wouldn’t allow in a date/datetime/timestamp column…
Dont have the exact info Robert - but I’m pretty sure it was’nt dates that were the issue. My recall is that some of the data was erroring and on other occasions it was importing without errors and afterwards we’d find that not all the data had been crossed over. Had to return to the VERY SLOW Navicat Execute to load the data separately. The schema was imported properly though!
I think McCourt was expecting some update or support from his ticket - nothing yet so I guess they are working on it (doubtless I’ll get some feedback now )
ROCLASI:
MySQL is infamous for allowing invalid dates (00-00-0000 for instance) something that any other RDBMS (including Sybase) wouldn’t allow in a date/datetime/timestamp column.
My problems with mySQL, just revisited, are with defaulting Timestamps on create and modify events. I’ve tried this before, followed the instructions in the commentary on the mysql website (v 5.5 Manual section 10.3.1.1, same as for my version 5.1), and it just doesn’t work for me so I give up and let Servoy set the times.
ROCLASI:
MySQL is infamous for allowing invalid dates (00-00-0000 for instance) something that any other RDBMS (including Sybase) wouldn’t allow in a date/datetime/timestamp column.
You should then also add that you then can’t use any java client apps that (tries to) convert the date/timestamp/datetime data into a date object. Or any other platform/app that does handle datatype constraints properly like Java does (and thus also Servoy).
The date ‘00-00-0000 00:00:00’ might be manageable because it’s easy to spot. But a date like the 30th of februari (yes MySQL allows that) is still an invalid date but much harder to spot.
Also note that MySQL allows a lot more that is frowned upon (to use an understatement) by DBA’s (and totally not allowed in other RDBMS’s).
For instance when you insert a string of 100 characters in a varchar(50) column it won’t error. It just inserts the first 50 characters of your string and gives a warning instead of refusing to store it and giving an error, like any other RDBMS does. Most developers/apps don’t handle warnings so it goes unnoticed. But even when it gets noticed, what do you do. It’s already stored. Only unless you use a database transaction then you can roll it back.
Now lets say you have a very important numeric value that means something to you, lets say the value 10,000. Insert this into a number(4,0) and it stores the value 9999 for you. No error is given. Suddenly your data is mangled beyond recognition.
So it’s more then just allowing invalid dates. MySQL mangles your data and they call it a feature.
You can make MySQL really enforce the proper constraints on these datatypes by enabling Strict Mode. In my view this should be enabled out-of-the-box but this would break a lot of websites/apps that just don’t do any sanity checks on their data or handle the MySQL warnings properly. Again in my view this would still be a good thing to do because it’s severely broken and would force those website/app developers to make a proper implementation. Sun/MySQL AB never dared to enable it by default. Lets hope Oracle has the balls to do it. You data deserves it.
I’m of the opinion that “you’re data deserves” an intelligent programmer. At the level you are discoursing on Robert, any RDBMS has its ups and downs. And the ones with MySQL that you are harping on hardly matter. If a programmer goes in and assigns a number type to number(4,0), you would hope that person would know the ramifications of this change. In reality, this is such a rare situation to need this level of granularity and even then I would argue that half the time it would be better to enforce at the business logic layer instead of the database layer.
The common errors programmers make using number types is just as easy to do in any RDBMS as in MySQL. Handling monetary values comes to mind. Nothing an RDBMS can do to make programmers more intelligent here.
The same applies to dates. I don’t care how you cut it, dates are the most challenging data types for people to grasp. There are so many ways to mess them up at the fringes of usage – you really need to know your specific RDBMS (no matter which one it is) if you’re going to get all technical in this area. And the default setup with Servoy and MySQL is totally fine for 99.99% of cases. For the other .01%, it is a simple flip of a few switches in MySQL to configure the exact behavior your application requires.
Technical minutia aside, what probably matters most from an everyday practical standpoint is how easy and manageable an RDBMS is to you. As mac-centric Servoy developers developing business applications (no brain surgery here), MySQL has been easy-beans for us compared to Sybase. MySQL just runs. And on the rare occasion it does hiccup, finding an answer is fast.
When a user tries to insert a number that is bigger than the developer anticipated the database should simply reject it and not insert a complete other number. It doesn’t represent anything that the user was inserting so that makes it actually invalid data. It has no valid meaning anymore.
Anyway, you are touching on a typical disagreement between the views (plural) of a developer and a DBA. For example like the view that the database is (part of) the program. DBA’s tend to think otherwise, like the same way a filesystem (with all it’s constraints and permissions) is not (part of) the program.
At the VUG on january 22nd 2009 I did a preso about MySQL vs PostgreSQL that showed where MySQL (and PostgreSQL) comes from and where they are heading. These two views was also mentioned in the preso.
Although I am very much a developer I lean more to the DBA side of things so I disagree with your opinion that the application (and thus the programmer) should take care of the data integrity. An RDBMS is not just some fileserver where you can dump anything on you like, it was designed for a very specific function. Keeping your data sane is one of them.
And don’t get me wrong. I use MySQL myself for a bunch of websites, with custom code or with a CMS. Even have some Servoy solutions connected to them. MySQL is not a bad product. It’s very useful for a bunch of applications.
But do know what it does and doesn’t. MySQL does not keep your data sane by enforcing your datatype constraints (again, out of the box, and thus in 99.9% of all installations).
In the realm of RDBMS’s MySQL is really the odd one out on this behavior.
By the way. Me, you, we are hijacking another topic
ROCLASI:
By the way. Me, you, we are hijacking another topic
Oh yea, but he’s all taken care of now
ROCLASI:
Although I am very much a developer I lean more to the DBA side of things so I disagree with your opinion that the application (and thus the programmer) should take care of the data integrity.
Exactly why I responded I’ve learned a lot from you going around on this topic.
I still lean towards having the database layer be relatively “dumb”. If users shouldn’t enter a 5 digit number, I’d rather implement that on the business layer.
One argument for having business logic at the database layer is so that multiple applications using the same database get the same logic applied. My reply to that is that it make more design sense to have one application be the “controller” to the data as far as business rules go and then only expose data to other applications via API’s, webservices, stored procedures, etc – that contain the rules of engagement as part of the access (and contain the response codes).
An accounting system for example: you wouldn’t hook Servoy directly up to its data and start entering transactions. You go through an API of some sort that follows the business rules.
To me, a DBA’s job is to keep a database running and running as fast as possible. Programmer’s job is to make sure the data is what it should be. There is some overlap obviously in all areas (a programmer can kill speed in a hurry!) but I think much less now than the days when much of the business logic was implemented with triggers and stored procedures.
ROCLASI:
By the way. Me, you, we are hijacking another topic
Oh yea, but he’s all taken care of now
Maybe, but Robert did mention Servoy and CMS in the same paragraph so if I could just hijack my topic back can I ask you two how to deal with running a Servoy app under the session token from a CMS (in my case, xoops.org)? Perhaps it is a new topic but here’s my problem:
Logging into a xoops website generates a session record with four fields: Session_ID,Session_IP, Session_starttime, Session_data.
The session_data field when examined closely obviously violates Codd & Date (data within a field should not be structured):
Clearly this field records session history and also serves to reset the session TTL clock. In integrating my slowly forming Servoy solution with xoops I want to deeplink a webclient into the website and effectively run it under the xoops session control. That way I get a single sign on and uniform session management. The question is: how to do this. I think I can organize a transparent log-on to servoy if I can get the xoops uid but this is buried in the session_data field. That implies parsing the contents of the field for the uid, which should be fairly straightforward. The problem really is in maintaining session currency. I need to write back to this record appending session actions as appropriate.
Since xoops is based on a core/module architecture I figured maybe a dummy module whose sole purpose was to maintain the session ‘heartbeat’ based on messages from the servoy app would be an easy way round this.
Returning to your original post, I gather that you have MySQL as your production database. You wish to synchronise frequently with your development machine.
Sybase Central’s migration wizard is good for one off careful migration from MySQL to Sybase. I have used it several times during the development of a new application destined to run under Sybase in production. However, I would not use it as a regular procedure.
I would recommend that you consider your development configuration. For development we can use Serclipse, and connect to a development database on any machine. We are not restricted to using a db on our development machine. To avoid clutter I prefer to limit the number of databases on my development machine to one only: Sybase. I install and run other databases (including MySQL) on another machine.
Given the scenario of a MySQL production database, I would put a development database under MySQL on a spare PC and connect to that from Serclipse. The synchronisation process is then to dump the MySQL db from the production server, and load it onto the development database server.
A further advantage of working with the same db as your production scenario is that the developer is able to test out raw SQL queries without the uncertainty of cross-db issues arising.
That’s my view: others will differ through their having experienced other scenarios.
By the way, the error you have encountered through the migration assistant is familiar to me. After migration, you need to open the resulting Sybase db in Sybase Central and remove the remote server or servers you created during the migration process. With no remote servers configured, you might find the db will run OK given the libraries delivered with Servoy/Sybase. My view still stands, however. Even if you were to succeed with the migration, I would advise against using the process regularly.
