自動変換ツールは、多くの場合、OracleからPostgreSQLへの情報システムの移行の大部分を担っています。しかし、そのようなソリューションでカバーされる膨大な量のコードの中には、即興で作成しなければならない例外的なストーリーもあります。このような場合、最初のステップはもちろん、問題を特定し、その原因を特定し、適切な解決策を見つけるために、同様の性質を持つ可能性のある同様の現実または架空の状況があるかどうかを検討することです。その後、通常、元のOracleコードをリファクタリングするか、変換プロセスと文法を変更するか、PostgreSQLに比類のないOracle機能を実装する必要があります。一見原始的な要求にエラーが発生したため、解決するには全体的な調査が必要でした。
スクリーンセーバー
— , . -, :
ERROR: syntax error at or near ":"
LINE X: WHERE strpos((concat(concat(';', (:ID)::varchar), ';'...
^
0
. :ID,
, - , — . instr
, strpos
, , . concat
, , — , ||
, NULL
-. , - . , . — , «». Oracle- :
select '\' || t.SOME_FIELD || '\' SLASHED_FIELD
from SOME_TABLE t
where instr(';' || :ID || ';', ';' || t.ID || ';') > 0
PostgreSQL:
SELECT concat(concat('\', t.some_field), '\') "slashed_field"
FROM some_table t
WHERE strpos((concat(concat(';', (:ID)::varchar), ';')), (concat(concat(';', (t.id)::varchar), ';'))))::numeric > 0
, . IDE psql
, :ID
. - , , , / ? , , .
select |
|
«» | |
, , | |
, / , | , , |
, , |
- :
select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
, , , , , - , . , PostgreSQL.
# ,
ERROR: syntax error at or near ":" at character 20
STATEMENT: select concat('\', :ID::varchar, '\') SLASHED_ID;
# psql \set
LOG: statement: select concat('\', 12345678::varchar, '\') SLASHED_ID;
LOG: duration: 0.936 ms
- . , ?
1
PHP, . Oracle « » oci8
, PostgreSQL — PDO
. - ( Query
) :
- , (
PDO::prepare()
); -
Query->Bind()
, , ; -
Query->Execute()
,PDOStatement::bindParam()
,PDOStatement::execute()
. -
Query->Fetch*()
.
, , , , . PDOStatement::bindParam()
, FALSE
. , TRUE FALSE
. PDO
, . PDO::errorInfo()
:
array(3) {
[0]=>
string(5) "42601"
[1]=>
int(7)
[2]=>
string(136) "ERROR: syntax error at or near ":"
LINE 1: select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
^"
}
PDO::debugDumpParams()
— :
SQL: [56] select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
Params: 0
2
, — PDO
:
<?php
$connectionString = getenv('CONNECTION_STRING');
$connection = new \PDO($connectionString);
if ($connection) {
$stmt = $connection->prepare("select concat('\\', concat(:ID::varchar, '\\')) SLASHED_ID");
if (!$stmt) {
print "Statement preparation has failed\n";
} else {
$value = '12345678';
if ($stmt->bindParam(':ID', $value)) {
print "Bound :ID with value of {$value}\n";
} else {
print "Bind attempt for :ID with value of {$value} has failed\n";
}
if ($stmt->execute()) {
print "Query successfully executed\n";
} else {
$info = $stmt->errorInfo();
print "Query execution has failed, reason: {$info[2]}\nDebug dump: ";
$stmt->debugDumpParams();
print "\n";
}
}
} else {
print "Connection attempt to {$connectionString} has failed, check the CONNECTION_STRING environment variable\n";
}
— , , . -: :
Bind attempt for :ID with value of 12345678 has failed
Query execution has failed, reason: ERROR: syntax error at or near ":"
LINE 1: select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
^
Debug dump: SQL: [56] select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
Params: 0
PDO
: PDO
. ATTR_EMULATE_PREPARES
. . : , - , - . , , -. FALSE
, , , TRUE
— - :
Bound :ID with value of 12345678
Query execution has failed, reason: ERROR: syntax error at or near ":"
LINE 1: select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
^
Debug dump: SQL: [56] select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
Params: 1
Key: Name: [3] :ID
paramno=-1
name=[3] ":ID"
is_param=1
param_type=2
, , , , . PDO
:
PDOStatement::bindParam() | [42601]: syntax error at or near ":" |
PDOStatement::bindValue() | [42601]: syntax error at or near ":" |
PDOStatement::execute($parameters) | [HY093]: Invalid parameter number |
, : 6 ATTR_EMULATE_PREPARES
. , , .
3
libpq
, , . , libpq
, . , /, .
#include <libpq-fe.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
static void graceful_failure(PGconn * conn) {
PQfinish(conn);
exit(1);
}
int main(int argc, char ** argv) {
const char * conninfo;
const char * stmtName = "TEST_STATEMENT";
PGconn * conn;
PGresult * res;
if (argc > 1) {
conninfo = argv[1];
} else {
fprintf(stderr, "Please provide a connection string as the first argument");
}
conn = PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK) {
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
graceful_failure(conn);
}
res = PQprepare(
conn,
stmtName,
"select concat('\\', $1::varchar, '\\') SLASHED_ID",
1,
NULL
);
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
fprintf(stderr, "Statement preparation has failed: %s", PQerrorMessage(conn));
PQclear(res);
graceful_failure(conn);
}
const char * paramValues[1];
int paramLengths[1];
paramValues[0] = "12345678";
paramLengths[0] = strlen(paramValues[0]);
res = PQexecPrepared(conn,
stmtName,
1,
paramValues,
paramLengths,
NULL,
0
);
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
fprintf(stderr, "Query execution has failed: %s", PQerrorMessage(conn));
PQclear(res);
graceful_failure(conn);
} else {
fprintf(stdout, "Got the following result: %s", PQgetvalue(res, 0, 0));
}
PQclear(res);
PQfinish(conn);
return 0;
}
, , :
$ gcc libpqtest.c -I /usr/include/postgresql -o libpqtest -lpq && ./libpqtest "$CONNECTION_STRING"
Got the following result: \12345678\
, - - PDO
.
, . , ? PDO
, , libpq
?
import os
import psycopg2
conn = psycopg2.connect(os.getenv("CONNECTION_STRING"))
cursor = conn.cursor()
cursor.execute("select concat('\\', %(ID)s, '\\') SLASHED_ID", {"ID": "12345678"})
for row in cursor:
print(row)
cursor.close()
conn.close()
:
('\\12345678\\',)
2
, . , PDO
, - , - PostgreSQL PHP, , pgsql
. :
<?php
$connectionString = getenv('CONNECTION_STRING');
$connection = pg_connect($connectionString);
if ($connection) {
$stmt = pg_prepare($connection, "query_with_slashes", "select concat('\\', concat($1::varchar, '\\')) SLASHED_ID");
if (!$stmt) {
print "Statement preparation has failed";
} else {
if (pg_execute($connection, "query_with_slashes", ['12345678'])) {
print "Query successfully executed\n";
} else {
$info = pg_last_error();
print "Query execution has failed, reason: {$info}";
}
}
} else {
print "Connection attempt to {$connectionString} has failed, check the CONNECTION_STRING environment variable\n";
}
Query successfully executed
, .
, , , . : (, ) — , . ASCII- , chr
:
select chr(92) || t.SOME_FIELD || chr(92) SLASHED_FIELD
from SOME_TABLE t
where instr(';' || :ID || ';', ';' || t.ID || ';') > 0
, ! : , - , . , , .
, . , . , , PHP, https://bugs.php.net Open, , , , . , .
, , :
- , —
FALSE
" "
, ; - open source — , , . , — Preptember, Hacktoberfest.
, :
- PHP 7.3.18, libpq-dev 10.12;
- PHP 7.4.5, libpq-dev 11.7.