Skip to main content
John_Allred
John_Allred
Inspiring
May 6, 2021
Answered

Advice on how to reuse old MS Access query in MySQL

  • May 6, 2021
  • 1 reply
  • 832 views

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

    This topic has been closed for replies.
    Correct answer BKBK

    ... 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">

    1 reply

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    May 10, 2021

    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
      John_AllredAuthor
      Inspiring
      May 10, 2021

      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____
      George____
      Inspiring
      May 10, 2021

      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#"

        Terms & ConditionsAccessibility statement
        Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices