unable to get column values

[madhuri kunchala]

hi,
i need to get the data which is entered in text field..i had written the query in java..but i m unable to get the values..can you help me out..here is the query.

contentRetirveHQLQueryB.append("select cot.id.contentid  from ContentTags cot,CmsTags ct");
contentRetirveHQLQueryB.append(" where ct.tagid = cot.id.tagId and ct.tagname like '%" + browseRequest.getTags() + "%')");

browseRequest.getTags()--from here i m getting the tag names

any help

[Jan Cumps]

Have you tested if browseRequest.getTags() returns the values you expect?
Does that work?
If yes, give us an example of what it returns.
If no, please inform us, and we will move your post to the JSP forum.

[Jan Cumps]

(by the way, you are using a very dangerous mechanism to run queries. A user can corrupt your database by putting a cunningly forged piece of sql in your text box.
What would happen if a user enters "TAG1%'; delete from customers; delete from vendors; select * from dual where x = 'X"

See sql injection

[madhuri kunchala]

if i give only one value i.e., app1 im getting.. the output correct output i.e., the corresponding values ... and the query is

select
                    contenttag8_.contentid 
                from
                    cms.content_tags contenttag8_,
                    cms.cms_tags cmstags9_ 
                where
                    cmstags9_.tagid=contenttag8_.tag_id 
                    and (
                        cmstags9_.tagname like '%app1%'
                    )

if i give multiple values in text box i.e., app1,app2..im not getting any output.. and the query is showing like this if i give two values.

select
                    contenttag8_.contentid 
                from
                    cms.content_tags contenttag8_,
                    cms.cms_tags cmstags9_ 
                where
                    cmstags9_.tagid=contenttag8_.tag_id 
                    and (
                        cmstags9_.tagname like '%app1,app2%'
                    )

what goes wrong with my query...

[Sean Clark]

Hey,

Your query needs to be more like: select contenttag8_.contentid from cms.content_tags contenttag8_, cms.cms_tags cmstags9_ where cmstags9_.tagid=contenttag8_.tag_id and ( cmstags9_.tagname like '%app1%' OR cmstags9_.tagname like '%app2%' )
Sean

[madhuri kunchala]

i tried that one im mysql query editor im getting...but i need to implement that code in java...can you suggest me how..i tried..but unable to get it.

[Sean Clark]

Hey,
I guess you'll need to split your tags and then loop and append each argument to your query string.
I don't think that it is particularly difficult.

Sean

[madhuri kunchala]

i had implemented that one..if i do i m getting all the data irrespective of the condition.here is the code.

public static String getTags() {
        String contentid = null;
        StringBuffer tags = new StringBuffer();
        List TagsList = nxl.DataAdapter.getResult("select cot.id.contentid as contentid  from ContentTags cot,CmsTags ct"
                + " where ct.tagid = cot.id.tagId and  '" + contentid + "' like concat(ct.tagname,'%') ", CmsTags.class);
        
        int loop = 0;
        if (TagsList != null) {

            for (Object object :TagsList) {
                CmsTags tagsObj = (CmsTags) object;
                if (loop > 0) {
                    tags.append(",");
                }
                tags.append(tagsObj.getTagname());
                loop++;
            }
        }
        return tags.toString();
    }

what the problem with my code

[Sean Clark]

Hey,
I'm not really sure on your logic of what you are trying to do.
In my opinion this method should return a List<String> tags which you can then use as you wish in your other query. Why have you decided to return comma seperated results?

Sean

[madhuri kunchala]

its my project requirement that i need to enter the values separated by commas..but i m not getting..any help regarding this.

[Sean Clark]

Yes, perhaps the input should come in seperated by commas, but I think when it comes to writing the query surely you should be implementing it by splitting the string on the commas and looking for matches.

Perhaps we should step back a touch and you should explain exactly what you are trying to achieve, how does the user interact with the system, what actions do they need to do and what are they expected to get in return?

Sean