以程序方式插入字段值为SQL语句的行

本文关键字：SQL 语句 程序 方式 插入 字段 | 更新日期: 2023-09-27 18:29:26

我目前正在C#中开发一个解决方案，将数据库表从Oracle复制到PostgreSQL。除了一件事之外，一切都很顺利。当我开始复制我的一个表时，它的一个字段中包含sql语句，由于有两个单引号，它会失败。SQL语句必须保留在表中，因为它被用来使另一个程序数据库不可知。

有没有一种方法可以写下面的SQL，但不需要像行尾附近的"TRUE"值附近那样背靠背地写两个单引号。我还在下面包含了我的代码，以展示如何在C#中构建该语句。该列在Oracle中是varchar2，在PostgreSQL中是TEXT列。

EDIT：显示的sql示例是由我的C#代码生成的实际INSERT语句，该语句将在postgresql数据库上运行，以向表中添加记录。它将用于插入一个文本字段，其中包含一个字符串形式的sql语句。

INSERT INTO SQL_FACTORY_TEST (SQL_FACTORY_TEST_ID,SQL_FACTORY_DIALECT,SQL_FACTORY_QUERY_NAME,SQL_FACTORY_SQL_COMMAND,USER_NAME) 
VALUES (21, 'ORACLE', 'GET_CLUSTERS', 'SELECT CLUSTER_ID, NUM_POINTS, FEATURE_PK, A.CELL_CENTROID.SDO_POINT.X, A.CELL_CENTROID.SDO_POINT.Y, A.CLUSTER_CENTROID.SDO_POINT.X, A.CLUSTER_CENTROID.SDO_POINT.Y, TO_CHAR (A.CLUSTER_EXTENT.GET_WKT ()),  TO_CHAR (A.CELL_GEOM.GET_WKT ()), A.CLUSTER_EXTENT.SDO_SRID FROM (SELECT CLUSTER_ID, NUM_POINTS, FEATURE_PK, SDO_CS.transform (CLUSTER_CENTROID, 4326) cluster_centroid, CLUSTER_EXTENT, SDO_CS.transform (CELL_CENTROID, 4326) cell_centroid, CELL_GEOM FROM :0) a  where sdo_filter( A.CELL_GEOM, SDO_CS.transform(mdsys.sdo_geometry(2003, :1, NULL, mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(:2, :3, :4, :5)),81989)) = 'TRUE'', 'PUBLIC')

代码示例：

oleDataBaseConnection.OleExecutePureSqlQuery("SELECT * FROM " + tableName);
    if (oleDataBaseConnection.HasRows())
    {
        while (oleDataBaseConnection.NextRecord())
        {
            Dictionary<string, string> postgreSQLQueries = TypeConversion.GetQueryDictionary("POSTGRESQL");
            string postgreSQLInsertQuery;
            postgreSQLQueries.TryGetValue("INSERT", out postgreSQLInsertQuery);
            postgreSQLInsertQuery = postgreSQLInsertQuery.Replace("{0}", tableName);
            StringBuilder postgresQuery = new StringBuilder();
            postgresQuery.Append(postgreSQLInsertQuery);
            postgresQuery.Append("(");
            int columnCounter = 0;
            //add a column parameter to query for each of our columns
            foreach (KeyValuePair<string, string> t in columnData)
            {
                postgresQuery.Append(t.Key + ",");
                columnCounter++;
            }
            postgresQuery = postgresQuery.Remove(postgresQuery.Length - 1, 1);
            postgresQuery.Append(") ");
            postgresQuery.Append("VALUES (");
            //Loop through values and 
            for (int i = 0; i < columnCounter; i++)
            {
                string[] foo = new string[columnData.Count];
                columnData.Values.CopyTo(foo, 0);
                if (foo[i].ToUpper() == "TEXT")
                {
                    postgresQuery.Append("'" + oleDataBaseConnection.GetFieldById(i) + "', ");
                }
                else
                {
                    postgresQuery.Append(oleDataBaseConnection.GetFieldById(i) + ", ");
                }
            }
            postgresQuery = postgresQuery.Remove(postgresQuery.Length - 2, 2);
            postgresQuery.Append(") ");
            postgresSQLDBConnection.PostgreSQLExecutePureSqlNonQuery(postgresQuery.ToString());
        }
    }

以程序方式插入字段值为SQL语句的行

最好的解决方案是使用不直接传递文本的PreparedStatement。我不知道C#，所以我不能给你举一个例子。

但是，如果有来保持这样的语句，则可以在PostgreSQL中使用"美元引号"字符串文字。

INSERT INTO SQL_FACTORY_TEST (SQL_FACTORY_TEST_ID,SQL_FACTORY_DIALECT,SQL_FACTORY_QUERY_NAME,SQL_FACTORY_SQL_COMMAND,USER_NAME) 
VALUES (21, 'ORACLE', 'GET_CLUSTERS', $$ ...... 'TRUE'$$, 'PUBLIC')

$$替换文字周围的单引号。如果您的值有可能包含$$，您也可以为其添加一些唯一标识符，例如

$42$String with embedded single quotes ''' and two $$ dollar characters$42$

我认为最好的方法是使用参数化的插入查询。所以你实际上是在构建一个查询，比如：

INSERT INTO [Table] VALUES (@Column1, @Column2, @Column3)

然后将变量传递到查询中。大致如下：

List<OleDbParameter> parameters = new List<OleDbParameter>();
            for (int i = 0; i < columnCounter; i++)
            {
                postgresQuery.Append(string.Format("@Column{0}, ", i));
                parameters.Add(new OleDbParameter(string.Format("@Column{0}, ", i), oleDataBaseConnection.GetFieldById(i));
            }

然后在执行时将paremeters.ToArray()传递给OleDbCommand。这意味着c#将完成所有的转义，并为您计算出数据类型。它可能需要一些调整来满足你的需求，但它的总体要点就在那里。

您应该始终正确地转义和取消转义存储在数据库中的值。

PostgreSQL客户端库为此提供了PQescapeLiteral函数。

相关文章：