current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I got a problem with this code:

string sql = "select distinct ruoli.c_tip_usr"
                                    + " from vneczx_ute_app_trn ruoli"
                                    + " join vnecyd_ent_prf ind"
                                    + " on ruoli.c_ent = ind.c_ent"
                                    + " where ruoli.c_app = :appCode"
                                    + " and ruoli.c_ute_mat = :matricola"
                                    + " and ind.t_des_ent = :indirizzo";                                               
var ruoli = session.CreateSQLQuery(sql)
                   .SetString("appCode", Config.Configurator.Istance.ApplicationCode)
                   .SetString("matricola", user.Matricola)
                   .SetString("indirizzo", indirizzoCasella)                                            
                   .List<string>();

This code is correctly executed, the query logged is correct, and the parameter passed correctly evaluated... but it doesn't return any result at all. Copying the query from the debug console and executing it directly in an Oracle client application (SQL Developer), it gets 2 results (the results I expect to be right).

I found out that the problem is in the last parameter indirizzo, and should depend on the fact that it contains a special char @ (indirizzo is an email address).

So I ended up using this solution:

string sql = "select distinct ruoli.c_tip_usr"
                                    + " from vneczx_ute_app_trn ruoli"
                                    + " join vnecyd_ent_prf ind"
                                    + " on ruoli.c_ent = ind.c_ent"
                                    + " where ruoli.c_app = :appCode"
                                    + " and ruoli.c_ute_mat = :matricola"
                                    + " and ind.t_des_ent = '" + indirizzoCasella + "'";                                               
var ruoli = session.CreateSQLQuery(sql)
                   .SetString("appCode", Config.Configurator.Istance.ApplicationCode)
                   .SetString("matricola", user.Matricola)                                                                 
                   .List<string>();

But it gives me thrills! Aren't the parameters in a query supposed to handle specifically this situation, and thus handle themselves situations with special char, and so on? Why here a string concatenation works better that a parametric query? Isn't there a way to force the NHibernate engine to escape some special char?

Update:

I found out how to solve this particular issue: usign the trim command on the field who raise the problem, the problem disappears. So last line of my sql string now is: 

+ " and trim(ind.t_des_ent) = :indirizzo";

I can't understand why it solves the problem thought. Not the field, nor the variable contains empty chars and copying th query on SQL Developer works in both way. So we have some luck soving the problem, but we have no idea why now it works? Any thoughts?

share|improve this question

2 Answers 2

up vote 0 down vote

even I was facing the same issue, but your hint using TRIM for column saved my day. Since I was looking for a long time, but not able to figure it out.

Along with that, I was able solve my issue by doing the below changes as well:

We were using CHAR datatype some of the columns which used in the query where clause. This was causing the issue to fetch the data from NHibernate. We changed the data type of that column from CHAR to VARCHAR2 and even updated the data with actual size and removed the TRIM from Where clause, the TRICK WORKED!!!! :)

So, any one face this kind of issue, first check whether you are having any column with CHAR and then change to VARCHAR2.

But one more thing you have to remember is: if you are running your application from ASP.Net Development Server, close it and re-run your application. Since if the opening Asp.Net Development Server and if you make any changes to datatype that will NOT be refreshed in your oracle connection. So, better you close the ASP.Net Development server and then re run the application.

Hope my points will help somebody in future!!

Regards,

Sree Harshavardhana

share|improve this answer
up vote -3 down vote

You are not using parameters in a SQL query if you want SQL parameters use a SQL stored proc

share|improve this answer
1  
-1: you aren't answering about the reason for NHibernate SQL query to fail. You're just proposing something like "throw away NHibrnate and write your own store procedure". Does it sound like a good advice to you? –  themarcuz Jun 20 '12 at 10:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.