• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Advice on how to reuse old MS Access query in MySQL

Contributor ,
May 06, 2021 May 06, 2021

Copy link to clipboard

Copied

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

Views

571

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Expert , May 10, 2021 May 10, 2021

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

Votes

Translate

Translate
Community Expert ,
May 10, 2021 May 10, 2021

Copy link to clipboard

Copied

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. 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
May 10, 2021 May 10, 2021

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 10, 2021 May 10, 2021

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
May 10, 2021 May 10, 2021

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 10, 2021 May 10, 2021

Copy link to clipboard

Copied

LATEST

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation