SQL command with parameters

[Robert_]

Hello,

I have tried to insert a new table into a database using a sql command with parameters. The variables "name" and "owner" are parameters of my function and declared as following outside this function (I am using c++ managed):

Code: Select all

String^ name = "db_30082008__1609";
String^ owner = "postgres";

First step in my function is to create a new command:

Code: Select all

PgSqlCommand^ sqlCommand = m_Connection->CreateCommand();

Second step is to prepare the command text:

Code: Select all

sqlCommand->CommandText = "CREATE DATABASE :database WITH OWNER = :owner ENCODING = 'UTF8';";
		sqlCommand->Parameters->AddWithValue("database", name);		
		sqlCommand->Parameters->AddWithValue("owner", owner);

Third step is the execution:

Code: Select all

sqlCommand->ExecuteNonQuery();

But I always get an exception:

CoreLab.PostgreSql.PgSqlException: syntax error at or near "$1"
bei CoreLab.PostgreSql.a.d(Boolean A_0)
bei CoreLab.PostgreSql.a.ae()
bei CoreLab.PostgreSql.a.b(String A_0)
bei CoreLab.PostgreSql.a.l()
bei CoreLab.PostgreSql.PgSqlCommand.a(Boolean A_0, Int32 A_1, Int32 A_2)
bei CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
bei System.Data.Common.DbCommand.ExecuteReader()
bei CoreLab.Common.DbCommandBase.ExecuteNonQuery()

If I try to set the values as fixed string, it works:

Code: Select all

sqlCommand->CommandText = "CREATE DATABASE " + name + " WITH OWNER = " + owner + " ENCODING = 'UTF8';";

Why does the command using parameters not work?

with kind regards,
Robert

[AndreyR]

Sorry, but you cannot use varchar parameters in the names of database objects. As an alternative, you can use the following code:

Code: Select all

String^ name = "db_30082008__1609"; 
String^ owner = "postgres";
PgSqlCommand^ cmd = pgSqlConnection1->CreateCommand();
cmd->CommandText = "CREATE DATABASE " + name + " WITH OWNER " + owner + " ENCODING = 'UTF8'";
try{
	pgSqlConnection1->Open();
	cmd->ExecuteNonQuery();
}
finally{
	pgSqlConnection1->Close();
}

[Robert_]

Hi Andrey,

thanks for your reply. So the problem is simply that I want to create database? If I want to execute a "normal query" the parameter style would work?

with kind regards,
Robert

[AndreyR]

Exactly. For example, you can use without such an error code like following.

Code: Select all

cmd->CommandText = "select * from dept where loc = :loc";
cmd->Parameters->Add("loc", "DALLAS");