Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

OLE COM ADO CDO ADSI LDAP

	ADO DAO
	Collections
	Constants
	Crystal Reports
	DSOFILE
	Errors
	ImageMagick
	IMAPI
	LogParser
	Lotus Notes
	MDAC
	Netscape
	OLE and Outlook
	OLE TUTORIAL
	OLE with Access
	OLE with Acrobat Viewer
	OLE with Excel
	OLE with Google Earth
	OLE with MSIE
	OLE with PowerPoint
	OLE with Word
	OLE with XML
	OpenOffice
	PDF Conversion
	RegExp
	Samples from Users
	SAPI
	Shell
	SQLite
	Task Scheduler
	Textract
	UDFs
	WIA
	WIL Type Viewer
	WindowsInstaller
	WinHttpRequest
	Winsock
	WSH
	Zip

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

A Small WinBatch Demo that Illustrates Inserting Data into a SQL Server 2000 (pubs database)

Keywords:   insert SQL Server 2000

I'm a non-programming type database administrator, but I'm hooked on WinBatch!

I am indebted to Stan Littlefield for the examples he posts on the WinBatch support/BBS site - Stan, you rock!

This example:

Uses oledb connection to pubs database.
Illustrates insert via TSQL Insert statement and via stored procedure.
SQL code to set up SQL Server to support this demo is included.

This demonstration app illustrates inserting into SQL Server 2000 using two methods:

Insert statement

Stored procedure execution

To run this example:

Unzip all the files into a single folder - such as c:\sqltest02
Using SQL Server 2000 Query Analyzer pointed to a NON PRODUCTION SQL SERVER:
* Log in as a user that can add logins, create tables in the pubs database, and manage db users and roles. This would either be the 'sa' account or some other account set up with the proper system and db roles.
* Select the pubs database that comes with SQL Server 2000.
* Execute the Create_Employee_APP_table.sql file provided.
* This sql script will set up everything needed in SQL Server to support this demonstration.

Edit the SQLTEST02.WBT file so that the correct server name is specified. This is the line that reads:

		src   = "m-beadles"			   ;This is the name of the SQL Server we point to - can also use IP address.

Save the file if necessary (if not using WinBatch Studio)
Run SQLTEST02.WBT

Notes:

The demonstration assumes that you have installed SQL Server 2000 and the pubs sample database that comes with SQL Server.

As a dba, I should mention that the stored procedure method is the preferred approach for SQL Server. When executing stored procedures, there is less network traffic and SQL Server already has most of the database <---> client overhead taken care of.

The Winbatch code:

;	Notes on SQL Accounts:
;		If you have created a SQL login and have granted access to the db, ensure that permissions have been set up for the user.
;		If you are using Windows authentication into SQL Server, ensure that the account has been given proper access to the db.


BoxOpen("Test Script","Inserting into SQL Server")
BoxText("This sample program will do the following:%@crlf%%@crlf%%@TAB%1) Populate some variables to make a SQL DB Connection" )
TimeDelay(4)
BoxText("This sample program will do the following:%@crlf%%@crlf%%@TAB%2) Set Up the OLEDB Connection to SQL Server%@crlf%%@TAB%    using the variables")
TimeDelay(4)
BoxText("This sample program will do the following:%@crlf%%@crlf%%@TAB%3) Perform an INSERT statement to a pre-existing table%@crlf%%@TAB%    in the database")
TimeDelay(4)
BoxText("This sample program will do the following:%@crlf%%@crlf%%@TAB%4) Execute a pre-existing stored procedure%@crlf%%@TAB%    (the procedure also does an insert)")
TimeDelay(4)
BoxTitle("Starting ...")
BoxText("")
TimeDelay(1)




src   = "m-beadles"			   ;This is the name of the SQL Server we point to - can also use IP address.
uid = "pubsuser"					;If using NT authentication, you do not need uid - can delete this line
pwd = "pubsuser"					;If using NT authentication, you do not need pwd - can delete this line
catalog = "pubs"					;This sets the database we need to point to.
table = "employee_app"  		;Could be hard coded in the cSQL, below, rather than using a variable.

