Javascript SQL statement

OL Connect Professional
1

Still struggling with a lot of SQL Deadlocks when using the standard component “Database Query”, sometimes I get 50-100 within 15 minutes.

Does anybody have an simpel example of these 3 types of statements using af javascript example ?
Insert, Update, Insert/Update

I always have the database connection string in a global variable like %global.DSN

I know I have to use the ADODB.Connection string to do itin the script.
But if somebody have some examples, I would really appreciate it.
Thanks in advance.
Klaus

2

Here is a sample script that uses a SELECT statement to extract the contents of a table to a CSV file.

var connect, sql, resultSet;

connect = new ActiveXObject("ADODB.Connection");
connect.ConnectionString =Watch.GetVariable("golbal.DSN");

try{
  connect.Open();
  sql="SELECT * FROM Customers";
  resultSet = connect.Execute(sql);

  exportRecordsToCSV(resultSet,"%t\output.csv");

} finally {
  resultSet.Close()
  connect.Close()
}


function exportRecordsToCSV(resultSet,csvFile){
  var fso = new ActiveXObject("Scripting.FileSystemObject");
  var pth = Watch.ExpandString("%t\output.csv");
  var txt = fso.CreateTextFile(pth, true);
  var oneLine = "";
  var fldCount = resultSet.Fields.Count;

  resultSet.MoveFirst();
  while (!resultSet.eof) {
    for(var i=0; i<fldCount; i++){
      oneLine += '"' + resultSet.Fields(i) + '"';
      if(i<(fldCount-1)) oneLine += ",";
    }
    txt.WriteLine(oneLine);
    oneLine = "";
    resultSet.MoveNext();
  }

}

If you replace the SELECT ... statement with something like INSERT INTO CUSTOMERS (fld1,fld2,fld3) VALUES ("Field 1","Field 2","Field 3"),("Other Field 1","Other Field 2","Other Field 3") (and remove the exportRecordsToCSV statement that follows it), then you will be inserting two rows in the table.

The UPDATE statement follows a similar syntax, with an addition WHERE clause.

Hope that helps get you started.

3

Thanks will try it.
Jet what is was looking for.