How do I deal with long SQL statements

[jpottsx1]

Hi,

I have an extremely long sql statement and I'm wondering how to best get it into LiveCode since the "\" character doesn't seem to work since the statement is between parenthesis. When I set it all in one line it gets really hard to manage.

Here is an example of the sql code.

Code: Select all

--Table: Mood

--DROP TABLE Mood;

CREATE TABLE Mood (
  ID                integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
  Date_Added        datetime DEFAULT CURRENT_DATE,
  Date_Modified     datetime DEFAULT CURRENT_DATE,
  To_Sync           boolean,
  Empty             boolean,
  UserField1        text,
  UserField2        text,
  UserField3        text,
  UserField4        text,
  UserField5        text,
  UserField6        text,
  UserField7        text,
  UserField8        text,
  UserField9        text,
  UserField10       text,
  Mania_Field_1     text,
  Mania_Field_2     text,
  Mania_Field_3     text,
  Mania_Field_4     text,
  Mania_Field_5     text,
  Mania_Field_6     text,
  Mania_Field_7     text,
  Mania_Field_8     text,
  Mania_Field_9     text,
  Mania_Field_10    text,
  Mania_Field_11    text,
  Mania_Field_12    text,
  Mania_Field_13    text,
  Mania_Field_14    text,
  Mania_Field_15    text,
  Mania_Field_16    text,
  Mania_Field_17    text,
  Mania_Field_18    text,
  Mania_Field_19    text,
  Mania_Field_20    text,
  Mania_Field_21    text,
  Mania_Field_22    text,
  Mania_Field_23    text,
  Mania_Field_24    text,
  Mania_Field_25    text,
  Mania_Field_26    text,
  Mania_Field_27    text,
  Depress_Field_1   text,
  Depress_Field_2   text,
  Depress_Field_3   text,
  Depress_Field_4   text,
  Depress_Field_5   text,
  Depress_Field_6   text,
  Depress_Field_7   text,
  Depress_Field_8   text,
  Depress_Field_9   text,
  Depress_Field_10  text,
  Depress_Field_11  text,
  Depress_Field_12  text,
  Depress_Field_13  text,
  Depress_Field_14  text,
  Depress_Field_15  text,
  Depress_Field_16  text,
  Depress_Field_17  text,
  Depress_Field_18  text,
  Depress_Field_19  text,
  Depress_Field_20  text,
  Depress_Field_21  text,
  Depress_Field_22  text,
  Depress_Field_23  text,
  Depress_Field_24  text,
  Depress_Field_25  text,
  Depress_Field_26  text,
  Depress_Field_27  text,
  Mood              integer NOT NULL DEFAULT 0
);

CREATE UNIQUE INDEX Mood_Index_Date_Added
  ON Mood
  (Date_Added);

CREATE UNIQUE INDEX Mood_Index_Date_Mod
  ON Mood
  (Date_Modified);

When nI get it into one line it is unbearable.

Code: Select all

CREATE TABLE Mood ( ID  integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, Date_Added datetime DEFAULT CURRENT_DATE, Date_Modified datetime DEFAULT CURRENT_DATE, To_Sync    boolean, Empty  boolean, UserField1 text, UserField2 text, UserField3 text, UserField4 text, UserField5 text, UserField6 text, UserField7 text, UserField8 text, UserField9 text, UserField10   text, Mania_Field_1 text, Mania_Field_2 text, Mania_Field_3 text, Mania_Field_4 text, Mania_Field_5 text, Mania_Field_6 text, Mania_Field_7 text, Mania_Field_8 text, Mania_Field_9 text, Mania_Field_10    text, Mania_Field_11    text, Mania_Field_12    text, Mania_Field_13    text, Mania_Field_14    text, Mania_Field_15    text, Mania_Field_16    text, Mania_Field_17    text, Mania_Field_18    text, Mania_Field_19    text, Mania_Field_20    text, Mania_Field_21    text, Mania_Field_22    text, Mania_Field_23    text, Mania_Field_24    text, Mania_Field_25    text, Mania_Field_26    text, Mania_Field_27    text, Depress_Field_1   text, Depress_Field_2   text, Depress_Field_3   text, Depress_Field_4   text, Depress_Field_5   text, Depress_Field_6   text, Depress_Field_7   text, Depress_Field_8   text, Depress_Field_9   text, Depress_Field_10  text, Depress_Field_11  text, Depress_Field_12  text, Depress_Field_13  text, Depress_Field_14  text, Depress_Field_15  text, Depress_Field_16  text, Depress_Field_17  text, Depress_Field_18  text, Depress_Field_19  text, Depress_Field_20  text, Depress_Field_21  text, Depress_Field_22  text, Depress_Field_23  text, Depress_Field_24  text, Depress_Field_25  text, Depress_Field_26  text, Depress_Field_27  text, Mood   integer NOT NULL DEFAULT 0
CREATE UNIQUE INDEX Mood_Index_ID
  ON Mood
  (ID);

Suggestions are desperately needed.

[dunbarx]

Does this help?

If I write a simple function:

on mouseUp
put doRandom(999999)
end mouseUp

function doRandom var
return random(var)
end doRandom

All is well.

But if I try to break it up as you indicated:

on mouseUp
put doRandom(999\
999)
end mouseUp

LC throws an error.

But this works:

on mouseUp
do "put doRandom(999"\
& "999)"
end mouseUp

A lot of extra stuff, to my mind, but it does allow a line to be broken even when enclosed by parentheses. The "do" construct allows you to form a string that can be evaluated, since it constructs the characters by concatenating, and it doesn't care about how those chars are laid out, only that the final string makes sense to the parser.

Craig Newman

[bangkok]

Use a loop, or several loops.

Code: Select all

put "CREATE TABLE Mood ( ID  integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, Date_Added datetime DEFAULT CURRENT_DATE, Date_Modified datetime DEFAULT CURRENT_DATE, To_Sync, boolean, Empty  boolean " into sqlQuery

repeat with i = 1 to 27
put ",Mania_Field_"& i &",text" after sqlQuery
end repeat 

.... execute