sqlwhere clause : how does it work ?

[Jalrond]

Hello everybody !

first, thank you VERY much for having released sja, this tool is great ! (btw, please pardon my sometimes poor english, be sure I try to stay understandable)

My litte problem, as summarized is the title, has to do with the clause from sja. I work with Linux, and MySQL v3.23.58 (and MySQL v3.23.54 for the other databases… didn't have the choice).

My goal is to synchronize two databases having the same structure and Primary keys, and this works fine. So I try now to speed up the synchro (which uses a low-debit transmission) by selecting the data to synchronize, i.e. the ones with a field date_last_change, (datatime format)

First problem : It seems impossible to use the > character in the XML file

Second problem : an update in the datas from the databases seems not to be found by sja.

I do understand that my explanations could be less than sufficient to help me… But thank you for any support, I'm ready to explain any part of the problem, please help ! 🙂

[Shadow]

Sja has nothing to do with the first problem, it's XML specific. < and > (and others) characters have special meaning in XML, therefore they must be escaped. < must be written as < and > as >.

You should describe the second problem in a greater detail! Does sja return any error messages? Any modification done to the dbs?

[Jalrond]

Thank you very much for my XML problem

Now, the easiest way is probably to describe you what I want to do ^_^

I've got two databases and I'd want to synchronize one table, called 'mouvements'

There is a field name 'date_modification', which format is datetime

And the datas from this table can be modified by users using any of the 2 databases.

So, when I sync, I want to put the newest record everywhere, without knowing where he is at first, that's why I wanted to use a sqlwhere clause in my params.

I made many experiments, but it seems hardly useful to describe them 😮

My latest idea was to have a request looking like “date_modification > LAST_DATE”, but how to compare 2 dates, and to obtain this param LAST_DATE ? By getting it in the sja.log file ?

I saw in the forums that we can't put in the XML file any request using the both databases, so I'm now 😮 ing , thanks in advance for your help

[Shadow]

Well, you could create a little script that modifies the XML file generated by sja by adding the desired date to node. You may use XML APIs (DOM, SAX), or just simply open it as a text file and insert the content.

If you want to use the date/time when the sync job is executed, then use CURDATE(), CURTIME(), or NOW() functions of MySql in the WHERE clause.

[Jalrond]

Hello again,

Thanks for your help, the work is progressing (I had the same idea that you suggested, was glad of this 😀 )

But it doesn't work 😡

Let me show you the “wrong?” part of the XML file :

`mouvements`

mouvements.date_fiche_maj > 20040610115204

(mouvements is the only table I want to update, and only the records which were updated after a certain date… the request works fine if I try it directly with mysql 😕 !!!)

Do you see another 🙁 error ?

I wanna say, you are very kind for helpingus, poor newbies 😛

[Jalrond]

update :

I seem to have the same problem than in the previous topic (error 1064…)

Explanations : in the sja.log file, I have this error :

“You have an error in your SQL syntax near 'group by 1 order by 2' at line 1”

as in the other topic, this error disappears as soon as I choose to sync all the tables 😮

[Ritesh]

Can you send me backup of the databases and the corresponding XML file so that my developers can work on it?

It is very difficult to give a definite answer without looking into the data and the XML schema.

[Shadow]

Jalrond: Did you try to replace 20040610115204 with 2004-06-10 11:52:04?

[Jalrond]

Hello !

Sorry for being a bit late to answer your suggestions…

1st : Shadow, I tried this morning to write the datetime data int the 'correct' form ('2004-xx-xx xx:xx:xx'), no difference…

Then, I downloaded your trial version of SQLYog (I succeded in finding a Pc under Windows :P, but this was hard 😛 ) SQLYog is very nice to use, shame it doesn't exist under Linux !

I re-created my databases (no network connection to my main server ;)), and I used the wizard to do the sync….It worked perfectly !

I do believe now that the problem comes from your Linux version… (I copied the XML file to my Linux computer and tried to use it…with the same errors as before 😡 )

Sad… Do you still want my backups, Ritesh ?

[Shadow]

In this case, unfortunately, I cannot be of any help, as I don't have access to any Linux based computers running MySql…

[Author]

Posts