Check if records exists in a table of a different database / LEFT JOIN with 2 databases

[MH2ag]

I got stuck with the following problem:

I have 2 databases. MS SQL an a local network server and MySQL on a remote server.

I’m synchronizing 2 tables. Now I want to check if records of the local table exists in the remote table.

On the same database I would do it with a LEFT JOIN with the condition of NULL.

How would I do this with 2 tables of 2 different databases?

This is what I have:

[patrick]

The only way to do that is using 2 different queries to both databases, you can’t do a query to 2 databases.

[MH2ag]

I know, this is what I have:

or do you mean something else?

The first query on my MS SQL database is:

SELECT * FROM *.*Kontakt WHERE FORMAT(ErstelltAm, 'yyyy-MM-dd') = CAST(GETDATE() AS DATE)

the second query is like in the screenshot above and I get this error:

{"status":"500","code":"ER_PARSE_ERROR","message":"SELECT * FROM *_*Kontakt WHERE (Date(ErstelltAm) = CURDATE()) AND KontaktId NOT IN '3627C28C-898A-4768-AD09-5614F3D4318C' - ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''3627C28C-898A-4768-AD09-5614F3D4318C'' at line 1","stack":"Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''3627C28C-898A-4768-AD09-5614F3D4318C'' at line 1
    at Query.Sequence._packetToError (/*/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
	at Query.ErrorPacket (/*/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
	at Protocol._parsePacket (/*/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/*/node_modules/mysql/lib/protocol/Parser.js:433:10)\n    at Parser.write (/*/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/*/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at TLSSocket.ondata (internal/streams/readable.js:719:22)
    at TLSSocket.emit (events.js:315:20)
    at addChunk (internal/streams/readable.js:309:12)\n    at readableAddChunk (internal/streams/readable.js:284:9)
    --------------------
    at Protocol._enqueue (/*/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/*/node_modules/mysql/lib/Connection.js:198:25)
    at /*/node_modules/knex/lib/dialects/mysql/index.js:132:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/*/node_modules/knex/lib/dialects/mysql/index.js:126:12)
    at executeQuery (/*/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
    at Client_MySQL.query (/*/node_modules/knex/lib/client.js:144:12)
    at Runner.query (/*/node_modules/knex/lib/execution/runner.js:130:36)
    at ensureConnectionCallback (/*/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:13:17)
    at Runner.ensureConnection (/*/node_modules/knex/lib/execution/runner.js:272:20)"}

[patrick]

The SQL syntax is incorrect. You have

SELECT * FROM *_*Kontakt WHERE (Date(ErstelltAm) = CURDATE()) AND KontaktId NOT IN '3627C28C-898A-4768-AD09-5614F3D4318C'

You should use <> instead of NOT IN if you check a single id or correct the syntax to NOT IN (:P1).

[MH2ag]

Thanks for your tip. Sadly I don’t get the desired results.

Would it be possible to get a LEFT JOIN Server Action in Data Transformation similar to the JOIN Action?

[MH2ag]

@mebeingken did you already have a similar task to do and give me a hint? I remember that you also worked with 2 databases.

[mebeingken]

The ability to join results was added to wappler after I needed to do this, but I documented it here:

Two data sources, one output

Up to this point, my projects have had a single datasource, so querying with joins makes returning a single dataset straightforward. But what to do when you have two datasources where joins are not possible? Wappler makes it easy by using the repeat action and clever output options. [20%20AM] The two datasources: ‘hc’ is mysql, and ‘hcinternal’ is mssql on a remote server. query_job_orders gets the baseline information but I don’t output the query itself as I normally do. A repeat is use…

You just use values from the first lookup inside the second lookup.

[MH2ag]

I think that this is the way I did the query. My problem is that I have to check what records are missing in the remote table. How would you do the following query with your solution?

SELECT
	qKontaktTodayLocal.KontaktId,
	qKontaktTodayRemote.KontaktId AS RemoteId 
FROM
	qKontaktTodayLocal
	LEFT JOIN qKontaktTodayRemote ON qKontaktTodayLocal.KontaktId = qKontaktTodayRemote.KontaktId 
WHERE
	ISNULL(
	qKontaktTodayRemote.KontaktId)

[mebeingken]

You are selecting from qKontaktTodayLocal in the SELECT clause, but your FROM has qKontaktToday.

Shouldn’t those match?

[MH2ag]

You are right , I edited the query and forgot to change the qKontaktToday to qKontaktTodayLocal.
My example was like I would do it on the same database. Should this also work with your setup?

[mebeingken]

@MH2ag

Should this also work with your setup?

I don't understand your question.

[MH2ag]

sorry about that. I think my problem is that I don’t get at what step of my setup (post 1 and 3) I would use my custom query to make it work.

[mebeingken]

A custom query is not a requirement when using two data sources, you can do regular queries inside the repeat. The general concept is that you do your first query, then repeat over those record to get corresponding values from the 2nd database. If you have a lot of records, it might not be the best approach as you are doing a query for each record in the repeat.

[MH2ag]

That is exactly my problem and that’s why I would like to simulate a LEFT JOIN but can’t understand the way this would work with 2 databases. I have about 560 records a day and with a LEFT JOIN I would get the missing records within a second. If I understand it correctly the new JOIN module does only do an INNER JOIN.
The reason why I used a custom query is that I need to check all records of today: WHERE FORMAT(ErstelltAm, 'yyyy-MM-dd') = CAST(GETDATE() AS DATE).
This is not possible with a regular query.

[mebeingken]

If you are trying to use a single query to retrieve records across two database connects, you can stop trying that approach.

If the Join action doesn’t work for you, then you could make a custom formatter.

[MH2ag]

I solved the problem by using Join from the Data Transformations. I wasn't aware that it uses a LEFT JOIN as standard.

It would be great if I could set a condition in the Join properties. This should speed up my current solution with *.length() == 0.

=>

Extend JOIN in Data Transformations

I would like to have the possibility to use a Condition with Join in Data Transformations. I would like only to show the records which are not existent in the second array.