SQL and Resource files

[Guest Close Jason Rogoz]

I'm running into a destination file write error. I have been told that resource files
may help solve this problem. I cannot figure out how to put my SQL statements
into the resource files though. Do i need to pass a bunch of parameters back
and forth and what will the syntax in the file look like

------------------

[Michael Mattias Member Find all posts View Profile Close Michael Mattias]

You can save any text as a resource, either in a string table or as a binary resource.

If you have a file of SQL statements you can even:

// sql.rc resource file

 sqlStmt  sql my_sql.txt

Of course, with binary (user-defined) resources, you are responsible for parsing the statement out of the resource data.

MCM

[Guest Close Jason Rogoz]

can you elaborate more on the resource table thing, i've looked at it in the samples
but it looks confusing and i'm not sure how the SQL will compile. I tried to run the rc.exe on
this code

STRINGTABLE
BEGIN
""SELECT tblOrder.Sponsor, count(tblOrder.Sponsor) " & _
"FROM tblOrder, tblStatusTrack, tblStatusCCAuth, tblStatusRefund, tblStatusCancel, tblStatusChargeBack " & _
"Where tblOrder.OrderNumber=tblStatusTrack.OrderNumber AND " & _
"tblOrder.OrderNumber=tblStatusCCAuth.OrderNumber AND " & _
"tblOrder.OrderNumber=tblStatusRefund.OrderNumber AND " & _
"tblOrder.OrderNumber=tblStatusCancel.OrderNumber AND " & _
"tblOrder.OrderNumber=tblStatusChargeBack.OrderNumber AND " & _
"tblStatusTrack.Status= '1' AND " & _
"tblStatusCancel.Status <> '1' AND " & _
"tblStatusRefund.Status <> '1' AND " & _
"tblStatusChargeBack.Status <> '1' AND " & _
"tblOrder.BillingCountry <> 'usa' AND " & _
"tblOrder.BillingCountry <> 'unitedstates' AND " & _
"tblOrder.BillingCountry <> 'canada' AND " & _
"tblOrder.Sponsor <> " & "'" & nothing & "'" & " " & _
"tblStatusTrack.StatusDate Between " & "{ts '" & startcurrentday & "'} " & " AND " & "{ts '" & endcurrentday & "'} Group By tblOrder.Sponsor ""
END

not luck. any ideas


------------------

[Edwin Knoppert Member Find all posts View Profile Close Edwin Knoppert]

Instead of converting SQL strings to a string table, because of it's work involved, i recommend saving the SQL statements in your project dir and make an entry in your resource like this:


SQL1 RCDATA "SQL1.TXT"
This adds it completely and retrieval is easy.
I could help you with that.


------------------
http://www.hellobasic.com

[Guest Close Jason Rogoz]

hhhhhmmmmmmm What this seems like it going to be more work that it needs to be
I am now trying to just store the SQL in a text file and just opening the file and reading it
the problem now is that do i need write the SQL statement in the text file
exactly how it would appear in PB ie utilizing all the strings around variable as such,
or do i just write the SQL exactly as the DB would understand it?

------------------

[Eric Pearson Member Find all posts View Profile Close Eric Pearson]

Jason --

If you put variable names in a text file, they will NOT be recognized as variables. In other words, if you do this:

SELECT * FROM MYTABLE WHERE MYFIELD = 'MYVAR'

...that is exactly what will be read from the disk file.

I would suggest "tokenizing" your disk files something like this:

SELECT * FROM MYTABLE WHERE MYFIELD = '|001|'

Read the string from the disk file, and do something like this:

REPLACE "|001|" WITH MyVar1$ IN SQLStmt$
REPLACE "|002|" WITH MyVar2$ IN SQLStmt$

...and so on. Of course you must choose tokens that will never appear in a real string, or the wrong thing will be replaced.

-- Eric


------------------
Perfect Sync Development Tools
Perfect Sync Web Site
Contact Us: mailto:[email protected][email protected]</A>

[This message has been edited by Eric Pearson (edited September 25, 2001).]

[Guest Close Jason Rogoz]

Well This is what my SQL stmt looked like in powerbasic before the error and it worked

SELECT tblOrder.Sponsor, count(tblOrder.Sponsor) FROM tblOrder, tblStatusTrack, tblStatusCCAuth, tblStatusRefund, tblStatusCancel, tblStatusChargeBack Where tblOrder.OrderNumber=tblStatusTrack.OrderNumber AND tblOrder.OrderNumber=tblStatusCCAuth.OrderNumber AND tblOrder.OrderNumber=tblStatusRefund.OrderNumber AND tblOrder.OrderNumber=tblStatusCancel.OrderNumber AND tblOrder.OrderNumber=tblStatusChargeBack.OrderNumber AND tblStatusTrack.Status=" & "'" & one & "'" & " and " & "'" & one & "'" & " AND tblStatusCancel.Status <> " & "'" & one & "'" & " AND tblStatusRefund.Status <> " & "'" & one & "'" & " AND tblStatusChargeBack.Status <> " & "'" & one & "'" & " AND tblOrder.BillingCountry <> " & "'" & usa & "'" & " AND tblOrder.BillingCountry <> " & "'" & unitedstates & "'" & " AND tblOrder.BillingCountry <> " & "'" & canada & "'" & " AND tblOrder.Sponsor <> " & "'" & nothing & "'" & " AND tblStatusTrack.StatusDate Between " & "{ts '" & startcurrentday & "'} " & " AND " & "{ts '" & endcurrentday & "'} Group By tblOrder.Sponsor

It's pretty long and ugly but it worked. I figure if i put that exact syntax in the file and then
read it in it should sit where the variable was and it would act like it was in the code in the first place.
Is that correct?

------------------

[Eric Pearson Member Find all posts View Profile Close Eric Pearson]

No.

PowerBASIC is a compiler, not an interpreter.

Think of it this way... When you compile your program, the variable names "go away". PB translates everything in your program (except for literal strings) into machine code, so "words" like MyVar$ and your variable called "one" are not part of the final EXE. So if you read in a string from a disk file at runtime, the program will see nothing but a string. It can't possibly be treated as "commands". It won't recognize & or + in the string as meaning "add these two things together", or anything else. & and + will be the strings & and + and nothing else.

The only thing that understands PB source code is the compiler itself. So unless you #INCLUDE the file when you compile your program, variable names and BASIC syntax are meaningless. (And no, using #INCLUDE would not solve your problem because it would add literal strings to your program, and that is what you are trying to avoid.)

HTH.

-- Eric


------------------
Perfect Sync Development Tools
Perfect Sync Web Site
Contact Us: mailto:[email protected][email protected]</A>



[This message has been edited by Eric Pearson (edited September 25, 2001).]

[Guest Close Jason Rogoz]

So what you're saying is that the token thing that you suggested is
probably the way to go then right? Do you have any idea how to incorporate the time stamp thing with the token
then.



------------------

[Eric Pearson Member Find all posts View Profile Close Eric Pearson]

If I understand your question, I am suggesting something like this:

LINE INPUT #1, SQLStmt$  'read string from disk file
REPLACE "|001|" WITH startcurrentday IN SQLStmt$
REPLACE "|002|" WITH endcurrentday   IN SQLStmt$
REPLACE "|003|" WITH one             IN SQLStmt$
SQL_Stmt %IMMEDIATE, SQL_Stmt$

You would also need to create tokens for "unitedstates" and any other runtime variable, and insert them into the appropriate place(s) in the string.

-- Eric


------------------
Perfect Sync Development Tools
Perfect Sync Web Site
Contact Us: mailto:[email protected][email protected]</A>



[This message has been edited by Eric Pearson (edited September 25, 2001).]

[Guest Close Jason Rogoz]

This looks like it could get ugly down the road. Are failiar with passing parameters
to Access from powerbasic and storing the query in access itself. If this is possible
then it would save me a lot messy code and i could store the queries in the DB

------------------

[Eric Pearson Member Find all posts View Profile Close Eric Pearson]

Jason --

That would involve using "stored procedures" and "bound statment input parameters" (see the SQL Tools Help File) and it would be much more complex than loading/processing strings from a text file.

-- Eric

P.S. To Lurkers...

