Concatenating strings

[Gene]

Having issues concatenating strings.  

function SQL_ADD_TEST(string SER_NO, string FIRST_HIGH, string FIRST_LOW, string SECOND_HIGH, string SECOND_LOW, string THIRD_HIGH, string THIRD_LOW)
   
   local dbase = db.Open("Daq351")
   
   local executeSTR = Format("insert into app01 (serialnumber, firsthigh, firstlow, secondhigh, secondlow, thirdhigh, thirdlow) values ('%s', '%s','%s','%s','%s','%s','%s')",SER_NO, FIRST_HIGH, FIRST_LOW, SECOND_HIGH, SECOND_LOW, THIRD_HIGH, THIRD_LOW)
      
   local inserts = db.Execute(dbase,executeSTR)
    
   db.Close(dbase)
   
return inserts

when trying to output to console with ? executeSTR, i get NaN.

I've also tried "insert into...." + value1 + ", " + value2 " but get an error saying it expected a number (in general, i can get the actual error again).

if I hard code the sql, this sequence works fine.

 

thanks in advance, gene

[AzeoTech]

OK, three problems:

1) "local" is the wrong declaration.  You want "private".  "local" is for class declarations and never used inside a function.
2) while you remembered to declare all your function parameters as strings, you forgot to declare executeSTR as a string.  So you need:

private string executeSTR = format(...)

This is why you get NaN().  DAQFactory is trying to convert your insert string into a number so it can store it in executeSTR.

3) I typically do not recommend constantly opening and closing a database.  Just open it once at startup and leave it open.  Close it only if you lose connectivity (which is rare).

Two notes:

1) db.execute() doesn't return anything so "inserts" will never have anything
2) I typically prefer to build up my SQL strings over several lines, if only to make it clearer.  So:
 

private string executeSTR = "INSERT INTO app01 (serialnumber, firsthigh, firstlow, secondhigh, secondlow, thirdhigh, thirdlow) values ("
executeSTR += ER_NO 
executeSTR += ",'" + FIRST_HIGH + "'"
executeSTR += ",'" +FIRST_LOW+ "'"
executeSTR += ",'" +SECOND_HIGH+ "'"
executeSTR += ",'" +SECOND_LOW+ "'"
executeSTR += ",'" +THIRD_HIGH+ "'"
executeSTR += ",'" +THIRD_LOW + "'"
executeSTR += ")"

 

[Gene]

Thank you, that corrected it, first try!

Working on proof of concept, thank you for the suggestions, will definitely be incorporated.

[AzeoTech]

Just another suggestion: wrap your DB calls in a function.  So, for example at a minimum (each function is a sequence):

function dbOpen(string dbsource)
   global dbase = db.open(dbsource)

function dbExecute(string sql)
   db.execute(dbase, sql)

function dbQueryToClass(string sql)
   private ret = db.queryToClass(dbase,sql)
   return(ret)

Then, instead of calling db.open(), db.execute() and db.queryToClass(), call the above functions.  I gave the minimum that they'd do, but you can then add error handling and debugging code to the above to further enhance it.  

The best solution however is to create a class:

class CDataBase
   local handle = 0
   function Open(string dbsource)
      handle = db.open(dbsource)
   endfunction

   function Execute(string sql)
      db.execute(handle, sql)
   endfunction

   function QueryToClass(string sql)
      private ret = db.queryToClass(handle,sql)
      return(ret)
   endfunction
endclass

Then instantiate a single instance in startup:

global dbase = new(CDataBase)

and call into it like this:

dbase.open("mydatasource")

or

dbase.Execute("INSERT INTO mytable (field1) values (3.423)")

Again, this allows you to eventually add features (like error handling and debugging code) as needed and encapsulates it into a single object.