synergeticsoul
asked on
Using regular expressions in a link
Hi.
I have a calendar application with various showTo 'views'. There are 2 problems: the datatypes for the primary to foreign key in the tables used are different. The calendar table has the field- showId, but it is a varchar (and the table already has a lot of data), while the corresponding foreign key in the showTo table, for showId is an integer. The application was originally built to insert "x" number of showTo (showId) variables in the calendar table for each entry. However, as the showTos grow, i don't want to continue copying the relevant pages for the application and inserting in the appropriate new folder. What are the best solutions? I ultimately just want this one application and perhaps using links and regular expressions, display the appropriate data. I already have well over 100 showIDs and I want to get control of this before I considered the following:
1) Using like within my query to obtain the one value for the particular view:
<cfquery name="getweekcal" datasource="#application.d
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId like '%10%'
ORDER BY calendar.calendardate
</cfquery>
This works, but I will still need to copy the pages of the application into various folders for all new views and change the values.
2) Link Option with a regular expression
This seems like a good choice, but I don't know how I would construct the regular expression to find all values where showID LIKE ShowId like '%10%'.
I thought I could do: <a href="test_calendar.cfm?sh
Please advise. What are my options so that I can gain control of this application?
Thank you.
I have a calendar application with various showTo 'views'. There are 2 problems: the datatypes for the primary to foreign key in the tables used are different. The calendar table has the field- showId, but it is a varchar (and the table already has a lot of data), while the corresponding foreign key in the showTo table, for showId is an integer. The application was originally built to insert "x" number of showTo (showId) variables in the calendar table for each entry. However, as the showTos grow, i don't want to continue copying the relevant pages for the application and inserting in the appropriate new folder. What are the best solutions? I ultimately just want this one application and perhaps using links and regular expressions, display the appropriate data. I already have well over 100 showIDs and I want to get control of this before I considered the following:
1) Using like within my query to obtain the one value for the particular view:
<cfquery name="getweekcal" datasource="#application.d
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId like '%10%'
ORDER BY calendar.calendardate
</cfquery>
This works, but I will still need to copy the pages of the application into various folders for all new views and change the values.
2) Link Option with a regular expression
This seems like a good choice, but I don't know how I would construct the regular expression to find all values where showID LIKE ShowId like '%10%'.
I thought I could do: <a href="test_calendar.cfm?sh
Please advise. What are my options so that I can gain control of this application?
Thank you.
ASKER
The problem is the 2 different datatypes. The application is old. When I tried the above, I get:
"Syntax error converting the varchar value '1,2,6,10,40' to a column of data type int."
I am trying to do this from a link. Each record has various showTos, for example, one record may have, 1, 5, 10, 30 as possible showTos. As the application grows, I am trying to fix the problem before it gets out of hand. I am not sure if I should be doing replacements or what..
please advise... thank you...
"Syntax error converting the varchar value '1,2,6,10,40' to a column of data type int."
I am trying to do this from a link. Each record has various showTos, for example, one record may have, 1, 5, 10, 30 as possible showTos. As the application grows, I am trying to fix the problem before it gets out of hand. I am not sure if I should be doing replacements or what..
please advise... thank you...
Oh then you can use IN
<cfquery name="getweekcal" datasource="#application.d
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId IN (#URL.ShowID#)
ORDER BY calendar.calendardate
</cfquery
<cfquery name="getweekcal" datasource="#application.d
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId IN (#URL.ShowID#)
ORDER BY calendar.calendardate
</cfquery
If you need to have single quotes around each value, then you can use the ListQualify() function:
<cfquery name="getweekcal" datasource="#application.d
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId IN (#ListQualify(URL.ShowID, "'")#)
ORDER BY calendar.calendardate
</cfquery>
Note that the function's second argument is a single quote surrounded by double quotes.
<cfquery name="getweekcal" datasource="#application.d
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId IN (#ListQualify(URL.ShowID, "'")#)
ORDER BY calendar.calendardate
</cfquery>
Note that the function's second argument is a single quote surrounded by double quotes.
ASKER
what is the ListQualify() function?
Neither solution works because of the data type difference. i still get : "Syntax error converting the varchar value '1,2,6,10,40' to a column of data type int."
Or I get the calendar minus the event data. I also tried casting, but still get the same errors.
Are there other options to explore that can be done immediately? I have a lot of data in the table now. Each record has various showTo variables as varchar, from the showTo table (int).
??Help.
Neither solution works because of the data type difference. i still get : "Syntax error converting the varchar value '1,2,6,10,40' to a column of data type int."
Or I get the calendar minus the event data. I also tried casting, but still get the same errors.
Are there other options to explore that can be done immediately? I have a lot of data in the table now. Each record has various showTo variables as varchar, from the showTo table (int).
??Help.
ASKER
FYI, there are currently 3943 total records in the table.
ASKER
Could I possibly use a combination of "Like" and "In" in my query?
ListQualify will not work since the data type is int. It is for strings.
The problem is that you are sending it in in quotes which makes it see it as a varchar instead of a string of ints.
Try this :
<cfset tempqry = "SELECT * FROM calendar WHERE calendar.calendardate between #begofweek# and #endofweek# AND ShowId IN (#URL.ShowID#) ORDER BY calendar.calendardate">
<cfoutput>#tempqry#</cfout
<cfabort>
And see exactly what gets printed to the screen.
It should look like this (I filled in dates since I don't know what those will be):
SELECT * FROM calendar WHERE calendar.calendardate between 3/4/2004 and 3/11/2004 AND ShowId IN (1,2,6,10,40) ORDER BY calendar.calendardate
The problem is that you are sending it in in quotes which makes it see it as a varchar instead of a string of ints.
Try this :
<cfset tempqry = "SELECT * FROM calendar WHERE calendar.calendardate between #begofweek# and #endofweek# AND ShowId IN (#URL.ShowID#) ORDER BY calendar.calendardate">
<cfoutput>#tempqry#</cfout
<cfabort>
And see exactly what gets printed to the screen.
It should look like this (I filled in dates since I don't know what those will be):
SELECT * FROM calendar WHERE calendar.calendardate between 3/4/2004 and 3/11/2004 AND ShowId IN (1,2,6,10,40) ORDER BY calendar.calendardate
ASKER
I am still getting the same error-
"Syntax error converting the varchar value '1,2,6,10,40' to a column of data type int."
Please advise. What are my options besides rebuilding the table and datatypes which will be quite time-consuming?
"Syntax error converting the varchar value '1,2,6,10,40' to a column of data type int."
Please advise. What are my options besides rebuilding the table and datatypes which will be quite time-consuming?
ASKER
I also tried the following:
<cfquery name="getweekcal" datasource="#application.d
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
'#form.ShowID#'
<cfelse>'#URL.ShowID#'</cf
AND ShowId like
<cfif isDefined("Form.ShowID")>
'%#Form.ShowID#%'
<cfelse>'%#URL.ShowID#%'</
AND CAST(showId AS int)
Order BY calendarDate ASC
</cfquery>
I get the following error: Incorrect syntax near '20'.
<cfquery name="getweekcal" datasource="#application.d
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
'#form.ShowID#'
<cfelse>'#URL.ShowID#'</cf
AND ShowId like
<cfif isDefined("Form.ShowID")>
'%#Form.ShowID#%'
<cfelse>'%#URL.ShowID#%'</
AND CAST(showId AS int)
Order BY calendarDate ASC
</cfquery>
I get the following error: Incorrect syntax near '20'.
I do not think it should be deleted as the IN option will work.
I am sorry I did not respond after the post on the 8th, but I was out of town for a week.
synergeticsoul - the reason you got an error with incorrect syntax is that you left out the parenthesis. Look again at my example at what the SQL should look like. Then to test what yours looks like you do this :
<cfoutput>
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
'#form.ShowID#'
<cfelse>'#URL.ShowID#'</cf
AND ShowId like
<cfif isDefined("Form.ShowID")>
'%#Form.ShowID#%'
<cfelse>'%#URL.ShowID#%'</
AND CAST(showId AS int)
Order BY calendarDate ASC
</cfoutput>
You will see that you do not have the ( ) around the IN clause and that is causing your syntax problem.
I am sorry I did not respond after the post on the 8th, but I was out of town for a week.
synergeticsoul - the reason you got an error with incorrect syntax is that you left out the parenthesis. Look again at my example at what the SQL should look like. Then to test what yours looks like you do this :
<cfoutput>
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
'#form.ShowID#'
<cfelse>'#URL.ShowID#'</cf
AND ShowId like
<cfif isDefined("Form.ShowID")>
'%#Form.ShowID#%'
<cfelse>'%#URL.ShowID#%'</
AND CAST(showId AS int)
Order BY calendarDate ASC
</cfoutput>
You will see that you do not have the ( ) around the IN clause and that is causing your syntax problem.
ASKER
Thank You. MRichmon. I have been trying to test this and should have a response soon.
Thanks.
Thanks.
ASKER
I am still getting an error. This time the error is not a list, but invalid. Not sure what I am doing wrong. I am passing from a link. So on one page, the link may be: calendar.cfm?showid=1, but upon clicking on this, there is an error.
what is the error?
Can you give a URL so we can see it?
Can you give a URL so we can see it?
ASKER
Here is the actual error:
<ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '1'.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (39:1) to (39:58).
>
==========================
This is the code for the test link:
<a href="test_calendar.cfm?sh
==========================
This is the actual code for the page now:
<cfquery name="getweekcal" datasource="#application.d
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
'#form.ShowID#'
<cfelse>'#URL.ShowID#'</cf
AND ShowId like
<cfif isDefined("Form.ShowID")>
'%#Form.ShowID#%'
<cfelse>'%#URL.ShowID#%'</
AND CAST(ShowID AS int)
</cfquery>
<ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '1'.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (39:1) to (39:58).
>
==========================
This is the code for the test link:
<a href="test_calendar.cfm?sh
==========================
This is the actual code for the page now:
<cfquery name="getweekcal" datasource="#application.d
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
'#form.ShowID#'
<cfelse>'#URL.ShowID#'</cf
AND ShowId like
<cfif isDefined("Form.ShowID")>
'%#Form.ShowID#%'
<cfelse>'%#URL.ShowID#%'</
AND CAST(ShowID AS int)
</cfquery>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the late response, I have had no luck with this and have been trying to figure out what's wrong. MRichmon, thanks for your help. Points go to MRICHMON.
<a href="test_calendar.cfm?sh
Then on the page :
<cfquery name="getweekcal" datasource="#application.d
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId like '%#URL.SHowID#%'
ORDER BY calendar.calendardate
</cfquery>