SQL Data

[atlanta1213]

I have looked through several of the samples and tutorials relating to reading and writing data to a MS SQL database that are found on this site but I am somewhat overwhelmed by the choices and not sure which way to go.

I have a script that I was originally begun in literally the last century to monitor POP, SMTP, HTTP and FTP services in our organization.  I store all the configuration values that I call during the script execution in a couple of INI files.  That INI data lets me know the specifics for connecting to mail servers, web pages, FTP servers etc.  Settings like port numbers, usernames, passwords IP and DNS addresses and so forth.

I am wanting to change from INI files to a MS SQL 2008 db for storing my config data.  I have created the necessary tables and now I have to determine the best way to read that data at specific points in the script and assign those queried values to a WB variable for further script processing and then depending on the results of that process, write/update a value(s) in the same SQL db table (things like status etc.). One of the machines that the compiled WBT script runs on is part of the Amazon EC2 environment and so it can only connect to the SQL server on port 1433, not sure if that limits my choices but thought I should mention it.  I have an ODBC DSN for the SQL server configured on that remote server and it can communicate with the SQL server fine using that method.

I am not sure whether to use ODBC, ADO or OLEDB. As I mentioned earlier I am feeling very overwhelmed by the number of choices and I am just hoping that someone can point me to the proper code samples to go by which would be most appreciated.

[JTaylor]

ADO is what you would be using for working with the data.   ODBC and OLEDB relate to the Connection and Provider.   They are not 3 mutually exclusive things.  Depending on your MSSQL Version you may need to tweak the Provider information.

Here is a starting point.

Code (winbatch) Select Expand

 

  Home_Path = DirScript()
  DirChange(Home_Path)

  myConn = ObjectOpen('ADODB.Connection')
  myRS = ObjectOpen('ADODB.Recordset')
  myRS.CursorLocation = 3
  myConn.Provider = "SQLOLEDB.1"
  myConn.ConnectionString = "Provider=SQLOLEDB.1; Network Library=dbmssocn;Password=xxxxxxxxxxxx;User ID=xxxxxxxxxxxx;Initial Catalog=XXdatabaseXX;Data Source=XXdb_serverXX;"
  myConn.Open
  If myConn.State == 0 Then
    Message("Port is Blocked","The most likely reason you are seeing this message is that Port 1433 is being blocked by your firewall or you are not connected to the Internet. ")
    Return 9
  EndIf
  adoConst = ObjectConstantsGet(myConn)

  ;#####################################################
  ;EXAMPLE 1
  ;#####################################################
  SQLText = "select * from pn_notify where pn_location = 'Main' and pn_domain = 'my.com' "
  myRS.Open (SQLText, myConn, adoConst.adOpenStatic, adoConst.adLockReadOnly, adoConst.adCmdText)
  If myRS.EOF == @FALSE Then
    im_list = StrReplace(myRS.GetString(),@CR,@CRLF)
message("HI",im_list)
  Else
    Message("Note","No records found.")
  EndIf
  myRS.Close


  ;#####################################################
  ;EXAMPLE 2
  ;#####################################################
  SQLText = "select * from pn_notify where pn_location = 'Main' and pn_domain = 'my.com' "
  myRS.Open (SQLText, myConn, adoConst.adOpenStatic, adoConst.adLockReadOnly, adoConst.adCmdText)
  While myRS.EOF == @FALSE
    isbn            = myRS.Fields("isbn").Value
    imode           = myRS.Fields("imode").Value
    title           = myRS.Fields("title").Value
    author          = myRS.Fields("author").Value
message(isbn,title)
    myRS.MoveNext
  EndWhile
  myRS.Close

  myConn.Close


[atlanta1213]

Sorry I meant ODBC Extender not ODBC but thanks for posting the code that is exactly what I need to get started.