solr query output format Issue

[AlexCraig]

Greetings all.

OK.  I have searches against a collection generated by a script containing about a dozen sql/server datafields running fine in one of our modules used to search jobs.

However, I have a collection which was created against 60,000+ resumes utilizing the "Add New Solr Collection" option in the CF Administrator which is giving me trouble.

Basically, the collection was generated against a directory of numbered files 1 - 60000+ with .doc .docx .txt & .pdf extensions.  (e.g  1234.doc).

The error I am getting is:

**********************************

[Macromedia][SQLServer JDBC Driver][SQLServer]There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

******************************

The line involved is #47 as shown in the code below.

Here is the code I am using to try & utilize the output generated by a keyword search against the collection:

**********************************

<CFSEARCH NAME="applicants_#var#"
COLLECTION="#solrCollection_Resumes#"
TYPE="standard"
CRITERIA="#Form.keywords#" >

<!--- generate the list of applicants --->
<cfloop query="applicants_#var#" >
<cfquery datasource="#dsn#">
insert into tblCurrentSearches_apps (currentSearchID, appID)
values (#getCurrentSearchID.theSearchID#)         <----  Line 47
</cfquery>
</cfloop>

</cfloop>

<cfquery name="getTotalApps" datasource="#dsn#">
select count(*) as numApps
from tblCurrentSearches_apps
where currentSearchID = #getCurrentSearchID.theSearchID#
</cfquery>

************************************

I presume my problem involves parsing.  If so, can someone tell me what the format of the returns to the query would be, so I can get a read on the number of columns in the return.

As always, thanks in advance.

[AlexCraig]

After thinking this through, I now realize I am going to need to repopulate the collection with a script that includes both the Applicant ID from the database and corresponding Resume ID from the file.  There is no way that generating the collection from tool would have included the ID of the applicant to whom the resume belongs.

My initial analysis in the above italicized message was incorrect.  Although I will need to break my large collection down into 3 or 4 separate collections to speed up processing, I still need to solve the problem in my initial post. And to do that I believe I will need an answer to the question in my 2nd reply.  Namely what type of data is returned when you query a collection built by the Admin collection tool based on 60,000+ files. I would assume the file names would be returned as results.  Is that correct?  And, if it is, why am I getting the error in my initial post?  Thank you!

[AlexCraig]

I do have a follow-up question to try & understand how I'm going to solve this issue.

If I query this Resumes collection I created, when I query the collection I am assuming the query results will be the file names of the resumes that match the query.  Is that correct?  Please advise.  Thanks.

[AlexCraig · Accepted solution]

I finally solved the problem.

In CF 4.51 we used a variable to for  the COLLECTION="#verityCollection_Resumes#" parameter.  So desiring to maintain the same architecture.  I had used COLLECTION="#solrCollection_Resumes#".  Of course, I changed the CFset path accordingly.

The code ran for about 90 seconds before it produced the error mentioned in my original post.  I presumed it was taking that long to parse the collection.  However, it may have simply gone into some sort of loop and was causing CF to gobble up 2 GB of memory before the error page appeared.

Well, I spotted an example for CFsearch in the User Guide which accessed the explict name of the collection, to wit: COLLECTION="Resumes" & viola, it worked and did so in the couple of blinks of the eye!

I have one more issue to address related to this collection.  But I'll start another thread for it.