Devart Forums

Posted: **Mon 29 Mar 2010 10:12**

is tehe any chance to exec query like this:

WITH RECURSIVE REC(ID_PRACOWNIKA, NAZWA, ID_SZEFA) AS
(
SELECT "PRACOWNICY".id_pracownika,"PRACOWNICY".nazwa,"PRACOWNICY".id_szefa
FROM "PRACOWNICY" WHERE "PRACOWNICY".id_pracownika = 1
UNION ALL
SELECT P.ID_PRACOWNIKA,P.NAZWA,P.ID_SZEFA
FROM "PRACOWNICY" AS P
INNER JOIN REC AS R ON R.ID_PRACOWNIKA = P.ID_SZEFA
)

SELECT R.NAZWA AS PRACOWNIK, P.NAZWA AS SZEF
FROM REC AS R
INNER JOIN "PRACOWNICY" AS P ON R.ID_SZEFA = P.ID_PRACOWNIKA

Now I get exception. I want diplay result in grid control.

sory for me poor english

Posted: **Mon 29 Mar 2010 12:21**

Provided that the PostgreSQL server which you are using supports WITH queries, it should be possible to execute them via the PgSqlCommand class. Please note that WITH queries are supported since PostgreSQL version 8.4 only.

Posted: **Mon 29 Mar 2010 20:44**

I try something like this but I have exception:

pgSqlConnection1.Open();
pgSqlCommand1.Connection = pgSqlConnection1;
pgSqlCommand1.CommandText =
"WITH RECURSIVE REC(ID_PRACOWNIKA, NAZWA, ID_SZEFA) AS " +
"( " +
" SELECT \"PRACOWNICY\".id_pracownika,\"PRACOWNICY\".nazwa,\"PRACOWNICY\".id_szefa" +
" FROM \"PRACOWNICY\" WHERE \"PRACOWNICY\".id_pracownika = 1 " +
" UNION ALL " +
" SELECT P.ID_PRACOWNIKA,P.NAZWA,P.ID_SZEFA " +
" FROM \"PRACOWNICY\" AS P " +
" INNER JOIN REC AS R ON R.ID_PRACOWNIKA = P.ID_SZEFA " +
") " +
" " +
"SELECT R.NAZWA AS PRACOWNIK, P.NAZWA AS SZEF " +
"FROM REC AS R " +
"INNER JOIN \"PRACOWNICY\" AS P ON R.ID_SZEFA = P.ID_PRACOWNIKA ";

DataSet ds = new DataSet();
pgSqlDataAdapter1.Fill(ds);

radGridView1.DataSource = ds.Tables[0];

can you tell me whot are the limitations in free pg provider?

Posted: **Tue 30 Mar 2010 07:48**

Please specify the exact error message that you are receiving. Try executing this query with some standard tool, like pgAdmin.

Also, you may specify or send us the script creating the database objects, so that we are able to reproduce and investigate the issue.

Posted: **Tue 30 Mar 2010 10:01**

in pgadmin all works fine! this is only test so I will post datatabse here:

database:

CREATE TABLE "PRACOWNICY"
(
id_pracownika integer NOT NULL DEFAULT nextval('pracownicy_id_pracownika_seq'::regclass),
nazwa character varying(15),
id_szefa integer,
CONSTRAINT pracownicy_pkey PRIMARY KEY (id_pracownika)
)
WITH (
OIDS=FALSE
);
ALTER TABLE "PRACOWNICY" OWNER TO postgres;

data:

