1

I am struggeling on a sql statement and can't find the correct syntax for this.

Im programming with teamdeveloper 6.1 and I am using the function SqlPrepareAndExecute(...)

What i'm trying is to insert a set of values. On mysql it would be some like:

INSERT INTO supportContacts
    (type, details)
VALUES
    ('Email', 'admin@sqlfiddle.com'),
    ('Twitter', '@sqlfiddle');

With SqlTalk i can do this...

INSERT INTO SYSADM.FOOBAR VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
\
$datatypes CHARACTER,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC
"01",0,0,0,0,0,0,0,0,0,0,0,0
"02",0,0,0,0,0,0,0,0,0,0,0,0
"03",0,0,0,0,0,0,0,0,0,0,0,0
"04",0,0,0,0,0,0,0,0,0,0,0,0
"05",0,0,0,0,0,0,0,0,0,0,0,0
"06",0,0,0,0,0,0,0,0,0,0,0,0
"07",0,0,0,0,0,0,0,0,0,0,0,0
"08",0,0,0,0,0,0,0,0,0,0,0,0
/

Bot both won't work when I fill a variable and use it in the SqlExecute function of TD 6.1

I allways get errors like Statement not ended properly or Invalid constant.

I also tried to put the values like:

('01',0,0,0,0,0,0,0,0,0,0,0,0),
('02',0,0,0,0,0,0,0,0,0,0,0,0),
('03',0,0,0,0,0,0,0,0,0,0,0,0), ...

Error...

what am I doing wrong ?

EDIT (Things I have tried untill now):

Try 1

This here all

Try 2

"INSERT INTO SYSADM.FOOBAR VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
\
$datatypes CHARACTER,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC
'01',0,0,0,0,0,0,0,0,0,0,0,0
'02',0,0,0,0,0,0,0,0,0,0,0,0"

What only sayes SQL command not properly ended

Try 3

I thought may its because of the \. Actually it is a escapechar so I escaped it like 

"INSERT INTO SYSADM.FOOBAR VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
\\
$datatypes....

Now it sayed Invalid Character

Try 4

"INSERT INTO SYSADM.FOOBAR VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
\
$da...
\'01\',0,0,0,0,0,0,0,0,0,0,0,0
\'02\',0,0,0,0,0,0,0,0,0,0,0,0"

Was a fail also! :)

Community
  • 1
  • 1
Dwza
  • 6,494
  • 6
  • 41
  • 73
  • How does your SqlPrepare-Statement look? – Stephan Keller Jan 02 '15 at 10:20
  • It's actually the same like the middle post (sqltalk). – Dwza Jan 02 '15 at 10:22
  • There are some SQLTalk syntaxes (syntacti?) that you cannot use in SqlPrepare. I guess this is the case for your tries 2 - 4. So you could either prepare a script for SQLTalk and run this or you could go over your values in a loop in CTD and insert them one row after the other. – Stephan Keller Jan 05 '15 at 08:02
  • Hmm ok.. I'm looping them at the moment. Just tried to some more efficient like I do in all other programmings, e.g. PHP and mysql – Dwza Jan 05 '15 at 08:06

3 Answers3

0

This may just be due to the example query, but I'm not sure whether you are aware how databinding works in TD:

INSERT INTO SYSADM.FOOBAR VALUES(:var1,:var2,:var3)

where var1, var2, var3 are variables visible in the function that calls SqlExecute/SqlPrepareAndExecute

Nathanyel
  • 424
  • 2
  • 5
  • As you can see in my sample I did this. But that only works in sqltalk but not in the programming source... And I know that these are variables. But I want to pass the whole data to insert. I can do this in mysql and also in normal SQL. I only struggle on the programming part... – Dwza Dec 30 '14 at 22:03
0

Is your source data in database table? Then You can use INSERT SELECT statement.

Like this:

INSERT INTO SYSADM.FOOBAR (COL1, COL2, COL3) SELECT COL1, COL2, COL3 FROM SOURCE_DATA

EDIT:

Or You can use:

Call SqlPrepareAndExecute( hSql, '
INSERT INTO FOOBAR VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
select \'01\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'02\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'03\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'04\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'05\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'06\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'07\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'08\',0,0,0,0,0,0,0,0,0,0,0,0 from dual ' )
ZephyCZ
  • 71
  • 4
  • No, its nor from a table. I have to do a Update on some customer databases every 2 years. For this I have a script that contains the needed sql. Actually I can use the given tool (SQLTalk) to execute this script. But i allways have to do some changes on this script (change years on several positions). And now I tried to create a tool where I have a input field. There I would enter `15;16;17` to do this dbupdate for year 2015, 2016 and 2016. Depending on what I insert into the input. That's why I don't store the `source_data` in the DB. – Dwza Dec 31 '14 at 10:22
0

Some code to illustrate the general syntax.
Caveats:

  1. this is done with CTD 2.1.
  2. I left out all the code to connect to the database and just assume h_SqlMain as a valid connection handle.
  3. The data comes from an array. In real life you would e.g. read a CSV file.
  4. The variables myType and myDetail are used as binding variables in the SqlPrepare-Statment because CTD 2.1 does not support the direct use of array variables as bindings.

The "Local Variable"-Part: 

String: myType
String: myDetail
String: types[*]
String: details[*]
Number: i
Sql Handle: h_SqlMain

The "Actions"-Part:

Set types[0] = 'Email'
Set details[0] = 'admin@sqlfiddle.com'
Set types[1] = 'Twitter'
Set details[1] = '@sqlfiddle'
Set types[2] = 'Foo'
Set details[2] = 'Bar'
Call SqlPrepare( h_SqlMain, 
   "INSERT INTO supportContacts (type, details) 
    VALUES(:myType, :myDetail)")
Set i=0
While i <= 2 
    Set myType = types[i]
    Set myDetail = details[i]
    Call SqlExecute( h_SqlMain )
    Set i=i+1
Stephan Keller
  • 1,623
  • 11
  • 13
  • I actually have it in this way. In my source I have a insert string that gets modified in every iteration of the while. What I have is a table that has 13 columns (each month one + one to declare days) and 31 rows to get some kind of year view. There are some number values that will be stored. Like insert into xy set month1=12345 where day='02'. So I have to create all from beginning. Like I sayed I have a while atm. But I want to insert it in one statement.):) – Dwza Jan 02 '15 at 11:45
  • Does your insert-string have qoutes? If so, you have to escape them using the backslash. – Stephan Keller Jan 02 '15 at 14:06
  • Sure it has :) it's `Set sSql="INSERT INTO... "` and than normal `Call SqlPrepareAndExecute( sSql )`. Of course in the while where sSql is modified every iteration. without the while it doesn't work. And as a large string like middle code it also don't work – Dwza Jan 02 '15 at 16:04
  • I meant, does it contain quotes like in your third example. These you have to escape. This goes for " and ' – Stephan Keller Jan 02 '15 at 16:23
  • You mean I should do like: `"... (\'01\',0,0,0,0,0,0,0,0,0,0,0,0)..."`? – Dwza Jan 02 '15 at 17:36