473,396 Members | 2,098 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

GET and POST dropping wildcard characters in DB query

Hello experts,

I have been struggling for days to solve this problem and every
suggestion I find via Google does not work for me. There is probably a
solution out there that will do what I want, but I probably have not
properly implemented the solutions I find.

I am trying to make a page to allow personnel the ability to search our
backend DB (which is Ingres 2.0) through a web interface. Ingres has a
cgi program called ICE that can receive SQL queries and return data to
format. I have lots of pages setup that query correctly and work really
well. The one I am struggling with is searching our parts database
because I want to allow wildcard searches. The Ingres wildcard is a %
sign so I can input 011% and it will find any item number that starts
with 011. I can use %011% and it will find any part with 011 in the
string. I only care about three fields item_no, description_1, and
description_2 for searching.

I have set up a simple form that queries properly using only hidden
variables and GET and no JS, but the wildcards have to be explicitly
entered in the form. I want use JS to validate the form and add the
wildcards for the user.

So, here is the incredibly ugly code I hacked together that almost
achieves what I want. The problem is using either GET or POST for the
action, the query the DB receives does not have "item_no like '011%' "
instead it is "item_no like '011' " so it returns ONLY the item with
item_no EXACTLY 011. Not helpful. If I use an alert to display the
send_string before the submit, it has wildcards in the string as I
expect but for some reason when I look in the DB logs and the apache
logs the query has no % and the GET statement passed to apache has no %
signs. What can I do to validate the form and get the strings passed to
the query with the % wildcards intact?

I want the user to only have to fill in what he cares about and then I
can validate and correctly compose the SQL query from there. The most
common example is to fill in the item_no with a string. I then make the
two description fields a single % so the query returns any items that
match the item_no without caring about the description fields. That is
why the code checks if all fields in one group are empty, it makes the
contains parameter for this field a single %.

Anyone who can steer me the correct way will be much appreciated.
<SCRIPT LANGUAGE="Javascript">
<!--
function checkme(parts) {
var start_string=document.forms[0].start_item_no.value;
var length_start_string=start_string.length;
var contains_string=document.forms[0].contains_item_no.value;
var length_contains_string=contains_string.length;
var end_string=document.forms[0].end_item_no.value;
var length_end_string=end_string.length;
if (length_start_string != 0) {start_string=start_string+"%"};
if (length_contains_string != 0)
{contains_string="%"+contains_string+"%"};
if (length_end_string != 0) {end_string="%"+end_string};
if (length_start_string== 0 && length_contains_string==0 &&
length_end_string==0) {contains_string="%"};
var
send_string="start_item_no="+start_string+"&contai ns_item_no="+contains_string+"&end_item_no="+end_s tring;
var start_desc1=document.forms[0].start_description_1.value;
var length_start_desc1=start_desc1.length;
var contains_desc1=document.forms[0].contains_description_1.value;
var length_contains_desc1=contains_desc1.length;
var end_desc1=document.forms[0].end_description_1.value;
var length_end_desc1=end_desc1.length;
if (length_start_desc1 != 0) {start_desc1=start_desc1+"%"};
if (length_contains_desc1 != 0)
{contains_desc1="%"+contains_desc1+"%"};
if (length_end_desc1 != 0) {end_desc1="%"+end_desc1};
if (length_start_desc1==0 && length_contains_desc1==0 &&
length_end_desc1==0) {contains_desc1="%"};
send_string=send_string+"&start_description_1="+st art_desc1+"&contains_description_1="+contains_desc 1+"&end_description_1="+end_desc1;
var start_desc2=document.forms[0].start_description_2.value;
var length_start_desc2=start_desc2.length;
var contains_desc2=document.forms[0].contains_description_2.value;
var length_contains_desc2=contains_desc2.length;
var end_desc2=document.forms[0].end_description_2.value;
var length_end_desc2=end_desc2.length;
if (length_start_desc2 != 0) {start_desc2=start_desc2+"%"};
if (length_contains_desc2 != 0)
{contains_desc2="%"+contains_desc2+"%"};
if (length_end_desc2 != 0) {end_desc2="%"+end_desc2};
if (length_start_desc2==0 && length_contains_desc2==0 &&
length_end_desc2==0) {contains_desc2="%"};
send_string=send_string+"&start_description_2="+st art_desc2+"&contains_description_2="+contains_desc 2+"&end_description_2="+end_desc2;
document.parts.action="http://192.168.254.238/cgi-bin/ice/macro_partsearch.html?"send_string;
document.parts.submit();
} //submit function
-->
</SCRIPT>
<FORM NAME="parts" METHOD="GET" onSubmit="javascript:checkme(this);">
<P>
Enter a string that the item number begins with
<INPUT TYPE=text NAME="start_item_no" VALUE=""><br>
Enter a string that the item number contains
<INPUT TYPE=text NAME="contains_item_no" VALUE=""><br>
Enter a string that the item number ends with
<INPUT TYPE=text NAME="end_item_no" VALUE="">
<br><p>
Enter a string that description 1 begins with
<INPUT TYPE=text NAME="start_description_1" VALUE=""><br>
Enter a string that description 1 contains
<INPUT TYPE=text NAME="contains_description_1" VALUE=""><br>
Enter a string that description 1 ends with
<INPUT TYPE=text NAME="end_description_1" VALUE="">
<br><p>
Enter a string that description 2 begins with
<INPUT TYPE=text NAME="start_description_2" VALUE=""><br>
Enter a string that description 2 contains
<INPUT TYPE=text NAME="contains_description_2" VALUE=""><br>
Enter a string that description 2 ends with
<INPUT TYPE=text NAME="end_description_2" VALUE="">
<br><p>
<INPUT TYPE="submit" VALUE="Find">

