Advice on how to reuse old MS Access query in MySQL

[John_Allred]

Hi!

I'm reviving an application from nearly 20 years ago. In going through the CFML, I find that I created several queries within the Access database that I used at the time, and I used them, programatically, like tables.

I have little knowledge about stored procedures, but my guess is that if I used the old query to create a stored procedure in the MySQL database, I could access it through my CFML the same way I did previously. That is, treating it like a table within CFML queries. I'd like to use the following code (calling AllRecordsUnion) in my new site without modification:

<CFQUERY DATASOURCE="county" NAME="list_links_county">
SELECT Links.APPROVE, Links.TYPE_DESC, Links.MAIN_NBR, Links.BEFOR, Links.LINK, Links.LINK_DISPL, Links.AFTER, Links.DESCRIP, Links.MUSIC, Links.SLOW, Links.ADDED, Links.LINK_DISPL, AllRecordsUnion.CNTY_NAME, AllRecordsUnion.LINK_RECORD
FROM Links INNER JOIN AllRecordsUnion ON Links.RECORD = AllRecordsUnion.LINK_RECORD
ORDER BY AllRecordsUnion.CNTY_NAME, Links.LINK_DISPL;
</cfquery>

I would just try this right now, but I'm quite a ways from having an appliation that I could use to test it. The original was built in FuseBox, and I'm sorting through a redesign. At this point, I just want to get the database squared away.

Thanks!

John

[BKBK]

I am sorry I don't quite understand your question. Have you already switched database from MS Access to MySQL?

If so, then MS Access will be irrelevant to the discussion. Your question will be about MySQL stored procedures. 

[John_Allred]

Hi, BK!

I included the reference to MS Access, since I had originally developed the site using that. My question, I'm sure, was murky at best. In summary:

In my Access-powered website, I created queries in access, and within my CFML I treated them exactly like tables. The MySQL interface is different. Having never dealt with a stored procedure before, I was seeking some guidance.

At this point, I have my stored procedure in MySQL. I just don't know how to address it in CFML.

If you knew that the MySQL datasource, "county," existed and that it included all the tables shown in the query above, as well as a stored procedure named "AllRecordsUnion," how would you code the query above to utilize AllRecordsUnion?

Thanks!

John

[George____]

If you wanted to keep the query the same on the ColdFusion side then in MySQL AllRecordsUnion should be created as a MySQL View.    By creating it as a stored procedure you'll have to change the logic on the ColdFusion side to call the stored procedure.   You should be able to take your select statement from the stored procedure or MS Access and use it to create the view.

If you decide to stick with stored procedures you can either use cfstoredproc or cfquery with a Call statement to run a stored procedures, but cfstoredproc would make more sense.

I'd also caution you against using queries that were wrote 20 years ago.   It's very likely they could have SQL injection vulnerabilties.    I can't tell you how much old stuff I've seen with things like "where rowid = #URL.rowid#"

[John_Allred]

Thanks so much, George. I'm not married to a stored procedure. It was the only approach I was aware of. Your advice leads me into a new enquiry. I really appreciate that. I know enough about MySQL to get basic things done, but not enough to really understand the possibilities.

Thanks!
John

[BKBK · Accepted solution]

... If you knew that the MySQL datasource, "county," existed and that it included all the tables shown in the query above, as well as a stored procedure named "AllRecordsUnion," how would you code the query above to utilize AllRecordsUnion?

By @John_Allred

<cfstoredproc procedure="AllRecordsUnion" datasource="county">
	<cfprocresult name="spResult">
</cfstoredproc>
<!--- Show the result --->
<cfdump var="#spResult#" label="Stored procedure result">