How to use 'Insert' in a nativeSQL query in Hibernate outside the mapped class ?

In Hibernate, you can use the 'SELECT' queries in native SQL like this :

Query query = session.createSQLQuery("SELECT ... FROM ...");

But I would want to use an 'INSERT' query.

So, I looked at the documentation, and it seems you must go directly to the mapped class and write the code inside it.

But I would want to use it as I do for a 'SELECT' query (outside the mapped class) since it looks much more pratical.

Indeed, why would the treatment be different between 'SELECT' and 'INSERT' for a hibernate native SQL query ?

via Chebli Mohamed

nhibernate with a restriction outside of sql

Is it possible to add restriction in nHibernate (version 3.3) that is based on a calculation outside of the database? For example, say someCalculation below calls into some other method in my code and returns a boolean. For the sake of argument, someCalculation() can not be made in the database. Is there a way to get it to work? It's currently throwing and I'm not sure if it's because I am way off or I'm doing something else wrong.

 query.UnderlyingCriteria.Add(Restrictions.Where<MyEntity>(x => someCalculation(x.id)); 

via Chebli Mohamed

granted table with select but procedure doesn't work

I have one table granted with SELECT - so I can access the table with select..

I can also create copy of this table with:

CREATE TABLE my_table AS
SELECT *
FROM read_only_tbl;

And also manualy reloadtable ->

DELETE FROM my_table;
INSERT INTO my_table
SELECT *
FROM read_only_tbl;

But when I want to run the "reload" from procedure it gives me an error while compiling that the procedure can't see the "read_only_table"...

CREATE OR REPLACE PROCEDURE prcd_reload AS
BEGIN

    DELETE FROM my_table;
    INSERT INTO my_table
    SELECT *
    FROM read_only_tbl;

   /*** .. rest of code ***/

END;
/

-> PL/SQL: ORA-00942: table or view does not exist

what grant do I need to access that table in procedure?

via Chebli Mohamed

OracleCommand.Executenonquery throws 'System.InvalidOperationException'

The following function throws the System.InvalidOperationException:

internal void executeNonQuery(string connectionString, OracleCommand cmd)
    {
        using (OracleConnection conn = new OracleConnection(connectionString))
        {
            using (cmd)
            {
                conn.Open();
                cmd.ExecuteNonQuery(); //here is the error
                conn.Close();
            }
        }
    }

The additional information is:

Operation is not valid due to the current state of the object.

I try to insert a row into a table. Is there another way to do this or to fix this error?

EDIT: I build the query in the binaryManager class with the following methods:

internal object[] binaryInsert(string tblName, string tblQuery, int conStrgID, int cq)
    {
        object[] retValues = new object[3];
        Stream myStream = null ;
        OracleConnection con = null;
        string conString = qm.getConnectionString("ConnectionStringToMyDB"); //is correct

        byte[] data = GetBytes(tblQuery);

        String sql = "INSERT INTO MYTABLES VALUES (NULL, '" + tblName + "', ':tblQueryBlob', " + conStrgID + ", " + cq + ")";


        OracleCommand cmd = new OracleCommand();
        cmd.CommandText = sql;  // Set the sql-command
        cmd.Connection = con;   //con is an OracleConnection

        OracleParameter param = cmd.Parameters.Add("tblQueryBlob", OracleDbType.Blob); //Add the parameter for the blobcolumn
        param.Direction = ParameterDirection.Input;

        param.Value = data;     //Asign the Byte Array to the parameter

        //command containts the parameter :tblQueryBlob with its value
        retValues[0] = cmd;
        retValues[1] = conString;

        return retValues;
    }

private byte[] GetBytes(string str)
    {
        byte[] bytes = new byte[str.Length * sizeof(char)];
        System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
        return bytes;
    }

I call the binaryInsert method from another class with the following code:

BinaryManager bm = new bBinaryManager();
string sqlQuery = "large string with 5100 characters";
object[] binaryValues = bm.binaryInsert("TextTbl", sqlQuery, 1, 1);
string conString = binaryValues[1].ToString();
OracleCommand cmd = (OracleCommand)binaryValues[0];

QueryManager qm = new QueryManager();
qm.executeNonQuery(conString, cmd);

via Chebli Mohamed

What's the basis to choose SQL over HQL in hibernate for some queries?

I am using hibernate in my project and I have always used HQL. However, I have seen in other projects where at times SQL queries are used by createSqlQueries, rather than HQL. I wanted to know, what could be the deciding factor to choose SQL over HQL in certain scenarios. Also if there are some queries which can't be performed by HQL and we need to choose only SQL, please cite example.

via Chebli Mohamed

Changing the dataset on a server executed SSRS Report

I'm trying to narrow down the results returned from a server generated SSRS report, but the customer is requesting too many fields to do be able to do it easily with parameters into a predefined SQL statement.

Is it possible to pass a statement into the reporting server from .NET that the server will execute as its datasource, instead of the preconfigured one? Either the complete statement or the WHERE clause would be fine.

If not, is it possible to eval a parameter sent into a stored procedure? I'm aware of the security implications.

via Chebli Mohamed

get latest row from foreign key table in left join in sql

i have a table with multiple columns ,and there are 5 more tables ,which have reference foreign key relation .in one table we have more than 5 columns for one ref. but I want only latest one ..can you please tell me how can I take it by single query without using temp ...

via Chebli Mohamed