• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

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
0
firstbankak
Asked:
firstbankak
  • 12
  • 8
1 Solution
 
firstbankakAuthor Commented:
I was thinking of a NOT IN subquery, but I didn't know if I could do with with different datasources...
0
 
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....
0
 
firstbankakAuthor Commented:
I don't think I've ever tried using that...
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
firstbankakAuthor Commented:
Anyone have an example of that?
0
 
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.
0
 
mrichmonCommented:
<cfquery dbtype="query" name="ExcludedRecords">
SELECT *
FROM query1
WHERE query1.id not in (selecy distinct id from query2)
</cfquery>
0
 
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>
0
 
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'>
0
 
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.
0
 
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.

0
 
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>
0
 
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.
0
 
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.
0
 
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...

0
 
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>
0
 
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?!
0
 
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>

0
 
firstbankakAuthor Commented:
How should I do the point breakdown/answer?
0
 
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.
0
 
firstbankakAuthor Commented:
Ok, I'm going to try that solution again with the same scenario...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now