BoxTitle("Using these connection variables:")
BoxText("Server Name = %src% %crlf% User = %uid% %crlf% Password = %pwd% %crlf% Database = %catalog%")
TimeDelay(3)

cConn = 'DRIVER={SQL Server};SERVER=%src%;UID=%uid%;PWD=%pwd%;Catalog=%catalog%'
DB  = ObjectOpen("ADODB.Connection")
DB.Open(cConn)

BoxTitle("Using this OLE connection string:")
BoxText("%cConn%")
TimeDelay(3)


cSQL = "INSERT [%catalog%].[dbo].[%table%] VALUES('mbeadles', 'Insert Method', getdate());"
BoxTitle("SQL Insert Statment:")
BoxText("%cSQL%")
TimeDelay(3)
DB.Execute("%cSQL%") ;Insert





cSQL = "usp_ins_employee_app @emp_nt_id = 'mbeadles', @app_name = 'Stored Procedure Method', @app_launchtime = ''"
BoxTitle("SQL Stored Procedure (with parameters):")
BoxText("%cSQL%")
TimeDelay(3)
DB.Execute(cSQL) ;SP


DB.Close()
ObjectClose(DB)
BoxShut()


tget  = strcat( Dirget(),"db.Txt")

DB    = ObjectOpen("ADODB.Connection")  ;We are openning an ADO db connection Object
RS    = ObjectOpen("ADODB.Recordset")	 ;We are opening a record set object to hold the results of a select query.

cSQL = "select * from employee_app"
message(cSQL,"")


DB.Open(cConn)
RS   = DB.Execute(cSQL)

;create ASCII text file
fle  = FileOpen(tget,"WRITE")  ;open the text file for writing
cRc  = ""
sort = ""
f    = ""
;make Column Headings First line in text file
FLDS   = RS.Fields	   ;object recordset fields
nCount = FLDS.Count()   ;the count method  - how many columns
For i=0 to ( nCount-1 )
   nField = RS.Fields(i)
   cName= nField.Name
   cRc   = strcat(cRc,',"',cName,'"')
   sort  = strcat(sort,'',cName,"",@CRLF)
   f     = strcat(f,'',@CRLF)
Next

cRc = StrSub(cRc,2,-1)
FileWrite(fle,cRc)

Boxtext("Creating ASCII Input File")


; ///////////////////////////////////////////////////////
; I originally used the getstring() method here as it is
; 10-20 times faster than looping through the records, however
; the HTML template wants all data to be delimited with "" which
; getstring() does not do by default, so the resulting HTML file
; screws up when sorting by Dialcode
; ///////////////////////////////////////////////////////
RS.MoveFirst()

While ! RS.eof()
 cRc=""
   For i=0 to ( nCount-1 )
      nField = RS.Fields(i)
      If i == ( nCount-1 )
         cRc = strcat(cRc,'"',nField.Value,'"')
      Else
         cRc = strcat(cRc,'"',nField.Value,'",')
      Endif
   Next
   FileWrite(fle,cRc)
   RS.MoveNext()
EndWhile
FileClose(fle)

Drop(cRest)

RS.Close()
ObjectClose(RS)
DB.Close()
ObjectClose(DB)


Boxtext("Formatting HTML Template File")

fs = FileSize( "htm.tem" )
binbuf = binaryalloc( fs+1000 )                   ;file size of htm.tem + 1000 bytes
ret = BinaryRead( binbuf, "htm.tem" )             ;read the htm.tem file into the binbuf buffer and then act on the buffer as follows:
num = BinaryReplace( binbuf, "//file//", tget ,0) ;replace this placeholder with the contents of tget (the text file path)
num = BinaryReplace( binbuf, "//sort//", sort ,0) ;replace this placeholder with javascript function call - see above in the sort loop.
num = BinaryReplace( binbuf, "//f//", f ,0)		  ;replace this placeholder 
BinaryWrite( binbuf, "db.htm" )			  			  ;write the completed template into the html file
BinaryFree(binbuf)										  ;free up the buffer
BoxShut()													  ;shut down the message box

ShellExecute("db.htm","","",@NORMAL,"")
Exit

Other pieces:

sqltest02.zip

Article ID:   W15228

File Created: 2017:08:29:11:58:40

Last Updated: 2017:08:29:11:58:40