Access and Sql DB query 1

[RosieGp]

I have to pull data from access DB based on parameters from SQl DB.
I'm doing the following:
SQL Query:
<cfquery datasource="MasterSQL" username="***" password="***" name="SQLQRY">
Select Employee.Emp_Name, Salary.Emp_ID
From Employee inner join Salary on (Employee.Emp_ID = Salary.Emp_ID) AND (Employee.SectionName = Salary.SectionName)
Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar"
</cfquery>

ACCESS Query:
<cfquery datasource="MasterDB" name="AccessQRY">
Select Emp_Info.Desc, Emp_Pro.Emp_ID
From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)</cfquery>
<cfdump var = "#AccessQRY#">

Now I only want The Emp_Info.Desc to showup when I run the query based on ----- Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar"
------ condition and only thing connecting the 2 DB's is the Emp_ID.
How can I do this...

 

[cfSearching]

I do not quite understand the question. You are trying to retrieve information from two separate databases. What are the two database types:

1) MS Access and
2) ( ? )

Are they on the same server and is there any link between them? ie In MS Access you can create a link to a table in an external database


----------------------------------

http://cfsearching.blogspot.com/

 

[RosieGp]

1) Sql SERVER AND 2) MS Access
They are on the same coldfusion server. No links....
the only way to retrieve Desc from ms access db is to match Emp_ID in Emp_Pro table (Ms Access) and Emp_ID in Salary table (Sql Server).

 

[cfSearching]

As they are on the same server, the easiest option is to create a link between the two. Either

1) Create a linked server on the MS SQL side OR
2) Some sort of linked table on the MS Access side.

Then you query both db's within the same cfquery.

Otherwise, you have to pass in a _list_ of id's from the first query into the second query, via a WHERE ColumnName IN (...list of values ..) clause. That technique is okay for small lists, but the more values in the list the slower the query.

<cfquery name="query2" ...>
SELECT ....
WHERE  Emp_ID IN 
(
   <cfqueryparam 
         value="#ValueList(query1.Emp_ID)#" 
         cfsqltype="cf_sql_integer"
         list="true"
    >

)

----------------------------------

http://cfsearching.blogspot.com/

 

[RosieGp]

So do i need 3 queries...
<cfquery datasource="MasterSQL" username="***" password="***" name="SQLQRY">
Select Employee.Emp_Name, Salary.Emp_ID
From Employee inner join Salary on (Employee.Emp_ID = Salary.Emp_ID) AND (Employee.SectionName = Salary.SectionName)
Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar"
</cfquery>

ACCESS Query:
<cfquery datasource="MasterDB" name="AccessQRY1">
SELECT Emp_ID
From Emp_Pro
WHERE Emp_ID IN
( <cfqueryparam
value="#ValueList(query1.Emp_ID)#"
cfsqltype="cf_sql_integer"
list="true"
> )
<cfquery datasource="MasterDB" name="AccessQRY2">
Select Emp_Info.Desc, AccessQRY1.Emp_ID
From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)
</cfquery>
<cfdump var = "#AccessQRY2#">

something like this...

 

[cfSearching]

No.

If the MS Access query should only return the Emp_ID's matched in the first query, you _add_ the IN (...) clause to your existing Access query:

<cfquery datasource="MasterDB" name="AccessQRY">
     Select Emp_Info.Desc, Emp_Pro.Emp_ID
     From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)

    WHERE Emp_Info.Emp_ID IN 
    (  <cfqueryparam
         value="#ValueList(NameOfFirstQueryHere.Emp_ID)#"
         cfsqltype="cf_sql_integer"
         list="true">
    )  
</cfquery>

----------------------------------

http://cfsearching.blogspot.com/

 

[RosieGp]

How can i put a like condition in the where clause.
The form number from the first query looks like the following:
A 1000
A 1123
A 23456
B 450674

The form number in the second query looks like
1000**
1123***
23456**
450674***

I looked at the escape character but it works for only one char...

I really appreciate your help...

 

[cfSearching]

You have to use the wildcard character for your database. IIRC, it is "%" for both MS SQL and Access. So this would find all records where the column values starts with '1000'.

WHERE  ColumnName LIKE '1000%'

BUT you cannot combine a LIKE operator with the IN clause above, if that is what you are thinking.

BTW, if you are doing a lot of database work you should really get familiar with your database's documentation. For MS SQL the Books Online are excellent.

