How to connect solr to MS Sql Server in CF 2023

[AlexCraig]

I am creating a new thread for this question so someone else in the community might be able to easily find it.

I have searched far and wide and have discovered that CF 2023 supports a solr function that did not exist in CF 4.51.

I finally found this on Quora.

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

You can connect Solr with MS SQL Server by using the Solr Data Import Handler (DIH) and configuring it to fetch data from SQL Server. Here are the basic steps:

1. Install and configure Solr on your server.
2. Download the Microsoft JDBC driver for SQL Server.
3. Copy the JDBC driver JAR file to the Solr server.
4. Create a data source name (DSN) for the SQL Server database using the JDBC driver.
5. Configure the Solr DIH to use the SQL Server DSN as a data source.
6. Define the SQL queries that will fetch the data from the SQL Server database.
7. Run a full import to fetch the data from SQL Server into Solr.

Once the data is imported, you can use Solr to index and search the data.

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

I have already accomplished steps 1 thru 4.  I have not been able to find any info on how to accomplish steps 5, 6 & 7.

I presume I must then need to learn how to code the parallel /sql request handler.

Any help would certainly be appreciated.  Thanks in advance.

[Charlie Arehart]

Before going any further, the solr dih feature is only supported in cf enterprise and not cf standard.  Which are you using?

As for learning more on the dih feature, it was covered a lot back in 2012 when it's support was introduced with cf10, iirc. I'm on a phone as I write, so gathering up resources would be a bit challenging right now.

But in hearing that it's indeed a solr feature (not a cf one), that may help you broaden your own search for more info.

Finally, I'll note first that I don't think it requires the ms driver, so could work with the Adobe provided sql server driver. Second, people who don't use cf enterprise can of course still integrate db data into solr collections. Besides using cfindex and the like, many also switch to calling on the solr implemented in cf by way of making cfhttp calls that send rest commands to the solr instance. Nothing in the cf docs covers that, but again solr docs will discuss it, and cfhttp can make such calls from cf. 

[AlexCraig]

Morning Charlie,  Aha!  Your reply is very helpful.  Thank you.

We are running cf standard.  So my next step is to learn how to create a solr collection from 6 columns in a given table in our sql/server database that are used in searches.

I presume that would require coding a .cfm script.

I have not been able to find an example of such a script thus far.  Hopefully, you additional info will help me find one.  Thanks again.

[Charlie Arehart]

Well, first, you could do that with cfindex...as one could even back in cf 4.51 with Verity, before cf9 introduced solr to replace that. And its indeed documented--including an example, yay--at the CF docs page for the cfindex tag. Note that importing a query (the result of a cfquery, where you name the fields you want) entails using the CFINDEX  attributes type="custom" and the query attribute naming that cfquery. See the examples for more, at the bottom of the page, especially example 3 and perhaps 4 and 5:

https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-tags/tags-i/cfindex.html

Finally, in case anyone else may seek to use that data import handler feature with CF (which was added in Cf10, but enterprise-only), I have found that somehow curiously the docs for it seem to have disappeared from the Adobe site. Can't explain it...and can't find it even via archive.org. But here (for folks on enterprise, not CF Standard) is a link to the page in the PDF of the docs for CF10:

https://help.adobe.com/en_US/ColdFusion/10.0/Developing/coldfusion_10_dev.pdf#page=508 

But, Alex, I think the first link above should get you going. Let us know how it goes.

[AlexCraig]

Appreciate the follow-up.  Flat excellent, Sir!

Example #3 should get me started.

Since I'm in it this deep, I may even create a separate collection for Job Titles.  Even though most of our recruiting is for technical disciplines, titles are often nebulous.  But on occasion they can help weed out the wheat from the chaff.

Will let you know how I make out.

[AlexCraig]

OK.  Using Example 3 as a template I have authored the following .cfm code.

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

<!---EXAMPLE #3: Index a QUERY (type = "custom") using custom1. ------------>
<!--- Retrieve data from the table. --->
<cfquery name="getJobData" datasource="TEN">
select * from tblJobOrders
</cfquery>
<!--- Update the collection with the above query results. --->
<!--- key is JobOrderNum in the tblJobOrders table. ---->
<!--- body specifies the columns to be indexed for searching. --->
<!--- custom1 specifies the value of the ??? column. --->
<cfindex
query="jobs"
collection="TPI_Jobs"
action="Update"
type="Custom"
key="JobOrderNum"
title="title"
body="JobOrderNum,JobTitle,JobReq1,JobReq2,JobReq3,JobReq4,JobPref1,JobPref2,JobPref3,JobPref4,JobReqNotes"
custom1=" "
>
<h2>Indexing Complete</h2>

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

