Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#18 CUBRID Forum: SQL Error

CUBRID Forum
open
Esen Sagynov
7
2011-01-04
2010-09-27
Esen Sagynov
No

Hello Catalin,

I encountered an SQL ERROR while editing a post on CUBRID Forum. I replicated the problem the following way:
Open any of your previous posts (or create a new one), then add the following contents:
------------------------------------------------------------------------------------
Assume you have a [b]create.sql[/b] script that you need to run with a particular database that you will create soon. Here is its contents:
[code]
CREATE TABLE T(C1 INT, C2 INT, C3 CHAR(64), C4 CHAR(256));
INSERT INTO T VALUES(0, 100, "1000", "c4");
INSERT INTO T VALUES(1, 101, "1001", "c4");
INSERT INTO T VALUES(2, 102, "1002", "c4");
INSERT INTO T VALUES(3, 103, "1003", "c4");
INSERT INTO T VALUES(4, 104, "1004", "c4");
INSERT INTO T VALUES(5, 105, "1005", "c4");
INSERT INTO T VALUES(6, 106, "1006", "c4");
INSERT INTO T VALUES(7, 107, "1007", "c4");
INSERT INTO T VALUES(8, 108, "1008", "c4");
INSERT INTO T VALUES(9, 109, "1009", "c4");
INSERT INTO T VALUES(10, 110, "1010", "c4");
CREATE INDEX IDX_T2 ON T2(C1, C2);
[/code]
------------------------------------------------------------------------------------

This should generate the error to block the SQL script to be entered. If I remove the message within the [code] block, everything is fine. Only with SQL statements inside, the error pops up.

------------------------------------------------------------------------------------
General Error
SQL ERROR [ cubrid ]

Syntax: syntax error, unexpected INTO [-493]

SQL

UPDATE phpbb_posts SET post_edit_reason = '', forum_id = 1, poster_id = 77, icon_id = 0, post_approved = 1, enable_bbcode = 1, enable_smilies = 1, enable_magic_url = 1, enable_sig = 1, post_username = '', post_subject = 'Re: Database creation', post_checksum = '85af64d9e6b1848104a4b36364aeddea', post_attachment = 0, bbcode_bitfield = 'QIA=', bbcode_uid = '16k91r6b', post_edit_locked = 0, post_text = 'There is a good solution to do what you want. Instead of creating a .sql script file and executing it, you can create a batch file [b:16k91r6b].bat[/b:16k91r6b] on Windows or [b:16k91r6b].sh[/b:16k91r6b] on Linux to do the same job. Assume you have a [b:16k91r6b]create.sql[/b:16k91r6b] script that you need to run with a particular database that you will create soon. Here is its contents: [code:16k91r6b] CREATE TABLE T(C1 INT, C2 INT, C3 CHAR(64), C4 CHAR(256)); INSERT INTO T VALUES(0, 100, ''1000'', ''c4''); INSERT INTO T VALUES(1, 101, ''1001'', ''c4''); INSERT INTO T VALUES(2, 102, ''1002'', ''c4''); INSERT INTO T VALUES(3, 103, ''1003'', ''c4''); INSERT INTO T VALUES(4, 104, ''1004'', ''c4''); INSERT INTO T VALUES(5, 105, ''1005'', ''c4''); INSERT INTO T VALUES(6, 106, ''1006'', ''c4''); INSERT INTO T VALUES(7, 107, ''1007'', ''c4''); INSERT INTO T VALUES(8, 108, ''1008'', ''c4''); INSERT INTO T VALUES(9, 109, ''1009'', ''c4''); INSERT INTO T VALUES(10, 110, ''1010'', ''c4''); CREATE INDEX IDX_T2 ON T2(C1, C2); [/code:16k91r6b] Once you have prepared the [b:16k91r6b].sql[/b:16k91r6b] SQL script, create a batch file (.bat or .sh) with the following contents. [code:16k91r6b] set -x cubrid createdb testdb_1 csql -S -i create.sql testdb_1 cubrid server start testdb_1 cubrid broker start testdb_1 java -cp .:$CUBRID/jdbc/cubrid_jdbc.jar Insert1 java -cp .:$CUBRID/jdbc/cubrid_jdbc.jar Delete java -cp .:$CUBRID/jdbc/cubrid_jdbc.jar Insert2 cubrid broker stop testdb_1 cubrid server stop testdb_1 [/code:16k91r6b] So, what it does is: 1. Runs the "cubrid createdb testdb_1" command to create a database 2. Runs the "csql -S -i create.sql testdb_1" command run your SQL script called "create.sql". 3. Starts the newly created database. 4. Starts the CUBRID Broker. 5. Stops the broker. 6. Stops the database.' WHERE post_id = 158 INTO :out_data

BACKTRACE

FILE: includes/db/cubrid.php
LINE: 130
CALL: dbal->sql_error()

FILE: includes/functions_posting.php
LINE: 2074
CALL: dbal_cubrid->sql_query()

FILE: posting.php
LINE: 1108
CALL: submit_post()

Related

Code: code

