November 7, 2006 at 1:31 pm
My name is Mike Trippett and I am a new hire at Omni Air International. My background is mostly in mainframe programming. I know very little about Visual Basic and just a little more about C and C++. However I do understand SQL.
My problem is this: I have a table on a SQL Server database that I need to load from a Microsoft folder. The folder contains JPEG files with the pictures of pilots. I need to read the folder, extract the name for each file along with the size, height and width and use that extracted data to load the SQL Server database. There are some values that would just be plugged into the database.
This would need to be a job that can be scheduled to run at a given time or on request.
I have not been able to find an example where Visual Basic allows the SQLs to INSERT using a variables (what are called a host variables in COBOL and ESQL/C).
I understand that C can use ESQL/C and that will allow host variables, but I am not sure of all of the steps to pre-compile, compile and run programs that use ESQL/C.
Is there a way to read the folder, extract the information from the files, put the information in variables and use those variables in an SQL in one program?
Does any have some coding or can you point me to a Web site where I can find out to do this?
I have read books and searched the internet for over a week and still cannot find all of the pieces.
Any help anyone can give me on this matter would be greatly appreciated>
Thanks,
Mike Trippett
November 7, 2006 at 11:44 pm
If you're using VB6, you need to look at the File System Object, which has the code necessary to:
1) Point a variable at the folder
2) List the files in the folder of type .jpg
3) For each file of that type, put the filename in a variable, and with a little research, it is probably possible to get the image width and height into variables as well
4) For each of those, create an INSERT statement which would put the data into your table (I'd use dynamic SQL)
Dim sSQL as string
sSQL = "INSERT INTO MyTable(FolderName, FileName, JPGSize, JPGHeight, JPGWidth) VALUES(" + sFolderName + "," + sFileName + "," + sJpgSize + "," + sJpgHeight + "," + sJpgWidth + ")"
Dim conn as new ADODB.Connection
conn.ConnectionString = "Provider=... InitialCatalog= etc
conn.Open
conn.Execute sSQL
conn.CLose
(obviously you would rework that into a loop)
in the above SQL you would define each of the s-variables as a string and load them from the FSO object.
Hope that's enough to steer you, Phil
November 8, 2006 at 2:27 am
good advice.....but it would be even better to put that SQL code into a Stored Procedure....better performance, better datatype checking, better security, better split of DB responsibility between front-end + back-end...
Also what's there isn't Dynamic SQL...it's in-line SQL (or pass-through) SQL. It only becomes Dynamic SQL if it's written as a concatenated string which is then EXEC'd inside a Stored Procedure
November 8, 2006 at 5:50 am
Regarding point 3, the scripting runtime's File object does not have anything for getting at a JPEG's height and width. You'll have to use .NET.
But because I needed this as well (very quickly), I did some research and found a neat java component for doing this at http://schmidt.devlib.org/image-info/. I tried it and it works like a charm. You can use it from the command line and pipe the results into a file followed by reading the file.
November 8, 2006 at 6:16 am
You can also use something similar to host variables instead of string concatenation, but the way to do it depends on the language you're using (VB 6 or VB.Net). This avoids having to worry about converting data types, and makes your SQL much more readable. Using just T-SQL:
declare
@TempId int
declare @TempText nchar(10)
set
@TempId = 99
set @TempText = 'hello'
INSERT
INTO [Tryout].[dbo].[TestTable]
([TempId]
,[TempText])
VALUES
(@TempId
,@TempText)
You can if you wish encapsulate the SQL into a stored procedure first as Andrew suggests. Either way, in VB.Net just add parameters to the SqlCommand's parameters collection before executing it. Not sure about VB6 sorry.
November 8, 2006 at 7:23 am
Thank you to everyone!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply