Vistadb Writing SQL For Image DataType

Question

I have recently started a new job and they use Vistadb so I cannot change the software package before people suggest that. I have obtained out of the database a byte[] from a datatype of image that is used in there different systems so its data type cannot be changed from image to varbinary. I have made alterations to the byte[] and now need to put it back into the database in an new record however I cant seem to work out how the SQL Query should be for it so far I have.

zz is the byte[] the rest of it works fine just need a way to put that into my SQL Query

sql = "INSERT INTO TimeHistory(\"Data\",\"Name\",\"Units\",\"ParameterData\",\"StartTime\",\"EndTime\",\"StorageRate\",\"Measurement\") SELECT \'" +zz+ "\',\'" + Name + "\',\'" + Units + "\',\'" + ParameterData + "\',\'" + start + "\',\'" + end + "\',\'" + storage + "\'" + ",SELECT Max(ID)From Measurement;";

ExecuteScript(sql);

This is done with c#.net using WPF forms.

Answer 1

The key to doing what you want is to use parameters to pass data to your SQL operation, not to convert it to a string and embed it in the TSQL code. This is a best practice not just because it prevents needless type conversions (say from DateTime to string and string back to DateTime for storage) but also for security - it ensures the database engine only attempts to execute as code things you intended to be code, not data that happened to be escaped so it was evaluated as part of the string.

We have a good example of how to do this in our ADO.NET samples at: Common Operations in ADO.NET If you go down the page you'll see an example "Inserting Data Using a Parameterized Command" which will work with any type, like this:

using (VistaDBConnection connection = new VistaDBConnection())
{
    connection.ConnectionString = @"Data Source=C:\mydatabase.vdb5";
    connection.Open();

    using (VistaDBCommand command = new VistaDBCommand())
    {
        int Age = 21;

        command.Connection = connection;
        command.CommandText = "INSERT INTO MyTable (MyColumn) VALUES (@age)";
        command.Parameters.Add("@age", Age);
        command.ExecuteNonQuery();
    }
}