http://msdn.microsoft.com/en-us/library/ms189454.aspx

----------------------------------

http://cfsearching.blogspot.com/

 

[RosieGp]

Can i 3 queries and do something like the following:

<cfquery datasource="MasterSQL" username="***" password="***" name="SQLQRY">
Select Employee.Emp_Name, Salary.Emp_ID
From Employee inner join Salary on (Employee.Emp_ID = Salary.Emp_ID) AND (Employee.SectionName = Salary.SectionName)
Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar"
</cfquery>

<cfquery datasource="MasterDB" name="AccessQRY">
Select Emp_Pro.Emp_ID
From Emp_Pro
WHERE Emp_Pro.Emp_ID like ("%#ValueList(SQLQRY.Emp_ID)#%")
</cfquery>

<cfquery datasource="MasterDB" name="AccessQRY2">
Select Emp_Info.Desc,
From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)
WHERE Emp_Info.Emp_ID IN

this block will change too...
( <cfqueryparam
value="#ValueList(SQLQRY.Emp_ID)#"
cfsqltype="cf_sql_integer"
list="true">
)

</cfquery>

 

[cfSearching]

No. You can only use LIKE to compare the value of a single record at a time. If you think about what you are trying to do you will see why it will not work.

Your sql

    WHERE Emp_Pro.Emp_ID like ("%#ValueList(SQLQRY.Emp_ID)#%")

would translate to this:

     WHERE  Emp_Pro.Emp_ID LIKE '%1000,1123,23456%'

It is highly doubtful a single Emp_ID record has a value like '1000,1123,23456'. You either have to link the databases and use LIKE with a JOIN. Or create a very ugly loop that generates one LIKE statement for each value in the SQLQRY query.



----------------------------------

http://cfsearching.blogspot.com/

 

[cfSearching]

One other option you might try is a QoQ (Query of Queries). Note, a QoQ is NOT a database query.

http://livedocs.adobe.com/coldfusion/8/htmldocs/using_recordsets_3.html

Revert to your original two queries, then try adding a third query (a QoQ) to apply the LIKE clause. I do not recall if this syntax will work off the top of my head. So you will have to try it yourself.

<cfquery dbtype="query" name="ThirdQuery">
SELECT ...etc...
WHERE QueryName1Here.Emp_ID LIKE QueryName2Here.Emp_ID + '%'
</cfquery>


----------------------------------

http://cfsearching.blogspot.com/

 

[cfSearching]

Good Luck

----------------------------------

http://cfsearching.blogspot.com/

 

[RosieGp]

Hi cfSearching,
I tried to pulling ID from SQL, then from Access and then QoQ, But the problem is Sql return me only one ID but Access will return all the id has (30000 records) and then perform QoQ which takes all the time in world and thus will hang up the server...

So I changed my Query like the following:

<cfquery datasource="MasterSQL" username="***" password="***" name="SQLQRY">
Select Employee.Emp_Name, Salary.Emp_ID
From Employee inner join Salary on (Employee.Emp_ID = Salary.Emp_ID) AND (Employee.SectionName = Salary.SectionName)
Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar"
</cfquery>

<cfquery datasource="MasterDB" name="AccessQRY">
Select Emp_Pro.Emp_ID
From Emp_Pro 
WHERE Emp_Pro.Emp_ID like [COLOR=red]'%#Right(SQLQuery.FormNumber,4)#%'[/color]
</cfquery>

<cfquery datasource="MasterDB" name="AccessQRY2">
Select Emp_Info.Desc,
From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)
WHERE Emp_Info.Emp_ID IN 
( <cfqueryparam
value="#ValueList([COLOR=red]AccessQRY.Emp_ID[/color])#"
cfsqltype="cf_sql_integer"
list="true">
) 

</cfquery>

Now the issue is the firts query will return an ID but access is always hung up on the very first ID it get, so if the i get the new ID from SQL, access shows me records from the previous Id.
Please let me know if something is wrong with what I'm trying to do...
I really appreciate your help...

 

[cfSearching]

Read my previous responses again, as well as those on your other threads. They explain why that code does not work and suggest other alternatives.

Also, if it is an option, you should seriously consider creating a link between the databases. Then you would not have to use a QoQ as a work-around.

----------------------------------

http://cfsearching.blogspot.com/