As I understand it, the example was to show how to update a specific record using the value of Custom1.

I've tried entering a value of "0" and " " to no avail.  I keep getting an error for Custom1.

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

The error occurred in D:/www_root/thebes/getJobData.cfm: line 18

16 : title="title" 17 : body="JobOrderNum,JobTitle,JobReq1,JobReq2,JobReq3,JobReq4,JobPref1,JobPref2,JobPref3,JobPref4,JobReqNotes" 18 : custom1="0" 19 : > 20 : <h2>Indexing Complete</h2>

Is there a value I can enter to instruct it to process the entire results of the query OR do I need to modify the query in some other respect to get it to populate the collection I need populated.

Thanks in advance!

[Charlie Arehart]

Alex, unless and until someone somehow readily recognizes this as some genetic problem with cfindex, we need more detail on your error. You may say you see nothing more, but look at cf's logs (especially the application.log and the exception.log). Then look also at those cfusion/jetty/logs as discussed in the other thread you'd opened several days ago.

If you may ask, "have you run that sample code there", I will say I have not. As before, I'm writing from a phone as I see your reply early in my morning (US Central time. And I reply when I can with what I may know or want to share without need of testing things. I often hope to follow up later, but sometimes the day gets away and I can lose track, until perhaps a later reply from someone here gets my attention.) 

[AlexCraig]

Hey Charlie,  Per your instructions the applilcation log appears to give no additional info.

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

An error occurred while indexing a Index collection.Could not find QUERY [jobs]. The specific sequence of files included or processed is: D:\www_root\thebes\getJobData.cfm, line: 18

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

The exception log below for the query shows a few lines that appear to confirm what we already know and a ton of addtional entries that are well beyond my level of understanding.

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

8365 "Error","ajp-nio-127.0.0.1-8022-exec-3","02/05/24","10:00:28","","An error occurred while indexing a Index collection.Could not find QUERY [jobs]. The specific sequence of files included or processed is: D:\www_root\thebes\getJobData.cfm, line: 18"
8366 coldfusion.tagext.search.IndexTag$CannotFindSomethingException: An error occurred while indexing a Index collection.
8367 at coldfusion.tagext.search.IndexTag.doQueryUpdate(IndexTag.java:746)
8368 at coldfusion.tagext.search.IndexTag.doStartTag(IndexTag.java:349)
8369 at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:5088)
8370 at cfgetJobData2ecfm2075999407.runPage(D:\www_root\thebes\getJobData.cfm:18)
8371 at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:256)
8372 at coldfusion.tagext.lang.IncludeTag.handlePageInvoke(IncludeTag.java:749)
8373 at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:578)
8374 at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
8375 at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:613)
8376 at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:43)
8377 at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
8378 at coldfusion.filter.PathFilter.invoke(PathFilter.java:162)
8379 at coldfusion.filter.IpFilter.invoke(IpFilter.java:45)
8380 at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30)
8381 at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:97)
8382 at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
8383 at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
8384 at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:60)
8385 at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
8386 at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
8387 at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62)
8388 at coldfusion.CfmServlet.service(CfmServlet.java:231)
8389 at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:311)
8390 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:209)
8391 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
8392 at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:46)
8393 at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:47)
8394 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
8395 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
8396 at coldfusion.inspect.weinre.MobileDeviceDomInspectionFilter.doFilter(MobileDeviceDomInspectionFilter.java:57)
8397 at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:47)
8398 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
8399 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
8400 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
8401 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
8402 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
8403 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
8404 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
8405 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:481)
8406 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130)
8407 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
8408 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
8409 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
8410 at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:446)
8411 at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
8412 at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:926)
8413 at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1791)
8414 at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
8415 at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
8416 at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
8417 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

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

[Charlie Arehart · Accepted solution]

The very phrase you quote--from the message in the application.log--does indeed show "additional info", at least in that you'd not shown previously the error message saying, "Could not find QUERY [jobs]". And the exception log indicates the same error. 

So help us out: had you seen that before and not told us? Or did you miss it being in what you've just posted? Either is OK. Just help us understand.

More to the point, if you may feel it "doesn't tell me anything meaningful ", then I'll share first that I see now how that example code (from the Adobe docs) happened to use a cfquery called "getJobData" but then referred in the cfindex to "jobs".  That seems a mistake, easily corrected by them or you: did you perhaps just use that EXACT code, changing only the datasource value and sql statement? If so, then just change either that cfquery name value or the cfoutput query value, so that they match. 

