Error in PgSqlDataTable.Load with multiple results returned

Error in PgSqlDataTable.Load with multiple results returned

Postby dsaracini » Thu 12 Mar 2009 21:17

Hello,

I am attempting to return multiple "results" from a single round trip and load them into separate tables. I have a simple way of reproducing the problem:

create three tables and fill with data:

CREATE TABLE foo
(
id serial NOT NULL,
foo_name character varying(30)
)
WITH (OIDS=FALSE);
ALTER TABLE foo OWNER TO postgres;

CREATE TABLE moo
(
id serial NOT NULL,
moo_name character varying(30)
)
WITH (OIDS=FALSE);
ALTER TABLE moo OWNER TO postgres;

CREATE TABLE noo
(
id serial NOT NULL,
noo_name character varying(30)
)
WITH (OIDS=FALSE);
ALTER TABLE noo OWNER TO postgres;

insert into foo (foo_name) values ('foo1');
insert into foo (foo_name) values ('foo2');
insert into foo (foo_name) values ('foo3');

insert into moo (moo_name) values ('moo1');
insert into moo (moo_name) values ('moo2');
insert into moo (moo_name) values ('moo3');

insert into noo (noo_name) values ('noo1');
insert into noo (noo_name) values ('noo2');
insert into noo (noo_name) values ('noo3');

then create a winform application... add a connection object to the main form and set the connection string (note: the protocol must be VER20)

pgSqlConnection1.Open(); //protocol = ver20
try
{
StringBuilder sqlstmt = new StringBuilder();
sqlstmt.AppendLine("select * from foo where id = :id;");
sqlstmt.AppendLine("select * from moo where id = :id;");
sqlstmt.AppendLine("select * from noo where id = :id;");

PgSqlCommand cmd = new PgSqlCommand(sqlstmt.ToString(), pgSqlConnection1);

cmd.Parameters.Add("id", 2);

PgSqlDataReader reader = cmd.ExecuteReader();
PgSqlDataTable foo_table = new PgSqlDataTable();

foo_table.Load(reader); //<---ERROR HERE!!!!!!!!!!!!!

reader.NextResult();
PgSqlDataTable moo_table = new PgSqlDataTable();
moo_table.Load(reader);

reader.NextResult();

PgSqlDataTable noo_table = new PgSqlDataTable();
noo_table.Load(reader);

gridControl1.DataSource = foo_table;

reader.Close();


}
finally
{
pgSqlConnection1.Close();
}


obviously, what I'm trying to do is to avoid the expense of 3 round trips to the database.

PLEASE NOTE! If I do the about and instead of trying to load a datatable, I just put the information into a list<> object, I can get the information out... so, it's not a problem with the reader not being able to "see" the information.

I am using:

VS 2008 std
Postgres 8.3
OS: Vista
.Net 3.5 sp1

Here is the stack of the exception I receive:

at Devart.Common.SelectStatement.f()
at Devart.Common.SelectStatement.a(ParserBehavior A_0)
at Devart.Data.PostgreSql.PgSqlSelectStatement.Parse(String text, ParserBehavior behavior)
at Devart.Data.PostgreSql.PgSqlDataReader.GetSchemaTable()
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMappingInternal(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at PostgresTextFieldTest1.Form1.simpleButton5_Click(Object sender, EventArgs e) in C:\Users\dsaracini\Documents\Visual Studio 2008\Projects\Testing\PostgresTextFieldTest1\PostgresTextFieldTest1\Form1.cs:line 220
at System.Windows.Forms.Control.OnClick(EventArgs e)
at DevExpress.XtraEditors.BaseButton.OnClick(EventArgs e)
at DevExpress.XtraEditors.BaseButton.OnMouseUp(MouseEventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevExpress.Utils.Controls.ControlBase.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at PostgresTextFieldTest1.Program.Main() in C:\Users\dsaracini\Documents\Visual Studio 2008\Projects\Testing\PostgresTextFieldTest1\PostgresTextFieldTest1\Program.cs:line 18
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()


Please let me know if I'm doing something stupid here... or if there is another way to accomplish this task. Ultimately, what I want to do is to return data from multiple queries with only "one round trip".

Thanks in advance.
dsaracini
 
Posts: 17
Joined: Wed 04 Mar 2009 07:50

Postby dsaracini » Thu 12 Mar 2009 21:42

Follow up:

I get the same error if I create a PgSqlDataSet... add 3 tables to it and try to do a:

ds.Load(reader, LoadOption.OverwriteChanges, new string[] {"myfoo", "mymoo", "mynoo"} );
dsaracini
 
Posts: 17
Joined: Wed 04 Mar 2009 07:50

Postby AndreyR » Fri 13 Mar 2009 10:17

Thank you for the report, we have reproduced the problem. I will let you know about the results of our investigation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby dsaracini » Thu 19 Mar 2009 17:41

May I have a status update? When will this issue be addressed?

Thank you.
dsaracini
 
Posts: 17
Joined: Wed 04 Mar 2009 07:50

Postby AndreyR » Thu 26 Mar 2009 12:16

The problem is fixed. Look forward to the next build of dotConnect for PostgreSQL.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for PostgreSQL