SQL queries and the SQL Loader

ed_creed
10-22-2004, 10:02 AM
How can I access a .sql file with my program and pass the sql query in the file to my application so that I don't have to hard code the query into my code?

Also, how do I access the SQL loader?

thanks
Ed

00100b
10-22-2004, 10:14 AM
For the first question, you can read the file into a string and pass it through the Execute method of an ADO.Connection object or an ADO.Command object, or the Open method of an ADO.Recordset object.

For the second question, what are you referring with "SQL loader". That is a term that I am unfamiliar with and SQL Books On-Line for both versions 7 and 2000 don't contain a reference to that term.

rufen101
10-22-2004, 10:18 AM
I believe SQL LOADER is a module of Oracle DBMS.

ed_creed
10-22-2004, 10:53 AM
For the first question, you can read the file into a string and pass it through the Execute method of an ADO.Connection object or an ADO.Command object, or the Open method of an ADO.Recordset object.

Is that the only way...I had thought of that, (hadn't tried it yet) but I was wondering if that was the only way and/or the best way.


For the second question, what are you referring with "SQL loader". That is a term that I am unfamiliar with and SQL Books On-Line for both versions 7 and 2000 don't contain a reference to that term.

Well, my VB App is the front end and it's hitting an Oracle database, but for loading large files, we're using the SQLLDR.exe which is similar to (I believe) the Bulk Loader in MSSql. We're currently running it manually by DOS batch files. But I'd like to be able to fire it off within VB. I hope that's clear enough.

Thanks for your help.
Ed

00100b
10-22-2004, 10:57 AM
If the results of the queries that are defined in the .SQL files are to be used by the application, then yes, I believe that will be the way to go.

You could use the Shell() function or the ShellExecute API call to launch SQLLDR.EXE along with the command-line switches.

ed_creed
10-22-2004, 12:29 PM
Not to beat a dead horse, but what if the queries in the .sql files are to be used strictly to insert records into a table or update records in a table? Would you do it any different?

Where can I get more info on the Shell() function and command-line switches.

I'll check out the ShellExecute API link above.

I appreciate the help.
Ed

00100b
10-22-2004, 12:48 PM
The horse ain't dead yet. Yeah, I'd still probably read the contents of the file in and then execute them using the Execute method of a Connection object.

If Oracle has a command line program to execute scripts, you could use the Shell() function again, passing it the .sql file to execute.

The Shell() function is described in VB's help file. Type it in a code window and press the F1 key. Command-line switches/arguments are what you would pass the shelled program (in this case SQLLDR.EXE) as if you were typing the whole thing in at the command prompt.

Shell "notepad c:\foo.txt", vbNormal

Would launch NotePad opening foo.txt located in the root directory c in a normal window.
the "c:\foo.txt" is an example of a command-line argument.

Some programs that can be launched from a command prompt accept arguments using command-line switches, which are little flags that tell the program what the value being passed is supposed to represent.

ed_creed
10-25-2004, 01:25 PM
that's working...thanks for all of the info.

Ed

ed_creed
10-26-2004, 03:33 PM
okay...I've got another question.

this is my current process

INSHIS = "INSERT INTO PHYDB.PHYADDRESS_HISTORY (ID,ADDL,CONTRACTED_NAME,PRACTICE_NAME,ADDR1,ADDR2,ADDR3,CITY,ST,ZIP,P HONE,FAX,CTY_CODE,CONTACT,OFFICE_HOURS,EMAIL_ADDRESS,SPONSORING_PROVID ER,TWOPRACTICE_NAME,TWOADDR1,TWOADDR2,TWOADDR3,TWOCITY,TWOST,TWOZIP,TW OPHONE,TWOFAX,TWOCTY_CODE,TWOCONTACT,TWOOFFICE_HOURS,THREEPRACTICE_NAM E,THREEADDR1,THREEADDR2,THREEADDR3,THREECITY,THREEST,THREEZIP,THREEPHO NE,THREEFAX,THREECTY_CODE,THREECONTACT,THREEOFFICE_HOURS,MADDR1,MADDR2 ,DEPT_NAME,MCITY,MST,MZIP,MPHONE,MFAX,BILLING_AGENT,BA_PHONE,BA_FAX,TI N1,BADDR1,BADDR2,BCITY,BST,BZIP,BPHONE,BFAX,TIN2,TWOBADDR1,TWOBADDR2,T WOBCITY,TWOBST,TWOBZIP,TWOBPHONE,TWOBFAX,SITE_SURVEY_DATE,SITE_SURVEY_ SCORE,HADDR,HCITY,HST,HZIP,TWOADDRESSPRINT,THREEADDRESSPRINT,PROVNO,AD DDATE,USER_ID)" _
& "SELECT ID,ADDL,CONTRACTED_NAME,PRACTICE_NAME,ADDR1,ADDR2,ADDR3,CITY,ST,ZIP,PH ONE,FAX,CTY_CODE,CONTACT,OFFICE_HOURS,EMAIL_ADDRESS,SPONSORING_PROVIDE R,TWOPRACTICE_NAME,TWOADDR1,TWOADDR2,TWOADDR3,TWOCITY,TWOST,TWOZIP,TWO PHONE,TWOFAX,TWOCTY_CODE,TWOCONTACT,TWOOFFICE_HOURS,THREEPRACTICE_NAME ,THREEADDR1,THREEADDR2,THREEADDR3,THREECITY,THREEST,THREEZIP,THREEPHON E,THREEFAX,THREECTY_CODE,THREECONTACT,THREEOFFICE_HOURS,MADDR1,MADDR2, DEPT_NAME,MCITY,MST,MZIP,MPHONE,MFAX,BILLING_AGENT,BA_PHONE,BA_FAX,TIN 1,BADDR1,BADDR2,BCITY,BST,BZIP,BPHONE,BFAX,TIN2,TWOBADDR1,TWOBADDR2,TW OBCITY,TWOBST,TWOBZIP,TWOBPHONE,TWOBFAX,SITE_SURVEY_DATE,SITE_SURVEY_S CORE,HADDR,HCITY,HST,HZIP,TWOADDRESSPRINT,THREEADDRESSPRINT,PROVNO,SYS DATE, '" & uidlogin & "' FROM PHYDB.PHYADDRESS WHERE PROVNO = '" & PHYID & "'"

'INSERT INTO HISTORY
With cmd
Set .ActiveConnection = con
.CommandType = adCmdText
.CommandText = INSHIS
End With

With rs
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.Open cmd
End With


But I'd like to store this query in a .sql file and read from the file when needed. However, due to the length of the string, I'm having difficulty.

My first question is do I need to enclose the string in " in the sql file?

Also, do I need to split the string in the sql file because it doesn't seem to like it when I don't. If I do need to split it, what's the syntax for something like that.

This is what I'm using to read from the file


Open "N:\DbaseMgmt\queries\insaddhist.sql" For Input As 1
Do While Not EOF(1)
Line Input #1, TEMP

STRTEXT = STRTEXT & TEMP
Loop
Close 1
INSHIS = STRTEXT


Thanks again for all your help.
Ed

00100b
10-27-2004, 06:07 AM
Write the query string out to the file as one long string (without any line feed or carriage returns embedded in the string). Then instead of looping through the file reading each line, just read the entire contents into a string variable. You do not need to include the quotation marks around the string when saving it to a file.

ed_creed
10-27-2004, 09:17 AM
my querystring in the sql file
INSERT INTO PHYDB.PHYADDRESS_HISTORY (ID,ADDL,CONTRACTED_NAME,PRACTICE_NAME,ADDR1,ADDR2,ADDR3,CITY,ST,ZIP,P HONE,FAX,CTY_CODE,CONTACT,OFFICE_HOURS,EMAIL_ADDRESS,SPONSORING_PROVID ER,TWOPRACTICE_NAME,TWOADDR1,TWOADDR2,TWOADDR3,TWOCITY,TWOST,TWOZIP,TW OPHONE,TWOFAX,TWOCTY_CODE,TWOCONTACT,TWOOFFICE_HOURS,THREEPRACTICE_NAM E,THREEADDR1,THREEADDR2,THREEADDR3,THREECITY,THREEST,THREEZIP,THREEPHO NE,THREEFAX,THREECTY_CODE,THREECONTACT,THREEOFFICE_HOURS,MADDR1,MADDR2 ,DEPT_NAME,MCITY,MST,MZIP,MPHONE,MFAX,BILLING_AGENT,BA_PHONE,BA_FAX,TI N1,BADDR1,BADDR2,BCITY,BST,BZIP,BPHONE,BFAX,TIN2,TWOBADDR1,TWOBADDR2,T WOBCITY,TWOBST,TWOBZIP,TWOBPHONE,TWOBFAX,SITE_SURVEY_DATE,SITE_SURVEY_ SCORE,HADDR,HCITY,HST,HZIP,TWOADDRESSPRINT,THREEADDRESSPRINT,PROVNO,AD DDATE,USER_ID) SELECT ID,ADDL,CONTRACTED_NAME,PRACTICE_NAME,ADDR1,ADDR2,ADDR3,CITY,ST,ZIP,PH ONE,FAX,CTY_CODE,CONTACT,OFFICE_HOURS,EMAIL_ADDRESS,SPONSORING_PROVIDE R,TWOPRACTICE_NAME,TWOADDR1,TWOADDR2,TWOADDR3,TWOCITY,TWOST,TWOZIP,TWO PHONE,TWOFAX,TWOCTY_CODE,TWOCONTACT,TWOOFFICE_HOURS,THREEPRACTICE_NAME ,THREEADDR1,THREEADDR2,THREEADDR3,THREECITY,THREEST,THREEZIP,THREEPHON E,THREEFAX,THREECTY_CODE,THREECONTACT,THREEOFFICE_HOURS,MADDR1,MADDR2, DEPT_NAME,MCITY,MST,MZIP,MPHONE,MFAX,BILLING_AGENT,BA_PHONE,BA_FAX,TIN 1,BADDR1,BADDR2,BCITY,BST,BZIP,BPHONE,BFAX,TIN2,TWOBADDR1,TWOBADDR2,TW OBCITY,TWOBST,TWOBZIP,TWOBPHONE,TWOBFAX,SITE_SURVEY_DATE,SITE_SURVEY_S CORE,HADDR,HCITY,HST,HZIP,TWOADDRESSPRINT,THREEADDRESSPRINT,PROVNO,SYS DATE, '" & uidlogin & "' FROM PHYDB.PHYADDRESS WHERE PROVNO = '" & PHYID & "'

my code

Open "N:\DbaseMgmt\queries\insaddhist.sql" For Input As 1
Line Input #1, temp

Debug.Print temp

Close 1
INSHIS = temp
Debug.Print INSHIS

'INSERT INTO HISTORY
With cmd
Set .ActiveConnection = con
.CommandType = adCmdText
.CommandText = INSHIS
End With

With rs
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.Open cmd
End With


I did like you said with the query string, but when my code hits debug.print, I get the query string in two lines as below....is this just a display issue? Is there some magic cutoff at 1024 characters? My problem is that I don't get a record inserted in to my table...but I also don't get an error message.

INSERT INTO PHYDB.PHYADDRESS_HISTORY (ID,ADDL,CONTRACTED_NAME,PRACTICE_NAME,ADDR1,ADDR2,ADDR3,CITY,ST,ZIP,P HONE,FAX,CTY_CODE,CONTACT,OFFICE_HOURS,EMAIL_ADDRESS,SPONSORING_PROVID ER,TWOPRACTICE_NAME,TWOADDR1,TWOADDR2,TWOADDR3,TWOCITY,TWOST,TWOZIP,TW OPHONE,TWOFAX,TWOCTY_CODE,TWOCONTACT,TWOOFFICE_HOURS,THREEPRACTICE_NAM E,THREEADDR1,THREEADDR2,THREEADDR3,THREECITY,THREEST,THREEZIP,THREEPHO NE,THREEFAX,THREECTY_CODE,THREECONTACT,THREEOFFICE_HOURS,MADDR1,MADDR2 ,DEPT_NAME,MCITY,MST,MZIP,MPHONE,MFAX,BILLING_AGENT,BA_PHONE,BA_FAX,TI N1,BADDR1,BADDR2,BCITY,BST,BZIP,BPHONE,BFAX,TIN2,TWOBADDR1,TWOBADDR2,T WOBCITY,TWOBST,TWOBZIP,TWOBPHONE,TWOBFAX,SITE_SURVEY_DATE,SITE_SURVEY_ SCORE,HADDR,HCITY,HST,HZIP,TWOADDRESSPRINT,THREEADDRESSPRINT,PROVNO,AD DDATE,USER_ID) SELECT ID,ADDL,CONTRACTED_NAME,PRACTICE_NAME,ADDR1,ADDR2,ADDR3,CITY,ST,ZIP,PH ONE,FAX,CTY_CODE,CONTACT,OFFICE_HOURS,EMAIL_ADDRESS,SPONSORING_PROVIDE R,TWOPRACTICE_NAME,TWOADDR1,TWOADDR2,TWOADDR3,TWOCITY,TWOST,TWOZIP,TWO PHONE,TWOFAX,TWOCTY_CODE,TWOCONTACT,TWOOFFICE_HOURS,TH
REEPRACTICE_NAME,THREEADDR1,THREEADDR2,THREEADDR3,THREECITY,THREEST,TH REEZIP,THREEPHONE,THREEFAX,THREECTY_CODE,THREECONTACT,THREEOFFICE_HOUR S,MADDR1,MADDR2,DEPT_NAME,MCITY,MST,MZIP,MPHONE,MFAX,BILLING_AGENT,BA_ PHONE,BA_FAX,TIN1,BADDR1,BADDR2,BCITY,BST,BZIP,BPHONE,BFAX,TIN2,TWOBAD DR1,TWOBADDR2,TWOBCITY,TWOBST,TWOBZIP,TWOBPHONE,TWOBFAX,SITE_SURVEY_DA TE,SITE_SURVEY_SCORE,HADDR,HCITY,HST,HZIP,TWOADDRESSPRINT,THREEADDRESS PRINT,PROVNO,SYSDATE, '" & uidlogin & "' FROM PHYDB.PHYADDRESS WHERE PROVNO = '" & PHYID & "'

thanks
Ed

00100b
10-27-2004, 10:09 AM
The Immediate Window will automatically wrap for very long strings, but the value of the string variable itself should be intact.

What are the values of the variables that are being concatenated in at the time of execution?

ed_creed
10-27-2004, 10:54 AM
The Immediate Window will automatically wrap for very long strings, but the value of the string variable itself should be intact.

What are the values of the variables that are being concatenated in at the time of execution?

There are two variables, the uidlogin value is PHYDB and the phyid value is 592743688

This query worked when I hard coded it like
inshis = "insert into blah blah blah"
except that I wrapped the string.

I also ran this query in Oracle's sqlworksheet but substituted the above values in quotes and it inserted the record.

00100b
10-27-2004, 12:38 PM
Here is the issue, I believe.

You are saving the string out with the variable references to the file. Correct?

In this case, just passing the contents of the string once read from the file doesn't include the values of the variables.

You could use the Replace function, once read from the file, on the string and replace the variable names with the actual values. This is because those variable references at the point that you've read the file in is a literal string and not a variable reference.

So, you'd use Replace to replace the the literal string part of uidlogin with the actual value of the uidlogin variable. The same would apply to the other variable reference as well.

ed_creed
10-27-2004, 02:10 PM
Here is the issue, I believe.

You are correct...
After an hour of syntax hell, I added the following statement and all is well.STRTEXT = replace(temp, "uid", "'" & uidlogin & "'")
STRTEXT2 = replace(STRTEXT, "physid", "'" & PHYID & "'")
INSHIS = STRTEXT2

Thanks for all your time and effort. It is very appreciated.
Ed

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum