Access Database Synchronization - Showing Excluded Records

I have two different Access datasources and am trying to write a query to show me the records that have not been updated (records that exist in one datasource and not the other)

This is my get/update query to update the production database

<cfparam name='ProdDB'default='emp'>
<cfparam name='DevDB'default='DevEmp'>

<cfquery name='getSITESdev'datasource='#DevDB#'>
select sID,sName,sLevel,sHome,sPRoot from Sites
</cfquery>
<cfoutput query='getSITESdev'>
      <cfquery name='updateSLEVELprod'datasource='#ProdDB#'>
      update Sites
      set sLevel=#getSITESdev.sLevel#,sHome='#getSITESdev.sHome#',sPRoot='#getSITESdev.sPRoot#'
      where sID=#getSITESdev.sID# and sName='#getSITESdev.sName#'
      </cfquery>
</cfoutput>

Now I want to run and output a query of records that:
1. Exist in Development but not Production
2. Exist in Production but not Development
firstbankakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

firstbankakAuthor Commented:
I was thinking of a NOT IN subquery, but I didn't know if I could do with with different datasources...
mrichmonCommented:
You can try with a query of queries - I don't remember if it supports the NOT IN, but it should since it is a simple where clause....
firstbankakAuthor Commented:
I don't think I've ever tried using that...
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

firstbankakAuthor Commented:
Anyone have an example of that?
firstbankakAuthor Commented:
<cfquery name='devq'datasource='#dev#'>select * from docType order by dtID asc</cfquery>
<cfquery name='liveq'datasource='#live#'>select * from docType order by dtID asc</cfquery>

<cfquery name='not'datasource='#live#'>
select *
from docType
where dtID not in
      (<cfloop query='devq'>#devq.dtID#</cfloop>)
</cfquery>

This isn't working. It produces all records in the table.

the LIVE DATASOURCE contains an extra record that is not contained in the DEV DATASOURCE. I am trying to produce a query that will output this. I will then use this to write the missing record to the DEV DATASOURCE.

I am trying to run a query of queries from two datasources.
mrichmonCommented:
<cfquery dbtype="query" name="ExcludedRecords">
SELECT *
FROM query1
WHERE query1.id not in (selecy distinct id from query2)
</cfquery>
mrichmonCommented:
Or

<cfquery datasource="yourdsn" name="ExcludedRecords">
SELECT *
FROM query1
WHERE query1.id not in <cfqueryparam value="query2.id" list="true" cfsqltype="cf_sql_integer">
</cfquery>
firstbankakAuthor Commented:
Here's what I did:

I imported the table from development into the production database then tried to run this query with no success...

<cfquery name='thelive'datasource='#live#'>select * from docType order by dtID asc</cfquery>

<cfquery name='not'dbtype='query'>
select *
from thelive
where dtID not in
      (select dtID from docTypeDev)
</cfquery>

docType contains 11 records
docTypeDev contains 10 records

Error:
Encountered "dtID not in ( select. Incorrect conditional expression, Exprected one of [like|null|between|in|comparison] condition,

<cfquery name='not' dttype='query'>
mrichmonCommented:
well first of all where is docTypeDev defined?

Second - I said that query of query may not work with a subquery.  In that case you need to use the second method I posted which passes in the list to the live server.
firstbankakAuthor Commented:
Sorry about my ineptitude, the QoQ, and subqueries, and <cfqueryparam> are all new topics for me...

docTypeDev is the name of the imported table, so I thought I could run the query off the table, but I think I see what you're saying. It also be a subquery of a query, not a table.

Here's what I have


<cfquery name='thelive'datasource='#live#'>select * from docType order by dtID asc</cfquery>
<cfquery name='devo'datasource='#live#'>select * from docTypeDev order by dtID asc</cfquery>

<cfquery name='not'dbtype='query'>
select *
from thelive
where dtID not in
      <cfqueryparam value="devo.dtID" list="true" cfsqltype="cf_sql_integer">
</cfquery>

Error:
Ivnalid data devo.dtID for CFSQLTYPE CF_SQL_INTEGER
  <cfqueryparam value="devo.dtID" list="true" cfsqltype="cf_sql_integer">

*Note: These are access tables where dtID is an Autonumber long integer type.

mrichmonCommented:
not a problem.

try without the cfsqltype:

<cfquery name='thelive'datasource='#live#'>select * from docType order by dtID asc</cfquery>
<cfquery name='devo'datasource='#live#'>select * from docTypeDev order by dtID asc</cfquery>

<cfquery name='not'dbtype='query'>
select *
from thelive
where dtID not in
     <cfqueryparam value="devo.dtID" list="true">
</cfquery>
firstbankakAuthor Commented:
Query Of Queries syntax error.
Encountered "thelive . dtID not in \'devo.dtID\'. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,  
 
The error occurred in C:\Inetpub\wwwroot\DatabaseSynchronization.cfm: line 37
 
35 : from thelive
36 : where thelive.dtID not in
37 :       <cfqueryparam value="devo.dtID" list="true">
38 : </cfquery>
39 :  


Using:
<cfquery name='thelive'datasource='#live#'>select * from docType order by dtID asc</cfquery>
<cfquery name='devo'datasource='#live#'>select * from docTypeDev order by dtID asc</cfquery>

<cfquery name='not'dbtype='query'>
select *
from thelive
where thelive.dtID not in
      <cfqueryparam value="devo.dtID" list="true">
</cfquery>


Maybe my approach is wrong, but I just want to be able to find the records that exist in one table and not the other, than write them to that table.
mrichmonCommented:
This method should not use query of queries.

I changed it above, but left the table named "query1" which was probably confusing. Then I did not catch that you also had the error the second time. I apologize.

It should be like this:

<cfquery name='devo' datasource='#live#'>select * from docTypeDev order by dtID asc</cfquery>
<cfquery name='not' dbtype='#live#'>
select *
from docType
where docType .dtID not in
     <cfqueryparam value="devo.dtID" list="true">
</cfquery>

I think that is it.
firstbankakAuthor Commented:
The above gave me this error:
Attribute validation error for tag CFQUERY.  
The value of the attribute datasource, which is currently "", is invalid.  
 
The error occurred in C:\Inetpub\wwwroot\DatabaseSynchronization.cfm: line 40
 
38 : </cfquery> --->
39 : <cfquery name='devo' datasource='#live#'>select * from docTypeDev order by dtID asc</cfquery>
40 : <cfquery name='not' dbtype='#live#'>
41 : select *
42 : from docType
 

The second query named "Not" uses a dbtype of #live#, but #live# is the name for the datasource, not a query. For query "Not" I substituted "devo" for "#live#" and got:
The value of the attribute datasource, which is currently "", is invalid.  
 
The error occurred in C:\Inetpub\wwwroot\DatabaseSynchronization.cfm: line 40
 
38 : </cfquery> --->
39 : <cfquery name='devo' datasource='#live#'>select * from docTypeDev order by dtID asc</cfquery>
40 : <cfquery name='not' dbtype='devo'>
41 : select *
42 : from docType
 
Substituting "#devo#" for "#live#" produced:
Complex object types cannot be converted to simple values.  
The expression has requested a variable or an intermediate expression result as a simple value, however, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.
The most likely cause of the error is that you are trying to use a complex value as a simple one. For example, you might be trying to use a query variable in a <CFIF> tag. This was possible in ColdFusion 2.0 but creates an error in later versions.
 
 
The error occurred in C:\Inetpub\wwwroot\DatabaseSynchronization.cfm: line 40
 
38 : </cfquery> --->
39 : <cfquery name='devo' datasource='#live#'>select * from docTypeDev order by dtID asc</cfquery>
40 : <cfquery name='not' dbtype='#devo#'>
41 : select *
42 : from docType
 
and writing the query "Not" as <cfquery name='not' datasource='#live#'> produced:
Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] In operator without () in query expression 'docType .dtID not in Pa_RaM000'.  
 
The error occurred in C:\Inetpub\wwwroot\DatabaseSynchronization.cfm: line 44
 
42 : from docType
43 : where docType .dtID not in
44 :      <cfqueryparam value="devo.dtID" list="true">
45 : </cfquery>
46 :


*Note: I notice in your code you write docType .dtID (with a space before the period) and I usually don't include a space. I'm not sure this matters...

mrichmonCommented:
Sorry should have been datasource not dbtype:

<cfquery name='devo' datasource='#live#'>select * from docTypeDev order by dtID asc</cfquery>
<cfquery name='not' datasource='#live#'>
select *
from docType
where docType .dtID not in
     <cfqueryparam value="devo.dtID" list="true">
</cfquery>
firstbankakAuthor Commented:
Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] In operator without () in query expression 'docType .dtID not in Pa_RaM000'.  
 
The error occurred in C:\Inetpub\wwwroot\DatabaseSynchronization.cfm: line 44
 
42 : from docType
43 : where docType .dtID not in
44 :      <cfqueryparam value="devo.dtID" list="true">
45 : </cfquery>
46 :
 
Still not working, I don't know what is wrong?!
firstbankakAuthor Commented:
Hey look, I got it!

<cfquery name='devo' datasource='#live#'>select * from docTypeDev order by dtID asc</cfquery>
<cfquery name='not'datasource='#live#'>
select *
from docType
where docType.dtID not in
      (select dtID from docTypeDev)
</cfquery>

firstbankakAuthor Commented:
How should I do the point breakdown/answer?
mrichmonCommented:
Yes that will work this will too:

<cfquery name='devo' datasource='#live#'>select * from docTypeDev order by dtID asc</cfquery>
<cfquery name='not' datasource='#live#'>
select *
from docType
where docType .dtID not in
     (<cfqueryparam value="devo.dtID" list="true">)
</cfquery>

It just needed ( ) around the IN clause.

But the solution you used will not work given the original scenario of: "records that exist in one datasource and not the other"  it only works now because noth are on #live# and you are not even using your first query.  That won't work on separate data sources, but the cfqueryparm solution will.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
firstbankakAuthor Commented:
Ok, I'm going to try that solution again with the same scenario...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.