<CENTER>
</CENTER>
</FORM>

Jan 17 '06 #1
3 2235

On 17 Jan 2006 ge***********@gmail.com wrote:
Hello experts,

I have been struggling for days to solve this problem and every
suggestion I find via Google does not work for me. There is probably a
solution out there that will do what I want, but I probably have not
properly implemented the solutions I find.

I am trying to make a page to allow personnel the ability to search our
backend DB (which is Ingres 2.0) through a web interface. Ingres has a
cgi program called ICE that can receive SQL queries and return data to
format. I have lots of pages setup that query correctly and work really
well. The one I am struggling with is searching our parts database
because I want to allow wildcard searches. The Ingres wildcard is a %
sign so I can input 011% and it will find any item number that starts
with 011. I can use %011% and it will find any part with 011 in the
string. I only care about three fields item_no, description_1, and
description_2 for searching.

I have set up a simple form that queries properly using only hidden
variables and GET and no JS, but the wildcards have to be explicitly
entered in the form. I want use JS to validate the form and add the
wildcards for the user.

[snip]

Before sending the query, any literal '%' characters in the URL must be
escaped as '%25'. See RFC 2396, Sections 2.4 to 2.4.3:
http://www.faqs.org/rfcs/rfc2396.html
ftp://ftp.isi.edu/in-notes/rfc2396.txt

--
Norman De Forest http://www.chebucto.ns.ca/~af380/Profile.html
af***@chebucto.ns.ca [=||=] (At the Sign of the Flashing Cursor)
"Oh how I miss the days when it was easier to catch gonorhea than a
computer virus." -- Big Will in alt.comp.virus, March 9, 2005

Jan 17 '06 #2
If you mean instead of having the JS append % on the end of the search
variable I need the script to append %25 on the end, I had already
tried that and it still fails to get the data. In the case of using
GET the %25 is not present in the apache log so it looks like the URL
is still malformed without the %. When using POST, the %25 is there in
the URL but % does not show up in the SQL query log.

Jan 17 '06 #3
VK

ge***********@gmail.com wrote:
document.parts.action="http://...?"send_string


Try:
document.parts.action="http://...?"+escape(send_string);

Jan 17 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Bryan Russell | last post by:
Hi, I'm trying to pass the wildcard variable % to another page via request.querystring. When I go to pick up the variable through request.querystring, it will never diplay the "%" if one is...
12
by: Niall Porter | last post by:
Right this has to be a Micro$oft mess-up surely...? I'm running SQL 2k standard with SP3. I have a table which I'm trying to query using a LIKE operator on a varchar field as follows .......
2
by: Dave Smithz | last post by:
Hello there, Summary: How far can you go with SQL Select queries using like clauses with wildcard characters. Can you apply anything like regular expressions? Full details: On a Intranet...
1
by: deko | last post by:
I have a form where users can enter a string with asterisks to perform a wildcard search. Currently, the string entered by the user looks like this: *somestring* The purpose is to match any...
1
by: JemPower | last post by:
Hi all, Can someone tell me if it's possible to search through a dataview based on a part-filter. For example, if I use... Dim dvAvailable = New DataView(dsRecipients.Tables("Available"),...
7
by: eddie.holder | last post by:
Hi ladies and gents. I'm hoping anyone will be able to help me in a small access problem I am having. Let me try to explain: I have a form with textboxes which I use as criteria for a query. The...
2
by: googlegroups.dsbl | last post by:
I'm really confused here, and am wondering if someone knows what could be the issue with my TableAdapter query. A few months ago, I created a really neat program that has th ability to search by...
9
by: romanko | last post by:
I'm running Access 2000 on Windows XP. I have a simple select query, with two tables joined at the "Account" field. The account field has 5 characters of text in both tables Table A is a...
0
by: savage678 | last post by:
Hi Everyone, I am new to this forum and am i dire need of some help. I am trying to use wildcard searches in infopath. I have it connected to an access database using data connection. I have...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.