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='DevEm
<cfquery name='getSITESdev'datasour
select sID,sName,sLevel,sHome,sPR
</cfquery>
<cfoutput query='getSITESdev'>
<cfquery name='updateSLEVELprod'dat
update Sites
set sLevel=#getSITESdev.sLevel
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
This is my get/update query to update the production database
<cfparam name='ProdDB'default='emp'
<cfparam name='DevDB'default='DevEm
<cfquery name='getSITESdev'datasour
select sID,sName,sLevel,sHome,sPR
</cfquery>
<cfoutput query='getSITESdev'>
<cfquery name='updateSLEVELprod'dat
update Sites
set sLevel=#getSITESdev.sLevel
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
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....
ASKER
I don't think I've ever tried using that...
ASKER
Anyone have an example of that?
ASKER
<cfquery name='devq'datasource='#de
<cfquery name='liveq'datasource='#l
<cfquery name='not'datasource='#liv
select *
from docType
where dtID not in
(<cfloop query='devq'>#devq.dtID#</
</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.
<cfquery name='liveq'datasource='#l
<cfquery name='not'datasource='#liv
select *
from docType
where dtID not in
(<cfloop query='devq'>#devq.dtID#</
</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.
<cfquery dbtype="query" name="ExcludedRecords">
SELECT *
FROM query1
WHERE query1.id not in (selecy distinct id from query2)
</cfquery>
SELECT *
FROM query1
WHERE query1.id not in (selecy distinct id from query2)
</cfquery>
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>
<cfquery datasource="yourdsn" name="ExcludedRecords">
SELECT *
FROM query1
WHERE query1.id not in <cfqueryparam value="query2.id" list="true" cfsqltype="cf_sql_integer"
</cfquery>
ASKER
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='
<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|comp
<cfquery name='not' dttype='query'>
I imported the table from development into the production database then tried to run this query with no success...
<cfquery name='thelive'datasource='
<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|comp
<cfquery name='not' dttype='query'>
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.
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.
ASKER
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='
<cfquery name='devo'datasource='#li
<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.
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='
<cfquery name='devo'datasource='#li
<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.
not a problem.
try without the cfsqltype:
<cfquery name='thelive'datasource='
<cfquery name='devo'datasource='#li
<cfquery name='not'dbtype='query'>
select *
from thelive
where dtID not in
<cfqueryparam value="devo.dtID" list="true">
</cfquery>
try without the cfsqltype:
<cfquery name='thelive'datasource='
<cfquery name='devo'datasource='#li
<cfquery name='not'dbtype='query'>
select *
from thelive
where dtID not in
<cfqueryparam value="devo.dtID" list="true">
</cfquery>
ASKER
Query Of Queries syntax error.
Encountered "thelive . dtID not in \'devo.dtID\'. Incorrect conditional expression, Expected one of [like|null|between|in|comp
The error occurred in C:\Inetpub\wwwroot\Databas
35 : from thelive
36 : where thelive.dtID not in
37 : <cfqueryparam value="devo.dtID" list="true">
38 : </cfquery>
39 :
Using:
<cfquery name='thelive'datasource='
<cfquery name='devo'datasource='#li
<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.
Encountered "thelive . dtID not in \'devo.dtID\'. Incorrect conditional expression, Expected one of [like|null|between|in|comp
The error occurred in C:\Inetpub\wwwroot\Databas
35 : from thelive
36 : where thelive.dtID not in
37 : <cfqueryparam value="devo.dtID" list="true">
38 : </cfquery>
39 :
Using:
<cfquery name='thelive'datasource='
<cfquery name='devo'datasource='#li
<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.
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
<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.
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
<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.
ASKER
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\Databas
38 : </cfquery> --->
39 : <cfquery name='devo' datasource='#live#'>select
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\Databas
38 : </cfquery> --->
39 : <cfquery name='devo' datasource='#live#'>select
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\Databas
38 : </cfquery> --->
39 : <cfquery name='devo' datasource='#live#'>select
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\Databas
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...
Attribute validation error for tag CFQUERY.
The value of the attribute datasource, which is currently "", is invalid.
The error occurred in C:\Inetpub\wwwroot\Databas
38 : </cfquery> --->
39 : <cfquery name='devo' datasource='#live#'>select
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\Databas
38 : </cfquery> --->
39 : <cfquery name='devo' datasource='#live#'>select
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\Databas
38 : </cfquery> --->
39 : <cfquery name='devo' datasource='#live#'>select
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\Databas
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...
Sorry should have been datasource not dbtype:
<cfquery name='devo' datasource='#live#'>select
<cfquery name='not' datasource='#live#'>
select *
from docType
where docType .dtID not in
<cfqueryparam value="devo.dtID" list="true">
</cfquery>
<cfquery name='devo' datasource='#live#'>select
<cfquery name='not' datasource='#live#'>
select *
from docType
where docType .dtID not in
<cfqueryparam value="devo.dtID" list="true">
</cfquery>
ASKER
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\Databas
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?!
[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\Databas
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?!
ASKER
Hey look, I got it!
<cfquery name='devo' datasource='#live#'>select
<cfquery name='not'datasource='#liv
select *
from docType
where docType.dtID not in
(select dtID from docTypeDev)
</cfquery>
<cfquery name='devo' datasource='#live#'>select
<cfquery name='not'datasource='#liv
select *
from docType
where docType.dtID not in
(select dtID from docTypeDev)
</cfquery>
ASKER
How should I do the point breakdown/answer?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, I'm going to try that solution again with the same scenario...
ASKER