Announcement

Collapse
No announcement yet.

SQL and Resource files

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL and Resource files

    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

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

  • #2
    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:
    Code:
    // 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
    Michael Mattias
    Tal Systems (retired)
    Port Washington WI USA
    [email protected]
    http://www.talsystems.com

    Comment


    • #3
      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


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

      Comment


      • #4
        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
        hellobasic

        Comment


        • #5
          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?

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

          Comment


          • #6
            Jason --

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

            Code:
            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:

            Code:
            SELECT * FROM MYTABLE WHERE MYFIELD = '|001|'
            Read the string from the disk file, and do something like this:

            Code:
            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).]
            "Not my circus, not my monkeys."

            Comment


            • #7
              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?

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

              Comment


              • #8
                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).]
                "Not my circus, not my monkeys."

                Comment


                • #9
                  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.



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

                  Comment


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

                    Code:
                    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).]
                    "Not my circus, not my monkeys."

                    Comment


                    • #11
                      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

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

                      Comment


                      • #12
                        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).]
                        "Not my circus, not my monkeys."

                        Comment


                        • #13
                          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


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

                          Comment


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

                            Code:
                            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).]
                            "Not my circus, not my monkeys."

                            Comment


                            • #15
                              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


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

                              Comment


                              • #16
                                Close...

                                I think you meant to type...

                                Code:
                                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:

                                Code:
                                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).]
                                "Not my circus, not my monkeys."

                                Comment


                                • #17
                                  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
                                  Tal Systems (retired)
                                  Port Washington WI USA
                                  [email protected]
                                  http://www.talsystems.com

                                  Comment


                                  • #18
                                    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


                                    Michael Mattias
                                    Tal Systems (retired)
                                    Port Washington WI USA
                                    [email protected]
                                    http://www.talsystems.com

                                    Comment


                                    • #19
                                      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


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

                                      Comment

                                      Working...
                                      X