Changing schema & disposing prepared statements

Changing schema & disposing prepared statements

Postby janagn » Tue 06 Dec 2011 13:30

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
janagn
 
Posts: 6
Joined: Thu 24 Nov 2011 11:34

Postby Pinturiccio » Wed 07 Dec 2011 14:42

overtime wrote: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").
overtime wrote:Is there anyway we can explicitly dispose a command without closing a Connection?

You can use PgSqlCommand.Dispose();
overtime wrote: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.
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL