SQL command with parameters

SQL command with parameters

Postby Robert_ » Wed 30 Jul 2008 14:52

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
Robert_
 
Posts: 2
Joined: Wed 30 Jul 2008 14:34

Postby AndreyR » Thu 31 Jul 2008 07:56

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();
}

AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Robert_ » Thu 31 Jul 2008 08:46

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
Robert_
 
Posts: 2
Joined: Wed 30 Jul 2008 14:34

Postby AndreyR » Thu 31 Jul 2008 10:04

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");
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for PostgreSQL