Creating MySQL Table (long code)

[Andreschrosa]

I'm trying to create a table from within DAQFactory, however, the code gets way too long, and involves some variables for the table's fields, so I wrote this:

function SQLCriaTabela(curnet, string device)
curnet = 1
device = "0"
global string name = "teste"
global string sqlscript = "CREATE TABLE powerview.leituras_"+curnet+"_"+name + \
	  "( TheTime DOUBLE, Vm_"+ curnet+"_"+ device+ " DOUBLE, " \	 
	  "V1_"+ curnet+"_"+ device	   + " DOUBLE, "+ \
	  "V2_"+ curnet+"_"+ device	   + " DOUBLE, "+ \
	  "V3_"+ curnet+"_"+ device	   + " DOUBLE, "+ \
	  "FreqMed_"+ curnet+"_"+device   + " DOUBLE, "+ \
	  "Im_"+ curnet+"_"+ device	   + " DOUBLE, "+ \
	  "I1_"+ curnet+"_"+ device	   + " DOUBLE, "+ \
	  "I2_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "I3_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "FPm_"+ curnet+"_"+device	   + " DOUBLE, "+ \
	  "FP1_"+ curnet+"_"+device	   + " DOUBLE, "+ \
	  "FP2_"+ curnet+"_"+device	   + " DOUBLE, "+ \
	  "FP3_"+ curnet+"_"+device	   + " DOUBLE, "+ \	  
	  "Pm_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "P1_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "P2_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "P3_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "Qm_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "Q1_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "Q2_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "Q3_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "Sm_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "S1_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "S2_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "S3_"+ curnet+"_"+device		+ " DOUBLE, "+ \
	  "EneAti_"+ curnet+"_"+device	+ " DOUBLE, "+ \
	  "EneCap_"+ curnet+"_"+device	+ " DOUBLE, "+ \
	  "EneInd_"+ curnet+"_"+device	+ " DOUBLE, "+ \
	  "EneAtiAcu_"+ curnet+"_"+device + " DOUBLE, "+ \
	  "DemAti_"+ curnet+"_"+device	+ " DOUBLE, "+ \
	  "DemCap_"+ curnet+"_"+device	+ " DOUBLE, "+ \
	  "DemInd_"+ curnet+"_"+device	+ " DOUBLE, "+ \
	  "DemAtiRev_"+ curnet+"_"+device + " DOUBLE, "+ \
	  "DemCapRev_"+ curnet+"_"+device + " DOUBLE, "+ \
	  "DemIndRev_"+ curnet+"_"+device + " DOUBLE, "+ \
	  "THDv1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "THDv2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "THDv3_"+ curnet+"_"+device	 + " DOUBLE, "+ \	  
	  "THDi1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "THDi2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "THDi3_"+ curnet+"_"+device	 + " DOUBLE, "+ \			
	  "H3v1_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H5v1_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H7v1_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H9v1_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H11v1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H13v1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H15v1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H17v1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H19v1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H21v1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H23v1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H25v1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H27v1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H29v1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H31v1_"+ curnet+"_"+device	 + " DOUBLE, "+ \			
	  "H3v2_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H5v2_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H7v2_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H9v2_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H11v2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H13v2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H15v2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H17v2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H19v2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H21v2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H23v2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H25v2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H27v2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H29v2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H31v2_"+ curnet+"_"+device	 + " DOUBLE, "+ \		 
	  "H3v3_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H5v3_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H7v3_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H9v3_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H11v3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H13v3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H15v3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H17v3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H19v3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H21v3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H23v3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H25v3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H27v3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H29v3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H31v3_"+ curnet+"_"+device	 + " DOUBLE, "+ \	  
	  "H3i1_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H5i1_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H7i1_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H9i1_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H11i1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H13i1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H15i1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H17i1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H19i1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H21i1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H23i1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H25i1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H27i1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H29i1_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H31i1_"+ curnet+"_"+device	 + " DOUBLE, "+ \	  
	  "H3i2_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H5i2_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H7i2_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H9i2_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H11i2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H13i2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H15i2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H17i2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H19i2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H21i2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H23i2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H25i2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H27i2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H29i2_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H31i2_"+ curnet+"_"+device	 + " DOUBLE, "+ \		 
	  "H3i3_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H5i3_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H7i3_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H9i3_"+ curnet+"_"+device	  + " DOUBLE, "+ \
	  "H11i3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H13i3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H15i3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H17i3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H19i3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H21i3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H23i3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H25i3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H27i3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H29i3_"+ curnet+"_"+device	 + " DOUBLE, "+ \
	  "H31i3_"+ curnet+"_"+device	 + " DOUBLE ) ENGINE = InnoDB;"

This wont work, the max I get into sqlscript string is:

CREATE TABLE powerview.leituras_1_teste

Any thoughts?

[AzeoTech]

Yeah, that's a pretty long string. Personally, I never use the \ when building up long strings and instead concatenate:

global string sqlscript = "CREATE TABLE powerview.leituras_"+curnet+"_"+name

sqlscript += "( TheTime DOUBLE, Vm_"+ curnet+"_"+ device+ " DOUBLE, "

sqlscript += "V1_"+ curnet+"_"+ device + " DOUBLE, "

etc.

In this case since all your fields are essentially the same data type, I'd do it with a loop. First create an array of all the channel prefixes, then just build up the string inside a for loop:

private chanprefixes = {"Vm","V1", etc...}
global string sqlscript = "CREATE TABLE powerview.leituras_"+curnet+"_"+name 
sqlscript += "(TheTime DOUBLE"
for (private x = 0, x < numrows(chanPrefixes), x++)
   sqlscript += "," + chanprefixes[x] + "_" + curnet + "_" + device + " DOUBLE"
endfor
sqlscript += ") ENGINE = InnoDB;"

Much neater, and if you decide all the sudden that you want the device first then curnet, or something, you only have to change it in one place.

[Andreschrosa]

Will give it a try, I thought maybe the '\' stuff is not good for so much lines, it works on smaller strings however.

[AzeoTech]

Possibly. As I said, I never use it. I find it much clearer to concatenate.

[Andreschrosa]

Worked alright! Thanks again!