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); // 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.