Embedding SQL Loops

[Frans Streur]

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

[Michael Mattias]

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

[Eric Pearson]

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.

[Frans Streur]

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

[Frans Streur]

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....

[Eric Pearson]

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...

SQL_Statement   1, [COLOR="Lime"]1[/COLOR], %SQL_STMT_IMMEDIATE, strSQLS
 
Do  'Get the rows from the "outer" statement one at a time
  
    SQL_FetchResult 1, [COLOR="Lime"]1[/COLOR], %NEXT_ROW
    IF SQL_EndOfData(1,[COLOR="Lime"]1[/COLOR]) 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,[COLOR="lime"]1[/COLOR],x) + whatever
  
    SQL_Statement 1, [COLOR="Red"]2[/COLOR], %SQL_STMT_IMMEDIATE, strSQLI
 
    Do  'Get the results from the second statement
        SQL_FetchResult 1, [COLOR="Red"]2[/COLOR], %NEXT_ROW
        IF SQL_EndOfData(1,[COLOR="Red"]2[/COLOR]) 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

[Frans Streur]

Thanks Eric,

This works fine.