[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access Database Synchronization - Showing Excluded Records

Posted on 2006-04-26
23
Medium Priority
?
177 Views
Last Modified: 2013-12-24
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
Comment
Question by:firstbankak
  • 12
  • 8
20 Comments
 

Author Comment

by:firstbankak
ID: 16546992
I was thinking of a NOT IN subquery, but I didn't know if I could do with with different datasources...
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 16549072
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
 

Author Comment

by:firstbankak
ID: 16587937
I don't think I've ever tried using that...
0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 

Author Comment

by:firstbankak
ID: 16598405
Anyone have an example of that?
0
 

Author Comment

by:firstbankak
ID: 16599389
<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
 
LVL 35

Expert Comment

by:mrichmon
ID: 16599415
<cfquery dbtype="query" name="ExcludedRecords">
SELECT *
FROM query1
WHERE query1.id not in (selecy distinct id from query2)
</cfquery>
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 16599436
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
 

Author Comment

by:firstbankak
ID: 16617300
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 16617332
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
 

Author Comment

by:firstbankak
ID: 16617504
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 16617827
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
 

Author Comment

by:firstbankak
ID: 16617907
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 16618040
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
 

Author Comment

by:firstbankak
ID: 16618480
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 16618522
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
 

Author Comment

by:firstbankak
ID: 16618594
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
 

Author Comment

by:firstbankak
ID: 16618776
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
 

Author Comment

by:firstbankak
ID: 16618789
How should I do the point breakdown/answer?
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 2000 total points
ID: 16618907
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
 

Author Comment

by:firstbankak
ID: 16619088
Ok, I'm going to try that solution again with the same scenario...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question