Devart Forums

Posted: **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

Posted: **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.

Posted: **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 .
Should there be a template in disigner.cs be the union of these tables?
In short you can do joins with the ExecuteQuery and return it var.

Greetings.

Posted: **Tue 16 Nov 2010 15:38**

A possible cause of the problem is that you are connecting to a schema where another table named BANCOS exists, but has a different set of columns. Please try setting the full name of the table, like "select * from SchemaName.bancos where Numero = 2222".

Also, please try executing the command with general ADO.NET components and tell us if the problem can be reproduced in this case. For example, try the following code:

Code: Select all

OracleCommand cmd = new OracleCommand("select * from bancos where Numero = 2222", (OracleConnection)db.Connection);
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read()) cad = reader.GetValue(2).ToString();

Also, please specify the version of dotConnect for Oracle you are using (you can check it in the Tools -> Oracle -> 'About dotConnect for Oracle' item of the Visual Studio menu).

We couldn't reproduce the issue in our environment.

Posted: **Wed 17 Nov 2010 09:06**

Code: Select all

Tools->LinqConnect
Standard Edition v:1.0.41.0
Hi StanislavK!!

Tools->Entity Developer
Standard Edition v:2.80.160

Tools->oracle not found.

References dll:
Devart.Data-> v:5.0.105.0
Devart.Data.Linq-> v:1.0.39.0
Devart.Data.Oracle-> v:5.70.152.0
Devart.Data.Oracle.Linq->v:1.0.39.0

References ADO:
Oracle.DataAccess->4.112.1.2

[/i]

First thanks for your help.

The problem was by the double quotes in the StringQuery and did not find any examples of how to do it, anyway do not understand how you it works without the quotes, will be the version?

Examples of code that works:

Code: Select all

 OracleConnection conn = new OracleConnection(connectionString);
            OracleCommand comand = conn.CreateCommand();
            //OK
            comand.CommandText = "SELECT * FROM bancos";
            comand.CommandText = "SELECT * FROM bancos WHERE \"Numero\" = 11";
            comand.CommandText = "SELECT * FROM bancos WHERE bancos.\"Nombre\" like 'B%'";
           

            conn.Open();

            string info = "";
            OracleDataReader reader = comand.ExecuteReader();
            while (reader.Read())
            {
                info += reader[0];
            }

            reader.Close();
            conn.Close();

            return info;

In code Linq:

Code: Select all

var ban = db.ExecuteQuery("SELECT * FROM bancos WHERE \"Numero\" = {0}", "11");

Greetings.

Posted: **Thu 18 Nov 2010 16:52**

Apparently, column names were quoted in the script used to create the Bancos table. If a column name is not quoted, Oracle converts it to upper case. Thus, when you execute a command like 'SELECT * FROM bancos WHERE Numero = 11', Oracle searches for column NUMERO and fails. For more information about this, please refer to
http://download.oracle.com/docs/cd/B193 ... nts008.htm

Posted: **Thu 18 Nov 2010 17:16**

Thanks now I understand many things.

Devart Forums

Important Question, about ExecuteQuery.

Important Question, about ExecuteQuery.