I'd like to emphasize that Jason's basic problem has nothing to do with SQL or SQL Tools, except that his program requires the use of many long strings. We have theorized that moving some of those strings to an external file will get rid of his "destination file write error" problem, but we are not certain of that.


------------------
Perfect Sync Development Tools
Perfect Sync Web Site
Contact Us: mailto:[email protected][email protected]</A>

[This message has been edited by Eric Pearson (edited September 25, 2001).]

[Guest Close Jason Rogoz]

So i guess i'm stuck with using this token method. Is there any plans to correct this
issue or would using a version other than 2.0 help eliminate this limitation of the compiler.

The only this that i'm uncertain with is what kind of syntax goes into the
text file whether is has powerbasic specific context or if it is straight SQL


------------------

[Eric Pearson Member Find all posts View Profile Close Eric Pearson]

Your text file would contain very long lines of text, portions of which would look like this:

AND tblOrder.OrderNumber=tblStatusChargeBack.OrderNumber AND tblStatusTrack.Status='|003|' AND

You would read the string(s) from the text file(s), perform the REPLACE operations (to change things like |003| into the runtime value of your "one" variable) and then submit the string to the database as a SQL statement.

Remember, most of the things like tblOrder.OrderNumber in your strings are not PowerBASIC variables they are parts of the SQL syntax that use a syntax that is similar to PowerBASIC. For example in that statement, tblOrder.OrderNumber isn't a PowerBASIC UDT variable, it means "the field called OrderNumber in the table called tblOrder". Things like that can be stored "literally" in the text file. The only thing you need to tokenize/replace is "dynamic" (runtime) data that can't be stored in the text file because the value is not known ahead of time.

-- Eric


------------------
Perfect Sync Development Tools
Perfect Sync Web Site
Contact Us: mailto:[email protected][email protected]</A>



[This message has been edited by Eric Pearson (edited September 25, 2001).]

[Guest Close Jason Rogoz]

ok i am starting to pull values from the DB only they are garbage values like squares commas, brackets
It looks like ASCII codes are being pulled. Would this be the same

select Ordernumber from tblOrder where Status = '|001|' 'this is in text file


REPLACE "|001|" with "1" in temp 'is this the same as the following


dim zz as string
zz="1"
REPLACE "|001|" with one in temp


------------------

[Eric Pearson Member Find all posts View Profile Close Eric Pearson]

Close...

I think you meant to type...

REPLACE "|001|" with zz in temp

But I don't see the point of doing that. Why not just put this in the text file:

select Ordernumber from tblOrder where Status = '1' AND

Or will the value of the "one" variable change sometimes?

Any "fixed" values that are known ahead of time can be put in the text file. You only need to tokenize and replace values that are not known ahead of time, such as "today's date". You would need to calculate values like that when your program is run, and insert them to replace the appropriate tokens.

-- Eric


------------------
Perfect Sync Development Tools
Perfect Sync Web Site
Contact Us: mailto:[email protected][email protected]</A>



[This message has been edited by Eric Pearson (edited September 25, 2001).]

[Michael Mattias Member Find all posts View Profile Close Michael Mattias]

Um, why bother with REPLACE and the like with string tokens?

Sounds like all those tokens are parameters, and you could just store the question marks, prepare the statements and bind the parameters.

MCM

[Michael Mattias Member Find all posts View Profile Close Michael Mattias]

Um, going back to the first message...

I'm running into a destination file write error. I have been told that resource files
may help solve this problem

Who told you that? That makes no sense at all.

I hope you've checked the more common causes of this error:
1. Program still running, or DLL not unloaded due to abnormal termination
2. Using "#COMPILE path\file," where "path" is invalid.


(Not that a discussion of resource-based SQL isn't pretty cool, but...)


MCM

[Guest Close Jason Rogoz]

the replace method is what seems to working fine now. I was able to
store the SQL stmt in a text file and then read the text file in and replace the
variables that change throughout the program. This is even better as now i can
test my queries in access and directly copy and paste them into the file with ease
where before i had to mak everything strings and if i left out a comma or quote i could waste
valuable time trying to debug that problem. Anyways there are probably
tons of ways to do this but this is the easiest and quickest way right now.
These compiler limitations are quite frustrating which almost make sense
to do a switch to ASP.

Thanks for the help guys


------------------