Important Question, about ExecuteQuery.

Important Question, about ExecuteQuery.

Postby xpomp » Mon 15 Nov 2010 16:52

BDDataContext.BDDataContext bd = new BDDataContext.BDDataContext();

others comands dotconnet for oracle linq if I work for DataContext.

This not:
var Log = bd.ExecuteQuery(@"SELECT * FROM logs IdLog = {0}", "436");

ExecuteQuery now works for the DataContext??

Some example? because I can not make it work.

If it does not work there any alternative with ADO. I have oracle provider 11g.


Greetings
xpomp
 
Posts: 4
Joined: Mon 15 Nov 2010 16:32

Postby StanislavK » Mon 15 Nov 2010 18:07

I will send you a test project in a letter, please check that it was not blocked by your mail filter. The table used in the sample is defined as
Code: Select all
CREATE TABLE DEPT (
  DEPTNO NUMBER(4) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14) ,
  LOC VARCHAR2(13)
);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

Please specify the error message you are receiving and describe what should be changed in the sample to reproduce the problem.

If possible, please send us your test project.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby xpomp » Tue 16 Nov 2010 10:48

Hi StanislavK

My simple sentences that do not work.

ORACLE code:
Code: Select all
CREATE TABLE BANCOS (
  IdBanco NUMBER(11) CONSTRAINT PK_BANCOS PRIMARY KEY,
  Numero NUMBER(4) ,
  Nombre NVARCHAR2(255)
);

INSERT INTO BANCOS VALUES (1,'1111','Santander');
INSERT INTO BANCOS VALUES (2,'2222','BBVA'); 


Code c#:

In BD.Designer.cs: IdBanco=int32, numero= int32 and nombre=string

Code: Select all
BDDataContext.BDDataContext db = new BDDataContext.BDDataContext();

            try
            {
                var ban = db.ExecuteQuery("select * from bancos where Numero = {0}", "2222");
                string cad = "";

                foreach (var row in ban)
                {
                    cad += "-" + row.IdBanco.ToString();
                    cad += ";" + row.Nombre;
                    cad += ";" + row.Numero.ToString();

                }
                return cad;
            }
            catch (Exception e)
            {
                return e.ToString();
            }


ERROR:
Devart.Data.Linq.LinqCommandExecutionException: Error on executing DbCommand. ---> Devart.Data.Oracle.OracleException: ORA-00904: "NUMERO": identificador no válido
en Devart.Data.Oracle.am.b(Int32 A_0)
en Devart.Data.Oracle.ao.e(Int32 A_0)
en Devart.Data.Oracle.ao.a(Int32 A_0, f A_1)
en Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
en Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
en Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
en System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
en Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(CompiledQuery compiledQuery, Object[] parentArgs, Object[] userArgs, Object lastResult)



If NUMBER with single quotes:
Code: Select all
var ban = db.ExecuteQuery("select * from bancos where 'Numero' = {0}", "2222");


Not error: but response empty.

If filter for NOMBRE:
Code: Select all
var ban = db.ExecuteQuery("select * from bancos where NOMBRE = {0}", "BBVA");


Devart.Data.Linq.LinqCommandExecutionException: Error on executing DbCommand. ---> Devart.Data.Oracle.OracleException: ORA-00904: "NOMBRE": identificador no válido
en Devart.Data.Oracle.am.b(Int32 A_0)
en Devart.Data.Oracle.ao.e(Int32 A_0)
en Devart.Data.Oracle.ao.a(Int32 A_0, f A_1)
en Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
en Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
en Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
en System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
en Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(CompiledQuery compiledQuery, Object[] parentArgs, Object[] userArgs, Object lastResult)


if:
Code: Select all
var ban = db.ExecuteQuery("select * from bancos");


Response : -1;Santander;1111-2;BBVA;2222 *OK*


What I need is to create queries that return me the union fields from multiple tables using string concatenation, hence the "ExecuteQuery" but these are defined at runtime by the user, I can not use views because defienen time compilation.

Question: Since the command ExecuteQuery