Second, if you may respond with more questions, it may help for you to clarify something: are you perhaps the owner of this web site (with this code from cf 4.51, from 25 years ago), who is not really a developer otherwise? And are you perhaps "just needing to get this web site running on a modem cf version", for whatever reasons?  If all that's true, knowing it may better help us in guiding you. We do tend to get questions here from developers rather than others, and we may leave out details or presume something's more apparent than maybe it woukd be for someone in that situation. 

But maybe the suggestions before that question will get you over this hump and you'll be all set. 

[AlexCraig · Accepted solution]

To answer your first question, I'd seen the "Could not find QUERY [jobs]" a number of times as I was trying to perfect the code as it was choking on earlier lines in the code as I worked my way through getting it correct and thought it was a generic message of little consequence.  I apologize for my ignorance.

The example in adobe docs was [query="getCourses"].  I had originally changed it to "getJobData" and then changed it to "jobs" because our current cfindex code scheme in 4.51 uses "jobs".  Of course, I am trying to keep as much of the nomenclature in 2023 the same as that used 4.51  in possible.

To answer your next question, yes I own the website.  As I believe I mentioned in a previous thread, the 4.51 development was done by a young man I placed as a CF developer who was touted by his boss as a "whiz kid" after he worked for him for a couple of years.  The "whiz kid" did the inital 4.51 coding for us as a side job and his work was exceptional.  Given the size of our system, we only found 3 bugs in it after pounding on it for a full year.

 As to myself, a long, long time ago, in a different universe I did a fair mount of COBOL programming on a Series 1 IBM mini-computer.  Bottom line, I learned enough about syntax & logic to be able to handle "maintenance programming" on our 4.51 system.  I could read the "whiz kids" code well enough to modify it as needed without issue.  Bottom, bottom line writing new CF code from scratch is a challenge.  I can learn it, but it requires a fair amount of time & effort.

With all that said, your last suggestion concerning the code did the trick.  Changing the input & output query names to match (jobs); commenting out the "custom1" field solved my problem. I now have a collection with 5500+ documents in it.  Thanks for your patience & help Charlie.  Apparently, "old dogs can learn new tricks.

For anyone who stumbles across this thread who needs a template from which to create a collection from a database table, this one worked.

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

<!---EXAMPLE #3: Index a QUERY (type = "custom") using custom1. ------------>
<!--- Retrieve data from the table. --->
<cfquery name="jobs" datasource="TEN">
select * from tblJobOrders
</cfquery>
<!--- Update the collection with the above query results. --->
<!--- key is JobOrderNum in the tblJobOrders table. ---->
<!--- body specifies the columns to be indexed for searching. --->
<cfindex
query="jobs"
collection="TPI_Jobs"
action="Update"
type="Custom"
key="JobOrderNum"
title="title"
body="JobOrderNum,JobTitle,JobReq1,JobReq2,JobReq3,JobReq4,JobPref1,JobPref2,JobPref3,JobPref4,JobReqNotes"
>
<h2>Indexing Complete</h2>

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

[Charlie Arehart]

Really glad to hear that all is now resolved for you. And FWIW I don't recall or find any discussion by you of another developer in that other thread , which is indeed why I'd felt compelled to ask in this one. 🙂 Again, it was more to help us know how best to help you. Even with this matter resolved (for now), some folks may keep in mind the discussion, should you open other threads.

Speaking of that other thread, it does SEEM that all is now resolved also, but you may want to drop a note of conclusion there for the sake of any others who may find the discussion. Finally, note that you can mark one or more replies in each thread as an "answer"...and I realize that no one may seem "the complete answer", so it's ok if you pick more than one (which can be your own and someone else's, if you like).

But hope for you sake that all else with your migration may go smoothly.

[AlexCraig]

I had sent you a private message a week or so ago.  Perhaps the info about the initial developer was in it.

Anyway, thanks again for the much appreciated assitance.

As much as I wish otherwise, you'll probably see addtional threads going forward.  🙂

Also, will mark this & the other thread.

[AlexCraig]

I am so excited I had to add an addendum to this thread.

After creating a successful collection for the "jobs" module of our system, I simply searched the code for the string "verityCollection_Jobs" and changed it to "solrCollection_Jobs". (Used by CFINDEX COLLECTION)

Did some testing and I'll be darned .... all of the functions seemed to work: modifying/adding/deleting/searching!!  Of course, I'll need to wait & see what happens with the Edit/Validation Bug report.  However, if they can cure that.  I'll be good to go on one of the 3 major modules in our system. How 'bout that!