INSERT INTO "PRACOWNICY" (id_pracownika, nazwa, id_szefa) VALUES (3, 'TOMEK', 2);
INSERT INTO "PRACOWNICY" (id_pracownika, nazwa, id_szefa) VALUES (1, 'MATEUSZ', NULL);
INSERT INTO "PRACOWNICY" (id_pracownika, nazwa, id_szefa) VALUES (2, 'WOJTEK', 1);
INSERT INTO "PRACOWNICY" (id_pracownika, nazwa, id_szefa) VALUES (4, 'DAMIAN', 2);
INSERT INTO "PRACOWNICY" (id_pracownika, nazwa, id_szefa) VALUES (5, 'KAMIL', 4);
INSERT INTO "PRACOWNICY" (id_pracownika, nazwa, id_szefa) VALUES (6, 'ŁUKASZ', 1);
INSERT INTO "PRACOWNICY" (id_pracownika, nazwa, id_szefa) VALUES (7, 'PAWEŁ', 5);

query:

WITH RECURSIVE REC(ID_PRACOWNIKA, NAZWA, ID_SZEFA) AS
(
SELECT "PRACOWNICY".id_pracownika,"PRACOWNICY".nazwa,"PRACOWNICY".id_szefa
FROM "PRACOWNICY" WHERE "PRACOWNICY".id_pracownika = 1
UNION ALL
SELECT P.ID_PRACOWNIKA,P.NAZWA,P.ID_SZEFA
FROM "PRACOWNICY" AS P
INNER JOIN REC AS R ON R.ID_PRACOWNIKA = P.ID_SZEFA
)

SELECT R.NAZWA AS PRACOWNIK, P.NAZWA AS SZEF
FROM REC AS R
INNER JOIN "PRACOWNICY" AS P ON R.ID_SZEFA = P.ID_PRACOWNIKA

result:

+-----------+---------+
| pracownik | szef |
+-----------+---------+
| WOJTEK | MATEUSZ |
| ŁUKASZ | MATEUSZ |
| TOMEK | WOJTEK |
| DAMIAN | WOJTEK |
| KAMIL | DAMIAN |
| PAWEŁ | KAMIL |
+-----------+---------+
6 rows in set

In visual 2008 try this:

private void radButton1_Click(object sender, EventArgs e)
{
pgSqlConnection1.Open();
pgSqlCommand1.Connection = pgSqlConnection1;
pgSqlCommand1.CommandText =
"WITH RECURSIVE REC(ID_PRACOWNIKA, NAZWA, ID_SZEFA) AS " +
"( " +
" SELECT \"PRACOWNICY\".id_pracownika,\"PRACOWNICY\".nazwa,\"PRACOWNICY\".id_szefa" +
" FROM \"PRACOWNICY\" WHERE \"PRACOWNICY\".id_pracownika = 1 " +
" UNION ALL " +
" SELECT P.ID_PRACOWNIKA,P.NAZWA,P.ID_SZEFA " +
" FROM \"PRACOWNICY\" AS P " +
" INNER JOIN REC AS R ON R.ID_PRACOWNIKA = P.ID_SZEFA " +
") " +
" " +
"SELECT R.NAZWA AS PRACOWNIK, P.NAZWA AS SZEF " +
"FROM REC AS R " +
"INNER JOIN \"PRACOWNICY\" AS P ON R.ID_SZEFA = P.ID_PRACOWNIKA ";

DataSet ds = new DataSet();
pgSqlDataAdapter1.SelectCommand = pgSqlCommand1;
pgSqlDataAdapter1.Fill(ds);

radGridView1.DataSource = ds.Tables[0];
}

I get this error message:

An unhandled exception of type 'Devart.Data.PostgreSql.PgSqlException' occurred in Devart.Data.dll

Additional information: External component has thrown an exception.

sory for me poor english

EDIT

when I do this with ODBC all works fine:

private void radButton1_Click(object sender, EventArgs e)
{
OdbcConnection conn = new OdbcConnection("DRIVER={PostgreSQL Unicode};SERVER=localhost;PORT=5432;DATABASE=test;UID=postgres;PWD=banita;");
conn.Open();

OdbcCommand cmd = new OdbcCommand();
cmd.Connection = conn;
cmd.CommandText =
"WITH RECURSIVE REC(ID_PRACOWNIKA, NAZWA, ID_SZEFA) AS " +
"( " +
" SELECT \"PRACOWNICY\".id_pracownika,\"PRACOWNICY\".nazwa,\"PRACOWNICY\".id_szefa" +
" FROM \"PRACOWNICY\" WHERE \"PRACOWNICY\".id_pracownika = 1 " +
" UNION ALL " +
" SELECT P.ID_PRACOWNIKA,P.NAZWA,P.ID_SZEFA " +
" FROM \"PRACOWNICY\" AS P " +
" INNER JOIN REC AS R ON R.ID_PRACOWNIKA = P.ID_SZEFA " +
") " +
" " +
"SELECT R.NAZWA AS PRACOWNIK, P.NAZWA AS SZEF " +
"FROM REC AS R " +
"INNER JOIN \"PRACOWNICY\" AS P ON R.ID_SZEFA = P.ID_PRACOWNIKA ";
OdbcDataAdapter d = new OdbcDataAdapter();
d.SelectCommand = cmd;
DataSet ds = new DataSet();
d.Fill(ds);
radGridView1.DataSource = ds.Tables[0];
}

Posted: **Tue 30 Mar 2010 12:36**

Thank you for your report, we have reproduced the problem in the case if pgSqlDataAdapter1.MissingSchemaAction is AddWithKey. We will investigate the situation and notify you about the results.

As a workaround, you may set pgSqlDataAdapter1.MissingSchemaAction to Add before filling the data set.

Posted: **Tue 30 Mar 2010 21:07**

.
.
.
pgSqlDataAdapter1.MissingSchemaAction = System.Data.MissingSchemaAction.Add;
pgSqlDataAdapter1.Fill(ds);

radGridView1.DataSource = ds.Tables[0];

still the same error

Posted: **Wed 31 Mar 2010 07:27**

Please specify the versions of dotConnect for PostgreSQL and PostgreSQL server you are using. With MissingSchemaAction set to Add, in our environment your query was executed properly when I used the 4.90.102 Beta version of dotConnect for PostgreSQL.

I send you a sample project in a letter, please check that it was not blocked by your mail filter. Please specify what should be changed in the sample to reproduce the error. If the problem occurs, please specify the exact error message shown in the message box.

Posted: **Wed 31 Mar 2010 09:21**

thanks! your example works fine.
When I write my own example all works fine too.

but when I build example by use component from toolbox I get error.

Posted: **Wed 31 Mar 2010 15:27**

Please specify the following:
- the exact error message you are receiving;
- which components you have created from the toolbox;
- which modifications (if any) you have made to them.

If possible, please send us a sample project with which the problem can be reproduced.

I dropped the PgSqlConnection, PgSqlCommand and PgSqlDataAdapter objects from the toolbox and ran the sample successfully with them.

Posted: **Sat 03 Apr 2010 10:35**

I try express version which dont have VS integration feature. I add componenet into toolbox manually. maybe this is a reason?

sory for me english

Posted: **Tue 06 Apr 2010 09:12**

I tried to reproduce the problem with the Express edition and failed. The query was executed properly.

Please add a try-catch block near the fill() invocation, like in the following sample:

Code: Select all

try
{
	DataSet ds = new DataSet();
	pgSqlDataAdapter1.SelectCommand = pgSqlCommand1;
	pgSqlDataAdapter1.Fill(ds);
}
catch(PgSqlException ex)
{
	MessageBox.Show(ex.Message);
}

and specify the exact error message which appears in the message box. Also, please send us the exception call stack.

We fixed the problem with MissingSchemaAction = AddWithKey, look forward to the nearest build.

Posted: **Fri 21 May 2010 16:46**

We have released the new 4.95.140 build of dotConnect for PostgreSQL. It can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

The new build contains fix for NRE on executing recursive queries with MissingSchemaAction = AddWithKey. For more information on fixes and improvements available in version 4.95.140, please see
http://www.devart.com/forums/viewtopic.php?t=18032

Devart Forums

exec query in express version

exec query in express version