Functions within cfquery/cfstoredproc loss of datasource

When is the data-source of your query not the data-source of the query ?

So I have a query block that call a table, and within the block I have a function that gets a value I need in the query.

<cfquery datasource="DB1">

Select *

from myTableInDB1

Where id = <cfqueryparam value="#val(_getID())#" cfsqltype="cf_sql_numeric">

</cfqueryparam>

<cffunction name="_getID" returntype="numeric" output="yes">

<cfset q=" ''">

<cfquery datasource="DB2" name="q">

Select top 1 id from AnotherTable

</cfquery>

<cfreturn>

</cfreturn>

So I want everything from myTableInDB1 that is in the DB1 data-source. and I want to pull back everything that has an ID equal to the response from a function.

Sadly that function contains another query which gets an ID from AnotherTable from Another datasource (DB2).

So what happens is I get an error that says myTableInDB1 does not exit in data source, which leads to lots of toy being chucked all over the place. and a moment remenisant of installing a printer in windows 98 "WHAT DO YOU MEAN IT RIGHT @#+-ing THERE!!!"

But what realy happens its the datasource in _getID() is used for both queries.

This issue was compounded that the _getID() set an application variable the first time it was executed, so on subsequent calls to the page the First query would run fine, but err every time I reset CF.

To solve this:

<cfset thisid=" val(_getID())">

<cfquery datasource="DB1">

Select *

from myTableInDB1

Where id = <cfqueryparam value="#thisID #" cfsqltype="cf_sql_numeric">

</cfqueryparam>

Another Head vs Desk moment.

NOTE: This works/doesn't works for both cfquery and cfstoredproc</cfquery></cfset></cfset></cffunction></cfquery>