sqlwhere clause : how does it work ?

[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