Discussion

  • Esen Sagynov
    Esen Sagynov
    2010-09-27

    • priority: 5 --> 7
    • summary: SQL Error --> CUBRID Forum: SQL Error
     
  • Esen Sagynov
    Esen Sagynov
    2010-09-27

    When I removed the "INSERT INTO T VALUES..." insert statements, there is no problem. But when I add the INSERT keyword in between, there is an error. Try it. So, what I temporarily did is added I-N-S-E-R-T instead of INSERT. This is very inconvenient

     
  • Esen Sagynov
    Esen Sagynov
    2010-09-27

    I found the same error with another post. So, briefly what it does is if it seems a separate word INSERT in the post, it throws an error. Maybe it just does not validate it.

    For example:
    INSERT/UPDATE - has no problem
    INSERT /UPDATE - throws an error.

     
  • fixed

     
    • assigned_to: catalinc --> kadishmal
    • status: open --> closed
     
  • Esen Sagynov
    Esen Sagynov
    2010-09-29

    What was the problem?

     
  • Esen Sagynov
    Esen Sagynov
    2011-01-04

    • status: closed --> open
     
  • Esen Sagynov
    Esen Sagynov
    2011-01-04

    Encountered a similar SQL error when trying to edit the http://forum.cubrid.org/viewtopic.php?f=10&t=89 post I wrote on Jan 04. As you can see there is some unexpected INTO at the end of that long query. Please fix it and apply the change to the phpBB package and upload it:

    SQL ERROR [ cubrid ]

    Syntax: syntax error, unexpected INTO [-493]

    SQL

    UPDATE phpbb_posts SET post_edit_reason = '', forum_id = 10, poster_id = 54, icon_id = 0, post_approved = 1, enable_bbcode = 1, enable_smilies = 1, enable_magic_url = 1, enable_sig = 1, post_username = '', post_subject = 'Re: Script lose connection with cubrid broker.', post_checksum = 'ba33ddeb011396471f55f67c1da1768d', post_attachment = 0, bbcode_bitfield = 'QIA=', bbcode_uid = 's23ndh81', post_edit_locked = 0, post_text = 'Hi Penguin, Here is the table [code:s23ndh81]CREATE TABLE &quot;tbl_test&quot;( &quot;id&quot; integer AUTO_INCREMENT, &quot;col1&quot; integer NOT NULL, CONSTRAINT pk_mytest_id PRIMARY KEY(&quot;id&quot;) );[/code:s23ndh81] Everything is the same as in your script, except for the column data types. I used just normal INTEGER instead of BIGINT that you''ve used. The reason I set that is the &quot;id&quot; column''s max value is 1,000,000, and &quot;col1&quot; is within the range 1~1000 ([b:s23ndh81]$rnd = rand(1,1000);[/b:s23ndh81]). So no reason to have it BIGINT. I have another suggestion off topic. In your PHP code you call the [b:s23ndh81]cubrid_execute[/b:s23ndh81] function with the query all 1,000,000 times. This, as you understand, requires much resources, though this is not a problem for CUBRID. But, since you know that your SQL query is the same: same table, same column; you just insert new records, you''d better use [b:s23ndh81]prepared statements[/b:s23ndh81] instead. See <!-- m --><a class="postlink" href="http://www.php.net/manual/en/function.cubrid-prepare.php">http://www.php.net/manual/en/function.c ... repare.php</a><!-- m --> for an example. This will increase your script''s performance overall, especially if you have millions of same operations. Let me know, if you could resolve your issue. Also, I have one more question. Do you use the default configurations for CUBRID?' WHERE post_id = 243 INTO :out_data

    BACKTRACE

    FILE: includes/db/cubrid.php
    LINE: 130
    CALL: dbal->sql_error()

    FILE: includes/functions_posting.php
    LINE: 2074
    CALL: dbal_cubrid->sql_query()

    FILE: posting.php
    LINE: 1108
    CALL: submit_post()

     
  • Esen Sagynov
    Esen Sagynov
    2011-01-04

    Here are the steps to replicate the previous error:

    1. Go to http://forum.cubrid.org/viewtopic.php?f=10&t=89 post.
    2. Click on Edit button.
    3. Paste the following text (almost same as the original, but with a few changes I needed to make).
    ============================================================
    Hi Penguin,

    Here is the table
    [code]CREATE TABLE "tbl_test"(
    "id" integer AUTO_INCREMENT,
    "col1" integer NOT NULL,
    CONSTRAINT pk_mytest_id PRIMARY KEY("id")
    );[/code]
    Everything is the same as in your script, except for the column data types. I used just normal INTEGER instead of BIGINT that you've used. The reason I set that is the "id" column's max value is 1,000,000, and "col1" is within the range 1~1000 ([b]$rnd = rand(1,1000);[/b]). So no reason to have it BIGINT.

    I have another suggestion off topic. In your PHP code you call the [b]cubrid_execute[/b] function with the query all 1,000,000 times. This, as you understand, requires much resources, though this is not a problem for CUBRID. But, since you know that your SQL query is the same: same table, same column; you just insert new records, you'd better use [b]prepared statements[/b] instead.

    See http://www.php.net/manual/en/function.cubrid-prepare.php for an example. This will increase your script's performance overall, especially if you have millions of same operations.

    Let me know, if you could resolve your issue. Also, I have one more question. Do you use the default configurations for CUBRID?

    4. Click on Submit button. It should fail in this step.

     

    Related

    Code: code