• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • Last Modified:

VBS script with textcopy command

Hi Experts,
I need following script using VBS.

1. Read values on database db01.tbl01.id01 and db01.tbl01.img01
2. I want to create sub-folder id01\sup_doc\ and need to store img01.jpg file on that directory.

I tried with attached dos script, but it seems I can't get multiple records using textcopy command on dos. (AppID is hard coded, and I need to loop multiple AppIDs and should create folders/files accordingly)
set PATH="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"<wbr ></wbr><wbr ></wbr><wbr ></wbr>;%PATH%
set DPath="D:\temp"

set AppIDD="1234567"

IF NOT EXIST %DPath%\%AppIDD% md %DPath%\%AppIDD%\supp_doc


textcopy.exe /S server01 /U user01 /P pwd01 /D db01 /T tbl01 /C field01 /W "where id=%AppIDD%" /Z /O /F %DPath%\\%AppIDD%\supp_doc\img01.jpg

PAUSE

Open in new window


I'm not much good in VBS, but I know we can connect to database and could get data to a record set and could take ids to a array and could create folder/images one by one.
 Please help me to create similar VBS script.
0
Dushan De Silva
Asked:
Dushan De Silva
  • 4
  • 2
1 Solution
 
Robert SchuttSoftware EngineerCommented:
I'm not sure if you wanted to use textcopy.exe from the vbs script but you can do it like this:
Option Explicit

Const C_DPath = "D:\temp"
Const C_IMG_NAME = "img01.jpg"

Const C_SRV = "server01"
Const C_DTB = "db01"
Const C_USR = "user01"
Const C_PWD = "pwd01"

Const C_TBL = "tbl01"
Const C_FLD_ID = "id01"
Const C_FLD_IMG = "img01"

Dim oConn, sAppIDD, sSQL, oRst, oFldImg, f, oFSO ' , oFile, n

Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & C_SRV & ";Initial Database=" & C_DTB & ";User ID=" & C_USR & ";Password=" & C_PWD & ";"
oConn.Open

For Each sAppIDD In Array("1234567", "1234568", "1234569")
	sSQL = "SELECT " & C_FLD_IMG & " FROM " & C_TBL & " WHERE " & C_FLD_ID & " = " & sAppIDD
	Set oRst = oConn.Execute(sSQL)

	While Not oRst.EOF
		oFldImg = oRst.Fields(0).Value
		If Not IsNull(oFldImg) Then
			SaveFile
		End If
		oRst.MoveNext
	Wend
	oRst.Close
	Set oRst = Nothing
Next
oConn.close
Set oConn = Nothing


Sub SaveFile()
	Set oFSO = CreateObject("Scripting.FileSystemObject")
	If Not oFSO.FolderExists(C_DPath & "\" & sAppIDD) Then
		oFSO.CreateFolder C_DPath & "\" & sAppIDD
		oFSO.CreateFolder C_DPath & "\" & sAppIDD & "\supp_doc"
	End If
	' save file without external object, slower
	'Set oFile = oFSO.CreateTextFile(C_IMG_NAME, True)
	'For n = 1 to LenB(oFldImg)
	'	oFile.Write Chr(AscB(MidB(oLOB, n, 1)))
	'Next
	'oFile.Close
	'Set oFile = Nothing
	SaveBinaryData C_DPath & "\" & sAppIDD & "\supp_doc\" & C_IMG_NAME, oFldImg
	Set oFSO = Nothing
End Sub

' faster file save, from http://www.motobit.com/tips/detpg_read-write-binary-files/
Function SaveBinaryData(FileName, ByteArray)
	Const adTypeBinary = 1
	Const adSaveCreateOverWrite = 2

	'Create Stream object
	Dim BinaryStream
	Set BinaryStream = CreateObject("ADODB.Stream")

	'Specify stream type - we want To save binary data.
	BinaryStream.Type = adTypeBinary

	'Open the stream And write binary data To the object
	BinaryStream.Open
	BinaryStream.Write ByteArray

	'Save binary data To disk
	BinaryStream.SaveToFile FileName, adSaveCreateOverWrite

	BinaryStream.Close
	Set BinaryStream = Nothing
End Function

Open in new window

EDIT: replaced my local db settings with examples given.
0
 
Dushan De SilvaTechnology ArchitectAuthor Commented:
Yeah Thanks a lot  robert_schutt!
I should be able to manage within vb without using external textcopy command.

I tried the code which you provided and it says following error.
------------------------------------------------------------------------------------------
Script   : D:\test2.vbs
Line     : 28
Error    : Invalid object name 'tbl01'.
Code    : 80040E37
Source :  Microsoft OLE DB Provider for SQL Server
------------------------------------------------------------------------------------------

tbl01 is exists on the database and I tried and can't find exact reason.. Please help me..
0
 
Dushan De SilvaTechnology ArchitectAuthor Commented:
Original developer's poor naming for DB column fields as "id", "file"....
Above error is coming for
-----------------------------------------------------------------------------------------------
sSQL = "SELECT [file] FROM tbl01 WHERE [id] = " & sAppIDD
-----------------------------------------------------------------------------------------------

and below error is coming for ....
-----------------------------------------------------------------------------------------------
sSQL = "SELECT file FROM tbl01 WHERE [id] = " & sAppIDD
-----------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------
Script   : D:\test2.vbs
Line     : 28
Error    : Incorrect syntax near the keyword 'file'.
Code    : 80040E14
Source :  Microsoft OLE DB Provider for SQL Server
------------------------------------------------------------------------------------------
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Dushan De SilvaTechnology ArchitectAuthor Commented:
it works I have to use
-----------------------------------------------------------------------------------------------
sSQL = "SELECT [file] FROM db01.dbo.tbl01 WHERE [id] = " & sAppIDD
-----------------------------------------------------------------------------------------------
0
 
Dushan De SilvaTechnology ArchitectAuthor Commented:
Thanks a lot!
0
 
Robert SchuttSoftware EngineerCommented:
Sorry I was out this morning. Great that you got it to work!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now