Query failed error ошибка синтаксиса примерное положение

Пытаюсь загрузить данные в таблицу Postgres. Скрипт и данные находятся на локальном сервере Open Server, а бд на другом, удаленном серваке, к которому есть полный доступ (соединяюсь с базой через менеджер бд и все редактирую). Подключение к базе при запуске скрипта успешно происходит, но вот запрос INSERT INTO не срабатывает:

$pg = "INSERT INTO public.1253_data () VALUES " . implode(",", $temp_array);

Ошибка выдается от функции pg_query, пишет: Warning: pg_query(): Query failed: Ошибка синтаксиса (примерное положение: .1253_data).

Я запускаю функцию pg_result_query():

$result = pg_query($conn, $sql);


 if ($result) {
   echo "New record created successfully";
} else {
      $k = pg_get_result($result);
      echo 'Query failed1: ' . pg_result_error($k);
}
pg_close($conn);

В результате pg_result_error дает пустоту:

введите сюда описание изображения

Судя по документации, pg_result_дает такое только тогда, когда ошибок никаких нет. Но тогда почему собственный дескриптор ошибки pg_query такой?

Только начинаю pg изучать, все перепробовал в поисках ошибки ( Эксперты, подскажите, в чем она может быть?

Источник

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH m_ty_person AS (return query execute sql)
select name, count(*) from m_ty_person where name like '%a%' group by name
union
select name, count(*) from m_ty_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;

But, this ended up in PostgreSQL error 42601. And he got the following error message,

ERROR: syntax error at or near "return"
LINE 5: WITH m_ty_person AS (return query execute sql)

Our PostgreSQL Engineers checked the issue and found out the syntax error. The statement in Line 5 was a mix of plain and dynamic SQL. In general, the PostgreSQL query should be either fully dynamic or plain. Therefore, we changed the code as,

RETURN QUERY EXECUTE '
WITH m_ty_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM m_ty_person WHERE name LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM m_ty_person WHERE gender = 1 GROUP BY name$x$;

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

$pg = "INSERT INTO public.1253_data () VALUES " . implode(",", $temp_array);

Ошибка выдается от функции pg_query, пишет: Warning: pg_query(): Query failed: Ошибка синтаксиса (примерное положение: .1253_data).

Я запускаю функцию pg_result_query():

$result = pg_query($conn, $sql);


 if ($result) {
   echo "New record created successfully";
} else {
      $k = pg_get_result($result);
      echo 'Query failed1: ' . pg_result_error($k);
}
pg_close($conn);

В результате pg_result_error дает пустоту:

введите сюда описание изображения

Только начинаю pg изучать, все перепробовал в поисках ошибки ( Эксперты, подскажите, в чем она может быть?

Источник

So, you’re creating a custom SQL query to perform a task in the database. After putting the code together and running it in PHPmyAdmin it responds with a 1064 error. It may look similar to this:

The 1064 error displays any time you have an issue with your SQL syntax, and is often due to using reserved words, missing data in the database, or mistyped/obsolete commands. So follow along and learn more about what the 1064 error is, some likely causes, and general troubleshooting steps.

Note: Since syntax errors can be hard to locate in long queries, the following online tools can often save time by checking your code and locating issues:

PiliApp MySQL Syntax Check
EverSQL SQL Query Syntax Check & Validator

Causes for the 1064 error

Reserved Words
Missing Data
Mistyped Commands
Obsolete Commands

This may seem cryptic since it is a general error pointing to a syntax issue in the SQL Query statement. Since the 1064 error can have multiple causes, we will go over the most common things that will result in this error and show you how to fix them. Follow along so you can get your SQL queries updated and running successfully.

Using Reserved Words

Every version of MySQL has its own list of reserved words. These are words that are used for specific purposes or to perform specific functions within the MySQL engine. If you attempt to use one of these reserved words, you will receive the 1064 error. For example, below is a short SQL query that uses a reserved word as a table name.

CREATE TABLE alter (first_day DATE, last_day DATE);

How to fix it:

Just because the word alter is reserved does not mean it cannot be used, it just has special requirements to use it as the MySQL engine is trying to call the functionality for the alter command. To fix the issue, you will want to surround the word with backticks, this is usually the button just to the left of the “1” button on the keyboard. The code block below shows how the code will need to look in order to run properly.

CREATE TABLE `alter` (first_day DATE, last_day DATE);

Missing Data

Sometimes data can be missing from the database. This causes issues when the data is required for a query to complete. For example, if a database is built requiring an ID number for every student, it is reasonable to assume a query will be built to pull a student record by that ID number. Such a query would look like this:

SELECT * from students WHERE studentID = $id

If the $id is never properly filled in the code, the query would look like this to the server:

SELECT * from students WHERE studentID =

Since there is nothing there, the MySQL engine gets confused and complains via a 1064 error.

How to fix it:

Hopefully, your application will have some sort of interface that will allow you to bring up the particular record and add the missing data. This is tricky because if the missing data is the unique identifier, it will likely need that information to bring it up, thus resulting in the same error. You can also go into the database (typically within phpMyAdmin) where you can select the particular row from the appropriate table and manually add the data.

Mistyping of Commands

One of the most common causes for the 1064 error is when a SQL statement uses a mistyped command. This is very easy to do and is easily missed when troubleshooting at first. Our example shows an UPDATE command that is accidentally misspelled.

UDPATE table1 SET id = 0;

How to fix it:

Be sure to check your commands prior to running them and ensure they are all spelled correctly.

Below is the syntax for the correct query statement.

UPDATE table1 SET id = 0;

Obsolete Commands

Some commands that were deprecated (slated for removal but still allowed for a period of time) eventually go obsolete. This means that the command is no longer valid in the SQL statement. One of the more common commands is the ‘TYPE‘ command. This has been deprecated since MySQL 4.1 but was finally removed as of version 5.1, where it now gives a syntax error. The ‘TYPE‘ command has been replaced with the ‘ENGINE‘ command. Below is an example of the old version:

CREATE TABLE t (i INT) TYPE = INNODB;

This should be replaced with the new command as below:

CREATE TABLE t (i INT) ENGINE = INNODB;

For developers or sysadmins experienced with the command line, get High-Availability and Root Access for your application, service, and websites with Cloud VPS Hosting.

Error 1064 Summary

As you can see there is more than one cause for the 1064 error within MySQL code. Now, you know how to correct the issues with your SQL Syntax, so your query can run successfully. This list will be updated as more specific instances are reported.

Источник

Вот пример из документации

QSqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
              "VALUES (:id, :forename, :surname)");
query.bindValue(":id", 1001);
query.bindValue(":forename", "Bart");
query.bindValue(":surname", "Simpson");
query.exec();


  Если вы пытаетесь сделать bind, то вам нужно правильно сформировать строку запроса, плюс указать bind привязки.
 

  В данном случае наличие кавычек уже не нужно будет.
 
bool DataBase::inserIntoTable(const QVariantList &data)
{

    QSqlQuery query;

   query.prepare("INSERT INTO public.NameTable (FirstName, SurName, Nik) VALUES (:db, 'cc', 'bb');");
   query.bindValue(":db",       data[0].toString());

    if(!query.exec()){
        qDebug() << "error insert into " << TABLE;
        qDebug() <<  query.lastQuery();
        qDebug() << query.lastError().text();
        return false;
    } else {
        return true;
    }
    return false;
}


  В статье, по которой вы это делаете были макросы, поэтому для конкатенации строк нужно было делать разделение кавычками. А вы очевидно формируете запрос сразу без макросов.

Источник

msm.ru

Нравится ресурс?
Помоги проекту!

информация о разделе

Данный раздел предназначается исключительно для обсуждения вопросов использования языка запросов SQL. Обсуждение общих вопросов, связанных с тематикой баз данных — обсуждаем в разделе «Базы данных: общие вопросы». Убедительная просьба — соблюдать «Правила форума» и не пренебрегать «Правильным оформлением своих тем». Прежде, чем создавать тему, имеет смысл заглянуть в раздел «Базы данных: FAQ», возможно там уже есть ответ.

UPDATE SELECT
, PostgreSQL 9.4

Подписаться на тему
Сообщить другу
Скачать/распечатать тему

Сообщ.
#1

,
22.03.16, 12:19

Senior Member

Рейтинг (т): 13

Expanded Wrap disabled

create table t1 (id integer, f1 integer, f2 integer);

create table t2 (f1 integer, f2 integer);

update t1 set (f1, f2) =

(select t2.f1, t2.f2 from t1 right join t2 on t1.id = t2.f1);

[Err] ОШИБКА: ошибка синтаксиса (примерное положение: «SELECT»)
LINE 2: (SELECT
^
Я никак не могу сообразить, как правильно обновлять значения в таблице на основании результатов SELECT .. FROM .. JOIN.
Подскажите пожалуйста! :wall:

grgdvo

Сообщ.
#2

,
22.03.16, 20:20

Member

Рейтинг (т): 21

какая версия PG у вас?? Такой синтаксис только начиная с 9.5

HighMan

Сообщ.
#3

,
23.03.16, 07:09

Senior Member

Рейтинг (т): 13

Цитата grgdvo @ 22.03.16, 20:20

Я в топе указал, что PostgreSQL 9.4.
Печально, что вышеприведенная конструкция работает лишь с 9.5.
Но должна же быть схожая конструкция для младших версий.
Вариант с where = (SELECT …) не интересен.
Нужно обновление таблицы данными и по условию выборки из других таблиц.

MIF

Сообщ.
#4

,
23.03.16, 08:31

Попробуй такой запрос:

Expanded Wrap disabled

update t1

set t1.f1= t2.f1,

t1.f2 = t2.f2

from t1

right join t2 on t1.id = t2.f1

grgdvo

Сообщ.
#5

,
23.03.16, 12:10

Member

Рейтинг (т): 21

MIF, t1 нельзя указывать и под UPDATE и под FROM.

HighMan, попробуйте вот так, вроде эквивалентно

Expanded Wrap disabled

update t1 set (f1, f2) = (t2.f1, t2.f2)

from t2 where t1.id = t2.f1;

HighMan

Сообщ.
#6

,
23.03.16, 18:21

Senior Member

Рейтинг (т): 13

Expanded Wrap disabled

update t1 set (f1, f2) = (t2.f1, t2.f2)

from t2 where t1.id = t2.f1;

Такой способ работает, но я не представляю как подобным запросом обрабатывать связи таблиц источников.

grgdvo

Сообщ.
#7

,
23.03.16, 20:16

Member

Рейтинг (т): 21

Вы можете делать JOIN практически также как в SELECT. Например

Expanded Wrap disabled

update t1 set (f1, f2) = (t2.f1, t2.f2)

from t2, t3 where t1.id = t2.f1 and t2.f2 = t3.id;

Expanded Wrap disabled

update t1 set (f1, f2) = (t2.f1, t2.f2)

from t2 left join t3 on t2.f2 = t3.id where t1.id = t2.f1;

0 пользователей читают эту тему (0 гостей и 0 скрытых пользователей)

0 пользователей:

Предыдущая тема
Базы данных: SQL
Следующая тема

[ Script execution time: 0,0255 ] [ 15 queries used ] [ Generated: 31.01.23, 05:33 GMT ]

Источник

Query failed error ошибка синтаксиса примерное положение

What causes error 42601 in PostgreSQL?

How we fix the error?

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Causes for the 1064 error

Using Reserved Words

Missing Data

Mistyping of Commands

Obsolete Commands

Error 1064 Summary

А вот еще интересные материалы: