Announcement

Collapse
No announcement yet.

Embedding SQL Loops

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

  • Embedding SQL Loops

    Hi I'm trying to loop through a SELECT and then build-up another tyable with INSERT per one record out of the SELECT statement:

    I gets stuck with the INSERT command with: Field/Recor Buffer error: (HYT00 in SQLCommand INSERT ...........

    Code:
    Function BuildAllocTable() As Long

    Local AllocRec As ALLOCRECORD
    Local dwWeeks As Dword
    Local dwWeek As Dword
    Local dwYear As Dword
    Local dblMHWeeks As Double
    Local strSQLD As String
    Local strSQLS As String
    Local strSQLI As String
    Local strTemp As String
    Local I, J As Integer

    strSQLD = "DELETE FROM x_allocations"

    strSQLS = "SELECT Seq_User, Seq_Project, Seq_Request, YEAR(x_orders.Startdate), WEEK(x_orders.Startdate), "
    strSQLS = strSQLS & "YEAR(x_orders.Duedate), WEEK(x_orders.Duedate), Manhours "
    strSQLS = strSQLS & "FROM x_requests, x_orders "
    strSQLS = strSQLS & "WHERE x_requests.Sequence = x_orders.Seq_Request AND "
    strSQLS = strSQLS & "NOT ISNULL(x_orders.Startdate) AND NOT ISNULL(x_orders.Duedate) AND "
    strSQLS = strSQLS & "Manhours <> 0 "
    strSQLS = strSQLS & "ORDER BY Seq_User, Seq_Project, Seq_Request, x_orders.Startdate;"

    strSQLI = "INSERT INTO x_allocations (Seq_User, Seq_Project, Seq_Request, Year, Week, Manhours) VALUES("

    SQL_Stmt %SQL_STMT_IMMEDIATE, strSQLD
    SQLCheckAndClose(SQL_State, strSQLD)

    SQL_Stmt %SQL_STMT_IMMEDIATE, strSQLS
    SQL_Fetch %NEXT_ROW
    While Not SQL_EOD
    AllocRec.dwUser = SQL_ResColSInt(1)
    AllocRec.dwProj = SQL_ResColSInt(2)
    AllocRec.dwReq = SQL_ResColSInt(3)
    AllocRec.dwSY = SQL_ResColSInt(4)
    AllocRec.dwSW = SQL_ResColSInt(5)
    AllocRec.dwDY = SQL_ResColSInt(6)
    AllocRec.dwDW = SQL_ResColSInt(7)
    AllocRec.dwMH = SQL_ResColSInt(8)

    'Calculate number of weeks (assuming 52 weeks per year)
    If AllocRec.dwDY > AllocRec.dwSY Then
    dwWeeks = (AllocRec.dwDY - AllocRec.dwSY) * 52
    dwWeeks = (dwWeeks - AllocRec.dwSW) + AllocRec.dwDW + 1

    Else
    dwWeeks = AllocRec.dwDW - AllocRec.dwSW + 1
    End If
    dblMHWeeks = Round(AllocRec.dwMH / dwWeeks , 2)
    dwYear = AllocRec.dwSY
    dwWeek = AllocRec.dwSW
    For i = 1 To dwWeeks
    dwWeek = dwWeek + 1
    If dwWeek > 52 Then
    dwWeek = 1
    dwYear = dwYear + 1
    End If
    strTemp = Str$(AllocRec.dwUser) & " ," & Str$(AllocRec.dwProj) & " ," & Str$(AllocRec.dwReq) & " ," & Str$(dwYear) & " ," & Str$(dwWeek) & ", " & Str$(dblMHWeeks)
    MsgBox strTemp

    strSQLI = strSQLI & Str$(AllocRec.dwUser) & ", " & Str$(AllocRec.dwProj) & ", " & Str$(AllocRec.dwReq) & ", "
    strSQLI = strSQLI & Str$(dwYear) & ", " & Str$(dwWeek) & ", " & Str$(dblMHWeeks) & ");"
    ' Execute strSQLI
    SQL_Stmt %SQL_STMT_IMMEDIATE, strSQLI
    SQLCheckAndClose(SQL_State, strSQLI)

    Next I
    SQL_Fetch %NEXT_ROW
    Wend
    SQLCheckAndClose(SQL_State, strSQLS)

    End Function
    IT Systems Management
    Engineer

  • #2
    HYT00 Timeout expired The query timeout period expired before the data source returned the result set. The timeout period is set through SQLSetStmtAttr, SQL_ATTR_QUERY_TIMEOUT.
    I have no idea why that particular error should be happening

    But the very first thing I'd try is editing my original post to insert Code tags
    ... since that would probably get more people to actually look at what I've tried so far.

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

    Comment


    • #3
      Frans --

      It looks like you are using SQL Tools... If you want to have two different SQL statements open at the same time you'll need to use two different "statement numbers". Think of it like having two files open FOR INPUT at the same time: you need to be able to tell them apart. In your code, the execution of the second statement is automatically closing the first.

      Read the section of the SQL Tools Help File called "Two of Everything: The Abbreviated and Verbose Functions" and it will explain everything.

      -- Eric Pearson, Perfect Sync, Inc.
      "Not my circus, not my monkeys."

      Comment


      • #4
        Changed coding according to Help file (just show you the differences):

        SQL_Statement 1, 1, %SQL_STMT_EXECUTE, strSQLS
        SQL_Fetch %NEXT_ROW
        While Not SQL_EOD
        ....
        ....
        SQL_Statement 1, 2, %SQL_STMT_EXECUTE, strSQLI
        SQLCheckAndClose(SQL_State, strSQLI)
        .......
        SQL_UseDBStmt 1, 1
        SQL_Fetch %NEXT_ROW
        Wend

        Still getting: Unknown SQL Error 0036 in SQLCommand SELECT and
        Unknown SQL Error 0034 in SQLCommand INSERT
        IT Systems Management
        Engineer

        Comment


        • #5
          Progression but still not good...

          Changed the coding now like:

          SQL_UseDBStmt 1, 1
          SQL_Stmt %SQL_STMT_IMMEDIATE, strSQLS
          SQL_Fetch %NEXT_ROW
          While Not SQL_EOD
          .....
          .....
          For I ....
          ........
          SQL_UseDBStmt 1, 2
          SQL_Stmt %SQL_STMT_IMMEDIATE, strSQLI
          SQLCheckAndClose(SQL_State, strSQLI)

          Next I
          SQL_UseDBStmt 1, 1
          SQL_Fetch %NEXT_ROW
          Wend
          ......


          The coding goes better, the select goes well, the first INSERT goes well but then the program stuks with 23000 error (Unique constraints). The table has no unique indexes. The error message builds up the buffer with the first INSERT en then parts of the new statements....
          IT Systems Management
          Engineer

          Comment


          • #6
            Frans --

            [Added: This message is based on your second-to-last post. You apparently posted again while I was composing my response.]

            If you want to have two statements open at the same time, all of the various SQL Tools functions will need to specify a statement number, not just SQL_Statement(). (SQL_UseStmtDB is not necessary, and in fact confuses the issue.)

            If I understand what you are trying to do, I think you'll need something like this...

            Code:
            SQL_Statement   1, 1, %SQL_STMT_IMMEDIATE, strSQLS
             
            Do  'Get the rows from the "outer" statement one at a time
              
                SQL_FetchResult 1, 1, %NEXT_ROW
                IF SQL_EndOfData(1,1) Then Exit Loop
                '(remember to add error checking here too)
              
                'Construct your second SQL statment based on one row from the first query
                strSQLI = SQL_ResultColumn*(1,1,x) + whatever
              
                SQL_Statement 1, 2, %SQL_STMT_IMMEDIATE, strSQLI
             
                Do  'Get the results from the second statement
                    SQL_FetchResult 1, 2, %NEXT_ROW
                    IF SQL_EndOfData(1,2) Then Exit Loop
                    '(remember to add error checking here too)
                    ....
                    'process a row from the second statement
                Loop
              
            Loop
            Remember too... To retrieve data from the statements you will need to use the SQL_ResultColumn*(1,x) functions, not the abbreviated SQL_ResCol* functions. If you need to have two statements open at the same time, every function will need to specify which statement it applies to. Think of it like having two files open FOR INPUT at the same time. If you didn't specify #1 or #2, how could PB know which file to INPUT from?

            Without knowing what your SQLCheckAndClose function does it's hard to speculate, but there's no reason to close a statement or database at that point in your code so I don't think it belongs there.

            -- Eric Pearson, Perfect Sync, Inc
            Last edited by Eric Pearson; 30 Apr 2008, 06:53 PM. Reason: code beautification
            "Not my circus, not my monkeys."

            Comment


            • #7
              Thanks Eric,

              This works fine.
              IT Systems Management
              Engineer

              Comment

              Working...
              X