Multiple record insert - Sql - ALS11

[Former Member]

Hi, I'm tried to do a multi record insert in one simple sql instruccions but doesn't workd

   INSERT into TTABLE1
   (FIELD1, FIELD2, FIELD3)
   values
   (VALUE1, VALUE2, VALUE3),
  (VALUE21, VALUE22, VALUE23),
  (VALUE31, VALUE32, VALUE33), 

 (VALUE1, VALUE2, VALUE3), (VALUE1, VALUE2, VALUE3), ( (VALUE1, VALUE2, VALUE3),VALUE1, VALUE2, VALUE3),

I get the error poQuery: Error 7200:  AQE Error:  State = 42000;   NativeError = 2117;  [iAnywhere Solutions][Advantage SQL Engine]Unexpected token: , -- Expecting semicolon. -- Location of error in the SQL statement is: 184 (line: 4 column: 70).

If I put semicolon get

poQuery: Error 7200:  AQE Error:  State = 42000;   NativeError = 2115;  [iAnywhere Solutions][Advantage SQL Engine]Expected lexical element not found: Your statement must start with CREATE, DROP, SELECT, INSERT, UPDATE, ALTER, EXECUTE, DELETE, GRANT, REVOKE, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, SET, DEBUG or a script statement.

If it possible in ADS, do a multi records insert in one sql statement? How?.

Thnks

[joachim_drr · Accepted solution]

this is a new feature in ADS 12...with ADS 11 this will not work.

[Former Member · Accepted solution]

Hi Joachin, I just downloaded the Evaluation version ADS 12 and get the new version of tdataset.exe for delphi and data architect.

In data architect SQL Multiple row insert works well.

I tried this way and works.

1.    INSERT into TTABLE1 
2.    (FIELD1, FIELD2, FIELD3) 
3.    values 
4.    (VALUE1, VALUE2, VALUE3), 
5.   (VALUE21, VALUE22, VALUE23), 
6.   (VALUE31, VALUE32, VALUE33); 

But from delphi project i ised TadsQuery this way

1. AdsQuery1.SQL.append('INSERT into TTABLE1');
2. AdsQuery1.SQL.append('(FIELD1, FIELD2, FIELD3)');
3. AdsQuery1.SQL.append('values');
4. AdsQuery1.SQL.append('(VALUE1, VALUE2, VALUE3),');
5. AdsQuery1.SQL.append('(VALUE21, VALUE22, VALUE23),');
6. AdsQuery1.SQL.append('(VALUE31, VALUE32, VALUE33);');  
7. AdsQuery1.ExecSQL;

And doesn't works. Get error UNEXPECTED TOKEN. , expecting semicolon. Line 4 column 25

But can't be semicolon, must be comma.

What I'm doing wrong?

[joachim_drr · Accepted solution]

check the properties of your AdsQuery component if it's still an old version ...

[Former Member · Accepted solution]

In AdsQuery Properties says VERSION 12.

I unninstalled Tdataset v12, deleted all references to Library in Delphi IDE, Run a registry cleaner and installed again and begin to works.

But there is something wrong now in my syntax I guess.

I'm filling the adsquery like say before. And at run time get error 7200 Aqe error S0000 native error =2121. Column not found VALUE1

If I put quotes 'VALUE1' Delphi IDE shows syntax error so value cannot be quoted.

If I remove field1 and their values, error halt at VALUE2.

Any idea?

[joachim_drr · Accepted solution]

what are the data types? Do they match? If you insert strings, then you need to quote:

AdsQuery.SQL.Add('('+QuotedStr('VALUE1')+', VALUE2, VALUE3),');

It might even be better to use parameters.

[Former Member · Accepted solution]

Thanks Joachim

I was missing the QuotedStr.

The error was because my strings values, they are fixed and passed in the sqlstrings. Other values are variable passed  by parameters with no problems.

Now works fine. Nice.

I have been using ALS since 9 version, and I see you're improving performance on every update.