Sql 104 ошибка

Error -104 creating Firebird stored procedure

I cannot run the following SP

The error I get is the following:

1 Answer 1

Have you used Set Term before and after this code?

All commands in Firebird must be terminated with a semi-colon. If you want to create a stored procedure you need to be able to distinguish between the terminating semi-colon from the semi-colons inside the stored procedure.

Something like this:

Please notice how the declaration of the stored procedure is terminated with ^, thus ending the statement. After the declaration you also restore the terminating semi-colon.

On a side note, I would recommend to copy firebird.msg to the location the error you get tells you so you can see what is really happening.

EDIT:

If you wish you can check this link. There you can find a lot of information regarding Firebird + IBExpress, including SET TERM (page 81).

EDIT 2:

Just tried at home with IBExperts + Firebird and I had no problem creating the stored procedure. My guess is you are trying to do one of the following things:

You have opened an SQL editor and are trying to compile the code directly. That will not work because IBExperts thinks you are trying to run DSQL sentences. Stored procedures are created with PSQL sentences.

You are trying to use the «New procedure» utility (check buttons in the upper right side of the main menu) and pasted the whole code into the editor. That will not work because in that editor you only have to put the body code. Stored procedure name is set in a field on the upper right side of the window you opened. Parameters and variables are introduced by using the «Insert Parameter/Variable» button on the left side above the code editor. The SET TERM sentences are created automatically by IBExperts. You can check the resulting code in the DDL tab.

Источник

SQL Server Error Msg 104 – ORDER BY items must appear in the select list if the statement contains a UNION

SQL Server Error Message

Msg 104, Level 16, State 1, Line 6
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Reason for the Error

This error usually happens when you use UNION operator in SQL Server and combine the results of the multiple SELECT statements and you specify an ORDER BY clause where the columns are not included in the SELECT list.

Example

Assume that you have two tables that contains Students and PartTimeStudents in a College. Both tables have the fields – First Name and LastName and you wish to contacenate both the FirstName and LastName and display the Full Name. You have used LastName in the ORDER BY clause as shown below.

Since LastName is used in the ORDER BY clause and not used in the SELECT Query when using UNION, you’ll notice the below error.

Msg 104, Level 16, State 1, Line 6
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Solution

This error message can be avoided by simply removing the ORDER BY clause from your query. If you need the ORDER BY LastName , you’ll need to include the field (LastName) in the SELECT query.

Источник

Problems executing SQL-script using Firebird.NET 2.5 (Error Code = -104)

Sorry for my English first of all. I have a problem and need help. I have a simple tool made by myself on c#. This tool makes connect to local or remote firebird server (v.2.5). And my tool can create specified .fdb file (database) somewhere on the server.

Also I have a file with SQL statements (create table, triggers and so on). I want to execute this file after database was created. Executing this file will fill structure of user database — not data, only structure.

But then I try to execute my SQL script — firebird server returns a

SQL error code = -104 Token unknown line xxx column xxx.

That’s the line on this CREATE TABLE SQL statement, for example:

If I will leave only one create statement in my file — all will be good. I don’t know how I explained (it’s clear or not)) — another words — why can’t I execute full query with many create statements in one transaction? There is my main method which executes query:

There is a query is my SQL file.

2 Answers 2

As Victor already stated in his final comment, you can use the FBScript class for batch execution.

I was just confronted with the same task. This question pointed me in the right direction but i had to do some further digging.

I this example, the source of the statements is a external script file:

This will work fine, but you may wonder why this whole thing isn’t surrounded by a transaction. Actually there is no support to «bind» FbBatchExecution to a transaction directly.

The first thing i tried was this (will not work)

This will result in an exception stating: «Execute requires the Command object to have a Transaction object when the Connection object assigned to the command is in a pending local transaction. The Transaction property of the Command has not been initialized.»

This means nothing more than that the commands that are executed by FbBatchExecution are not assigned to our local transaction that is surrounding the code block. What helps here is that that FbBatchExecution provides the event CommandExecuting where we can intercept every command and assign our local transaction like this:

Note that i have uncommented the myTransaction.Commit() line. I was a little bit surprised by this behavior, but if you keep that line the transaction will throw an exception stating that it has already been committed. The bool parameter fbe.Execute(true) is named «autoCommit», but changing this to false seems to have no effect.

I would like some feedback if you see any potential issues with assigning the local transaction this way, or if it has any benefits at all or could as well be omitted.

Источник

DB2 SQL error -104 when creating foreign key

I’m trying to create temporary tables to do some testing and I’m facing this error:

when trying to create 2 temporary tables with 1 foreign key, as follows:

If I remove the constraint part, it executes fine. I’ve tried both references session.company (id_comp) and references session.company.id_comp , with the same result.

How can I solve this?

Thanks in advance.

UPDATE:

It might be considered a different question but, as it was suggested that I generate id_comp as a PRIMARY_KEY in session.company , I can’t get it work this way neither.

I tried to create a table with a PRIMARY KEY in a new script with that simple table (as you can see, I tried to do it with the primary key constraint after a comma:

Also tried all this different options:

and none of them works, all return `Error Code: -104′.

1 Answer 1

If your Db2 server runs on Linux/Unix/Windows, then a DGTT cannot participate in declarative RI. See the documentation at this link specifically this text:

Restrictions on the use of declared temporary tables: Declared temporary tables cannot. Be specified in referential constraints (SQLSTATE 42995).

You can use programmed RI however (that is to say, manually enforce the integrity by using set null or delete as appropriate for your data model and business). This means your code must populate both tables and then program the RI checks and resulting actions (to set null or delete rows) accordingly using plain SQL.

You should explain clearly why you don’t want persistent tables so that the motivation is known — you then may get a better solution.

You can use persistent tables with the ‘not logged ‘ characteristic at transaction level, but this is a bad idea because you then have to drop/recreate persistent tables after any Db2 error.

If you do not need DGTT (session tables) and if you are happy to use persistent tables then the example syntax below is valid for Db2 current versions on LUW:

Источник

Как исправить ошибку Dynamic Sql: -104; Token Unkown — использование подзапроса в предложении where для фильтрации по максимальной дате

Я пытаюсь запросить базу данных firebird в первый раз, и я продолжаю получать

Кажется, это проблема с пунктом WHERE . CERT_DATE — это тип данных TIMESTAMP . Спасибо!

Я попытался использовать TIMESTAMP , думая, что это может быть ошибкой, но получил те же результаты. Любая помощь будет принята с благодарностью.

Запрос выглядит разумным. Иногда повторный ввод запроса может исправить ситуацию, потому что случайно включен какой-то скрытый символ.

Спасибо. попробую перепечатать. я не объединяю; ну не намеренно 😉 Я использую бесплатную пробную версию RazorSQL. Обычно я использую MSSQL Server.

Ваше сообщение об ошибке должно показать вам столбец и строку ошибки, а также конкретный токен, который там не разрешен. Так что читайте и ставьте ВЕСЬ текст той ошибки

Какую версию Firebird вы используете? И, пожалуйста, опубликуйте полное сообщение об ошибке, обычно в неизвестных ошибках токена также есть строка с «неизвестным» токеном, вызвавшим ошибку.

Ах. да, я забыл включить номера столбцов и строк в сообщение об ошибке. Они заключаются в следующем: ОШИБКА: динамическая ошибка SQL; Код ошибки SQL = -104; Токен неизвестен — строка 11, столбец 30; AS [SQLState: 42000, код ошибки ISC: 335544634] Код ошибки: 335544634 Спасибо!

Спасибо всем за вашу помощь! Сегодня я узнал, что использую старую версию Firebird, которая больше не поддерживается, и синтаксис, к которому я привык, отличается. Следующий шаг — обновиться до Firebird 2.0 или перейти на MSSQL.

@ M.Coull Я бы посоветовал, если вы собираетесь обновиться, перейти на 2.5 или 3.0. Firebird 2.0 также больше не поддерживается.

Ошибка указывает на то, что вы используете Firebird 1.5 или более раннюю версию. Проблема в том, что Firebird 1.5 и более ранние версии не поддерживают AS для определения псевдонимов таблиц.

В InterBase 6, Firebird 1 и Firebird 1.5 предложение FROM было определено как (из справочника по языку InterBase 6.0):

Как видите, этот синтаксис не позволяет использовать AS перед alias (иначе это был бы [[AS] alias] ). Эта поддержка дополнительного токена AS была добавлена ​​в Firebird 2.0 как часть поддержки Производные таблицы.

В качестве краткосрочного решения замените CERTS AS Z на CERTS Z .

Однако вам действительно следует обновиться: Firebird 1.5 больше не поддерживается (поддержка была прекращена почти 10 лет назад!) и содержит известные проблемы безопасности, которые были исправлены в более поздних выпусках Firebird.

Большое спасибо! Я только что унаследовал эту базу данных Firebird в понедельник, и у меня никогда не было опыта работы с Firebird в прошлом. Я бы никогда не догадался об этом сам. План состоит в том, чтобы перенести данные в базу данных MSSQL.

@ M.Coull В этом случае это компромисс. Начиная с версии 1.5 было добавлено множество более продвинутых функций SQL, которые могут помочь при миграции, с другой стороны, простое обновление базы данных 1.5 до версии 2.0 или выше может привести к некоторым проблемам совместимости, с которыми вы, возможно, не захотите иметь дело, если вы просто делаете разовая миграция.

@ M.Coull В любом случае, я знал, что AS должен работать, поэтому нужно было попробовать более старые версии, пока я не получил ту же ошибку;)

Ты человек! Еще раз спасибо!

1.5 на 2.0 изменили многие нестандартные причуды Interbase, хотя и в сторону более формальных и неформальных стандартов SQL, и могут нарушить работу приложения. У нас есть внутренняя самодельная CRM, которая скоро мирно умрет, она до сих пор использует BDE и FB 1.5 и ее не удалось перенести даже на 2.1. Так что, несмотря на то, что использование 1.5 действительно ужасно, мигрировать может быть не так просто.

Источник

Have you used Set Term before and after this code?

All commands in Firebird must be terminated with a semi-colon. If you want to create a stored procedure you need to be able to distinguish between the terminating semi-colon from the semi-colons inside the stored procedure.

Something like this:

SET TERM ^ ;

CREATE PROCEDURE SP_NYANSAT(
        FORNAVN VARCHAR(30),
        EFTERNAVN VARCHAR(30),
        ADRESSE VARCHAR(50),
        POSTNUMMER CHAR(4),
        TELEFONNUMMER CHAR(8),
        EMAIL VARCHAR(50))
    AS
    DECLARE VARIABLE ID INTEGER;
    BEGIN
      ID = GEN_ID(GEN_ANSAT_ID,1);
      INSERT INTO MYTABLE (ID, FORNAVN, EFTERNAVN, ADRESSE, POSTNUMMER, TELEFONNUMMER, EMAIL) VALUES (:ID, :FORNAVN, :EFTERNAVN, :ADRESSE, :POSTNUMMER, :TELEFONNUMMER, :EMAIL);
    END
    ^

SET TERM ; ^ 

Please notice how the declaration of the stored procedure is terminated with ^, thus ending the statement. After the declaration you also restore the terminating semi-colon.

On a side note, I would recommend to copy firebird.msg to the location the error you get tells you so you can see what is really happening.

EDIT:

If you wish you can check this link. There you can find a lot of information regarding Firebird + IBExpress, including SET TERM (page 81).

EDIT 2:

Just tried at home with IBExperts + Firebird and I had no problem creating the stored procedure. My guess is you are trying to do one of the following things:

• You have opened an SQL editor and are trying to compile the code directly. That will not work because IBExperts thinks you are trying to run DSQL sentences. Stored procedures are created with PSQL sentences.

• You are trying to use the «New procedure» utility (check buttons in the upper right side of the main menu) and pasted the whole code into the editor. That will not work because in that editor you only have to put the body code. Stored procedure name is set in a field on the upper right side of the window you opened. Parameters and variables are introduced by using the «Insert Parameter/Variable» button on the left side above the code editor. The SET TERM sentences are created automatically by IBExperts. You can check the resulting code in the DDL tab.

HTH

Have you used Set Term before and after this code?

All commands in Firebird must be terminated with a semi-colon. If you want to create a stored procedure you need to be able to distinguish between the terminating semi-colon from the semi-colons inside the stored procedure.

Something like this:

SET TERM ^ ;

CREATE PROCEDURE SP_NYANSAT(
        FORNAVN VARCHAR(30),
        EFTERNAVN VARCHAR(30),
        ADRESSE VARCHAR(50),
        POSTNUMMER CHAR(4),
        TELEFONNUMMER CHAR(8),
        EMAIL VARCHAR(50))
    AS
    DECLARE VARIABLE ID INTEGER;
    BEGIN
      ID = GEN_ID(GEN_ANSAT_ID,1);
      INSERT INTO MYTABLE (ID, FORNAVN, EFTERNAVN, ADRESSE, POSTNUMMER, TELEFONNUMMER, EMAIL) VALUES (:ID, :FORNAVN, :EFTERNAVN, :ADRESSE, :POSTNUMMER, :TELEFONNUMMER, :EMAIL);
    END
    ^

SET TERM ; ^ 

Please notice how the declaration of the stored procedure is terminated with ^, thus ending the statement. After the declaration you also restore the terminating semi-colon.

On a side note, I would recommend to copy firebird.msg to the location the error you get tells you so you can see what is really happening.

EDIT:

If you wish you can check this link. There you can find a lot of information regarding Firebird + IBExpress, including SET TERM (page 81).

EDIT 2:

Just tried at home with IBExperts + Firebird and I had no problem creating the stored procedure. My guess is you are trying to do one of the following things:

• You have opened an SQL editor and are trying to compile the code directly. That will not work because IBExperts thinks you are trying to run DSQL sentences. Stored procedures are created with PSQL sentences.

• You are trying to use the «New procedure» utility (check buttons in the upper right side of the main menu) and pasted the whole code into the editor. That will not work because in that editor you only have to put the body code. Stored procedure name is set in a field on the upper right side of the window you opened. Parameters and variables are introduced by using the «Insert Parameter/Variable» button on the left side above the code editor. The SET TERM sentences are created automatically by IBExperts. You can check the resulting code in the DDL tab.

HTH