Changing schema & disposing prepared statements

[janagn]

Hello all,

the dotConnect library supports a very useful option to assign a schema on the connection object. From what I have seen so far, you can safely switch schema to a connection even when the connection is open. My question is about prepared commands. Let's assume that we have prepared a command. To do so we need to provide a connection to the Command object. What happens though to this command if we change the schema later?:

Code: Select all

Dim aConn as new PgSqlConnection(pgConnectionString)
aConn.Schema = "mySchema"
Dim aCommand as new PgSqlCommand("select * from aTable", aConn)
aCommand.prepare()
aConn.Schema = "anotherSchema"

Will the new command be executed on "mySchema" or on "anotherSchema"?

Do we have now two (2) prepared statements, one for "mySchmea" and one for "anotherSchema" or the "mySchema" has been disposed and there is only one?

And the final question related to disposing a command. Is there anyway we can explicitly dispose a command without closing a Connection? If for example we have a lot of prepared statements accumulated on the server, is there any way providing we have a reference to them, to dispose them?

Kind Regards
Yiannis

[Pinturiccio]

Let's assume that we have prepared a command. To do so we need to provide a connection to the Command object. What happens though to this command if we change the schema later? Will the new command be executed on "mySchema" or on "anotherSchema"?

This command will be executed with the schema, which was assigned before execute prepare function ("MySchema").

Is there anyway we can explicitly dispose a command without closing a Connection?

You can use PgSqlCommand.Dispose();

Do we have now two (2) prepared statements, one for "mySchema" and one for "anotherSchema" or the "mySchema" has been disposed and there is only one?

You have one command with aConn.Schema="mySchema" and executed prepare() function. Than you change aConn.Schema="anotherSchema" and execute the prepare() function. In this case PgSqlCommand will refer to the prepared command with anotherSchema on the server and the prepared command with mySchmea will be disposed.