SQL query hangs from VB but not manually

waits77
04-14-2004, 07:28 AM
This query hangs when used by the program, but if I take the output from the "Debug.Print" and paste it into a SQL data manager, it works fine. The database is Pervasive.

Private Sub Load_Record()
On Error GoTo error1
intNoRecords = 0
With Adodc1
.RecordSource = strQuery ' execute the querry
' ########## Hangs Here ##########
.Refresh ' update the recordset
' ############################
intRowsProd = .Recordset.RecordCount ' see how many records (rows)
varProd = .Recordset.GetRows(intRowsProd) ' retrieve all of the records
End With
error1:
If Err.Number = 3219 Then ' if the database contains no matching records
intNoRecords = 1
Else
If Err.Number <> 0 Then
' use standard error message
MsgBox ("Error #: " & Str(Err.Number) & " " & Err.Description)
intNoRecords = 1
' jump out
Exit Sub
End If
End If
End Sub
'
'
strQuery = "SELECT T3.PARLOT_69, T3.COMLOT_69, T1.UDFKEY_10, T1.UDFREF_10, T4.SERIAL_68 " _
& "FROM " & Chr(34) & "Order Master" & Chr(34) & " T1 LEFT OUTER JOIN " _
& Chr(34) & "Lot Track Structure" & Chr(34) & " T3 ON T1.ORDER_10 = " _
& "T3.ORDNUM_69, " & Chr(34) & "Part Lot" & Chr(34) & " T4 WHERE " _
& "T1.LOTNUM_10 = T4.LOTNUM_68 AND T1.PRTNUM_10 IN " & strComponent _
& " AND T4.STK_68 = 'SUBST614' AND QTYOH_68 = '1' AND " & strTemp
' ##########
Debug.Print strQuery ' this runs if pasted into data manager
' ##########
Call Load_Record

MKoslof
04-14-2004, 08:02 AM
OK, for one, I have used Pervasive SQL a lot recently and I would advice against Data Controls. This is part of the problem. Switch over to standard ADO connections and recordsets...you will see a big difference. I never use data controls at this point.

What code is currently in Load_Record?

waits77
04-14-2004, 08:07 AM
Thanks, I'll look around and see if I can find some sample code that doesn't use the adodc control.

MKoslof
04-14-2004, 08:12 AM
For a Pervasive ADO connection string try the code below. As a test, (and a good way to learn how to connect via Pervasive) create a new UDL file (just create a standard text file and rename it to UDL). Then, double click the new UDL and walk through the steps to connect to your Pervasive database. When finished, open the UDL in textPad...it will provide the connection string for you.

Also, review this site: http://www.connectionStrings.com



Dim sSQL as string
Dim strCon as string
Dim cn as ADODB.connection
Dim rs as ADODB.recordset

Set cn = New ADODB.connection
Set rs = New ADODB.recordset

sSQL = "SELECT * FROM MainTable"

strCon = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=myDatabase"

cn.ConnectionString = strCon
cn.Open

rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText

'do your work

'remove to close the cn and rs objects and set their objects equal to nothing
'when finished working with them

waits77
04-14-2004, 08:21 AM
Thanks a lot! Especially for the tip on connection strings. I'll go take a whack at it now.

MKoslof
04-14-2004, 08:38 AM
Good luck :).

waits77
04-14-2004, 09:00 AM
At least I know why it hangs, sort of. All the other querys work fine and now I get an error message when I run the query in question (before it just hung).

Error #: -2147467259 [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager]Bookmark Error. Possible bad or out of date index.

Still doesn't explain why I can run this query from a Data Manager but not from VB. And I'm not sure what I need to fix in the database.

MKoslof,
Have you tried using the Pervasive driver instead of the Microsoft?

MKoslof
04-14-2004, 09:14 AM
No, only the Microsoft Driver..I don't have experience with the Pervasive Driver. Does this table have indexes on it? If so, how does this query effect the indexes, are these fields referenced?

waits77
04-15-2004, 07:31 AM
I've talked to our database administrator. He has checked all of the tables and assures me that everything is OK.

I've played with the query format and have dramatically improved the speed when issued from a Data Manager (table interface).

This VB
strQuery = "SELECT LOTNUM_10, COMLOT_69, UDFKEY_10, UDFREF_10, SERIAL_68 " _
& "FROM " & Chr(34) & "Order Master" & Chr(34) & ", " _
& Chr(34) & "Lot Track Structure" & Chr(34) & ", " _
& Chr(34) & "Part Lot" & Chr(34) & " WHERE ORDER_10 = ORDNUM_69 " _
& "AND LOTNUM_10 = LOTNUM_68 AND PRTNUM_10 IN " & strComponent _
& " AND STK_68 = 'SUBST614' AND QTYOH_68 = '1' AND " & strTemp
Produces this query (as copied from the immediate window)

SELECT LOTNUM_10, COMLOT_69, UDFKEY_10, UDFREF_10, SERIAL_68 FROM "Order Master", "Lot Track Structure", "Part Lot" WHERE ORDER_10 = ORDNUM_69 AND LOTNUM_10 = LOTNUM_68 AND PRTNUM_10 IN ('015121 01 00', '014121 01 00', '013121 01 00', '012121 01 00') AND STK_68 = 'SUBST614' AND QTYOH_68 = '1' AND (UDFKEY_10 = '4208' OR UDFREF_10 = '4029')

Runs fine from Data Manager, but hangs when run from VB.

MKoslof
04-15-2004, 07:33 AM
When you say "hangs" does it crash, or just take a long time to evaluate and produce an output?

And, since you are using three tables, see if a JOIN speeds up the query in VB.

waits77
04-15-2004, 07:43 AM
JOIN was what I started with. I was quite suprised to see the speed increase when I got rid of it, but it did. The current query returns all the records in less than 3 seconds (when using the Data Manager).

If I use JOIN, I get an error message:

Error #: -2147467259 [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager]Bookmark Error. Possible bad or out of date index.

If I don't use JOIN, the program just hangs. The server is processing something, but I've let it run an hour with no solution.

MKoslof
04-15-2004, 07:59 AM
So a JOIN throws an error and if you don't use a JOIN it just hangs. OK, well somewhere along the line, your indexes, primary/foreign keys are causing VB to go nuts.

Now, if you use a JOIN in the Data Manager, does the query work? If so, make sure you have the service pack five installed on your cpu. And, what is your current code, you have dropped the Data controls correct? Can you post your code? I want to see where you pass in this SQL command to the recordset object.

waits77
04-15-2004, 08:15 AM
Option Explicit

Dim strSize As String
Dim strHsngQry() As String
Dim strLeafQry() As String
Dim intRowsProd As Integer
Dim intBadSize As Integer
Dim intBadParameter As Integer
Dim intNoRecords As Integer
Dim strComponent As String
Dim strQuery As String ' for SQL query
Dim varProd As Variant ' to hold query contents
Dim varSub As Variant
Dim cnConnection As ADODB.Connection
Dim rsRecords As ADODB.Recordset
Const CONNECT_DB = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Data_Sample_Waits"

Private Sub SQL_Query()
' querry the database and load the results
Dim i As Integer ' counter
Dim intParentFlag As Integer
Dim strTemp As String
Dim itmHousing As ListItem

Screen.MousePointer = vbHourglass
If cbxLE.Text <> "" Then
' housings
' if all is specified, get evrything with a non blank UDFKEY
If cbxLE.Text = "All" Then
strTemp = "UDFKEY_10 <> ''"
' if an LE is specified, get all with that LE and then see if there's a parent
Else
strTemp = "UDFKEY_10 = '" & cbxLE.Text & "'"

' find the parent LE if present
strQuery = "SELECT UDFKEY_10, UDFREF_10 FROM " & Chr(34) & "Order Master" _
& Chr(34) & " WHERE " & strTemp
Call Load_Record
If intNoRecords Then
Screen.MousePointer = vbNormal
Exit Sub
End If
'strTemp = "T1.UDFKEY_10 = '" & cbxLE.Text & "'"
strTemp = "UDFKEY_10 = '" & cbxLE.Text & "'"
' loop through the records and see if there's a parent
' if so, add parent to SQL
For i = 0 To intRowsProd - 1
If IsNumeric(varProd(1, i)) Then
cbxLE.Text = Trim(varProd(1, i))
'strTemp = "(" & strTemp & " OR T1.UDFREF_10 = '" & Trim(varProd(1, i)) & "')"
strTemp = "(" & strTemp & " OR UDFREF_10 = '" & Trim(varProd(1, i)) & "')"
Exit For
End If
Next i
End If
'strQuery = "SELECT DISTINCT T3.PARLOT_69, T3.COMLOT_69, T2.OPRSEQ_14," _
'& " T2.QTYREM_14, T1.UDFKEY_10, T1.UDFREF_10 FROM (" & Chr(34) _
'& "Order Master" & Chr(34) & " T1 LEFT OUTER JOIN " & Chr(34) _
'& "Job Progress" & Chr(34) & " T2 ON T1.ORDER_10 = T2.ORDNUM_14) LEFT" _
'& " OUTER JOIN " & Chr(34) & "Lot Track Structure" & Chr(34) _
'& " T3 ON T1.ORDER_10 = T3.ORDNUM_69 WHERE T1.PRTNUM_10 IN " _
'& strComponent _
'& " AND T2.QUECDE_14 = 'Y' AND T2.QTYREM_14 <> '0' AND " & strTemp
' ########## this runs fine ##########
strQuery = "SELECT DISTINCT PARLOT_69, COMLOT_69, OPRSEQ_14," _
& " QTYREM_14, UDFKEY_10, UDFREF_10 FROM " & Chr(34) & "Order Master" _
& Chr(34) & ", " & Chr(34) & "Job Progress" & Chr(34) & ", " _
& Chr(34) & "Lot Track Structure" & Chr(34) & " WHERE ORDER_10 " _
& "= ORDNUM_14 AND ORDER_10 = ORDNUM_69 AND PRTNUM_10 IN " _
& strComponent & " AND QUECDE_14 = 'Y' AND QTYREM_14 <> '0' AND " _
& strTemp
Debug.Print strQuery
Call Load_Record
If intNoRecords = 0 Then
For i = 0 To intRowsProd - 1
Set itmHousing = lsvHousings.ListItems.Add(, , Trim(varProd(0, i)))
itmHousing.SubItems(1) = Trim(varProd(1, i))
itmHousing.SubItems(2) = Trim(varProd(2, i))
itmHousing.SubItems(3) = Trim(varProd(3, i))
itmHousing.SubItems(4) = Trim(varProd(4, i))
itmHousing.SubItems(5) = Trim(varProd(5, i))
Next i
End If
'strQuery = "SELECT T3.PARLOT_69, T3.COMLOT_69, T1.UDFKEY_10, T1.UDFREF_10, SERIAL_68 " _
'& "FROM " & Chr(34) & "Order Master" & Chr(34) & " T1 LEFT OUTER JOIN " _
'& Chr(34) & "Lot Track Structure" & Chr(34) & " T3 ON T1.ORDER_10 = " _
'& "T3.ORDNUM_69, " & Chr(34) & "Part Lot" & Chr(34) & " T4 WHERE " _
'& "T1.LOTNUM_10 = T4.LOTNUM_68 AND T1.PRTNUM_10 IN " & strComponent _
'& " AND T4.STK_68 = 'SUBST614' AND T4.QTYOH_68 = '1' AND " & strTemp
' ########## this hangs ##########
strQuery = "SELECT LOTNUM_10, COMLOT_69, UDFKEY_10, UDFREF_10, SERIAL_68 " _
& "FROM " & Chr(34) & "Order Master" & Chr(34) & ", " _
& Chr(34) & "Lot Track Structure" & Chr(34) & ", " _
& Chr(34) & "Part Lot" & Chr(34) & " WHERE ORDER_10 = ORDNUM_69 " _
& "AND LOTNUM_10 = LOTNUM_68 AND PRTNUM_10 IN " & strComponent _
& " AND STK_68 = 'SUBST614' AND QTYOH_68 = '1' AND " & strTemp
Debug.Print strQuery
Call Load_Record
If intNoRecords = 0 Then
For i = 0 To intRowsProd - 1
Set itmHousing = lsvHousings.ListItems.Add(, , Trim(varProd(0, i)))
itmHousing.SubItems(1) = Trim(varProd(1, i))
itmHousing.SubItems(2) = "SbAsmbly"
itmHousing.SubItems(3) = "1"
itmHousing.SubItems(4) = Trim(varProd(2, i))
itmHousing.SubItems(5) = Trim(varProd(3, i))
Next i
End If
'
'
'
End Sub

Private Sub Load_Record()

Set rsRecords = New ADODB.Recordset
On Error GoTo error1
intNoRecords = 0

rsRecords.Open strQuery, cnConnection, adOpenKeyset, adLockOptimistic, adCmdText
intRowsProd = rsRecords.RecordCount ' see how many records (rows)
varProd = rsRecords.GetRows(intRowsProd) ' retrieve all of the records
rsRecords.Close
Set rsRecords = Nothing

If intRowsProd = 0 Then intNoRecords = 1
error1:
If Err.Number = 3219 Then ' if the database contains no matching records
intNoRecords = 1
Else
If Err.Number <> 0 Then
' use standard error message
MsgBox ("Error #: " & Str(Err.Number) & " " & Err.Description)
intNoRecords = 1
' jump out
Exit Sub
End If
End If
End Sub

Private Sub Form_Load()
Dim colHousing As ColumnHeader
Dim colLeaflet As ColumnHeader
Set colHousing = lsvHousings.ColumnHeaders.Add()
colHousing.Text = "Lot"
colHousing.Width = lsvHousings.Width * 0.25
Set colHousing = lsvHousings.ColumnHeaders.Add()
colHousing.Text = "MLN"
colHousing.Width = lsvHousings.Width * 0.24
Set colHousing = lsvHousings.ColumnHeaders.Add()
colHousing.Text = "Operation"
colHousing.Width = lsvHousings.Width * 0.14
Set colHousing = lsvHousings.ColumnHeaders.Add()
colHousing.Text = "Quantity"
colHousing.Width = lsvHousings.Width * 0.13
Set colHousing = lsvHousings.ColumnHeaders.Add()
colHousing.Text = "LE"
colHousing.Width = lsvHousings.Width * 0.09
Set colHousing = lsvHousings.ColumnHeaders.Add()
colHousing.Text = "Parent LE"
colHousing.Width = lsvHousings.Width * 0.14
Set cnConnection = New ADODB.Connection
cnConnection.ConnectionString = CONNECT_DB
cnConnection.Open
End Sub

How can I tell if the indexes, primary/foreign keys are bad?

MKoslof
04-15-2004, 08:28 AM
Ok, do me a favor...run your code in debug mode. I want to see where this starts to go haywire. As you press F8 and go through this code, once you hit Call Load_Data, where does the code hang, does it hang on your rs.Open command, or on your .GetRows() Method. My bet is one of the two.


and actually, do a debug.print of your sql statement, and paste it here, what does it look like exactly from VB 6.

waits77
04-15-2004, 08:35 AM
From the Immediate window:

SELECT LOTNUM_10, COMLOT_69, UDFKEY_10, UDFREF_10, SERIAL_68 FROM "Order Master", "Lot Track Structure", "Part Lot" WHERE ORDER_10 = ORDNUM_69 AND LOTNUM_10 = LOTNUM_68 AND PRTNUM_10 IN ('015121 01 00', '014121 01 00', '013121 01 00', '012121 01 00') AND STK_68 = 'SUBST614' AND QTYOH_68 = '1' AND (UDFKEY_10 = '4208' OR UDFREF_10 = '4029')

it hangs on the
intRowsProd = rsRecords.RecordCount ' see how many records (rows)

MKoslof
04-15-2004, 08:41 AM
Interesting, so it gets past the recordset declaration and it dies on the recordset. OK, comment out the recordset lines, does the code run fine without this? You are using the proper lock and cursor type (OpenKeyset and LockOptimistic) to return the recordcount, thats interesting. If you remove the recordcount line are we OK. If so, let's try the OpenDynamic cursor type.

waits77
04-15-2004, 09:03 AM
rsRecords.Open strQuery, cnConnection, adOpenKeyset, adLockOptimistic, adCmdText
'intRowsProd = rsRecords.RecordCount ' see how many records (rows)
intRowsProd = 10
varProd = rsRecords.GetRows(intRowsProd) ' retrieve all of the records
gives an error message, but returns the records!

Error #: 3021 Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

It doesn't matter what value I use for intRowsProd, I've tried from 5 to 20 (there are about 50 records) and it doesn't change the error.

MKoslof
04-15-2004, 09:06 AM
OK, try changing your cursor type. It doesn't like the recordcount property. Pervasive and VB are fighting over something...hmmm. Try OpenDynamic. Because in the end you want to pass the recordcount to the variable so your .GetRows() call works.

And alternative could be to use a SELECT COUNT(*), and just reference the alias as your recordcount. But there must be a way to correct this...

waits77
04-15-2004, 09:11 AM
rsRecords.Open strQuery, cnConnection, adOpenDynamic, adLockOptimistic, adCmdText
gives me problems. I don't even get to where we've been working because earlier queries return no records.

MKoslof
04-15-2004, 09:13 AM
Ah ok, stick with OpenKeySet then. Are you against the SELECT COUNT(*) method, or returning a Count variable within your sql statement..this would serve the same purpose, then just assign the alias created to your intRowsProd variable

waits77
04-15-2004, 09:14 AM
BTW We're runing 2000i service pack 3. Our main DB application doesn't support anything higher.

waits77
04-15-2004, 09:16 AM
Ah ok, stick with OpenKeySet then. Are you against the SELECT COUNT(*) method, or returning a Count variable within your sql statement..this would serve the same purpose, then just assign the alias created to your intRowsProd variable
I'm not against anything that works! I don't know how to assign an alias. Can you give me a quick example?

MKoslof
04-15-2004, 09:27 AM
OK, I am using version 8 and 8.5 at work..one of our main applications uses Pervasive as its backend. I must admit, I don't like it :). Well, the problem with returning the COUNT(*) is this going to get hairy with your multiple tables. Hmm....

Let me think on this one. There must be a reason why your recordCount is failing.

MKoslof
04-15-2004, 09:30 AM
OK, heres an idea...use a ServerSide cursor...with your connection object declaration add this parameter before opening it:

conn.CursorLocation = adUseServer

I don't think Pervasive will let you run the recordcount against a client side cursor. However, by default, I am not sure what CursorLocation it gives you. With Pervasive it may be useNone by default.

waits77
04-15-2004, 09:46 AM
conn.CursorLocation = adUseServer

Didn't help.

MKoslof
04-15-2004, 10:13 AM
Really? do another test for me...in the Pervasive SQL Data Manager try to do a select COUNT(*) on this query...do you get results or an index error.

waits77
04-15-2004, 10:28 AM
SELECT COUNT(*) LOTNUM_10, COMLOT_69, UDFKEY_10, UDFREF_10, SERIAL_68 FROM "Order Master", "Lot Track Structure", "Part Lot" WHERE ORDER_10 = ORDNUM_69 AND LOTNUM_10 = LOTNUM_68 AND PRTNUM_10 IN ('015121 01 00', '014121 01 00', '013121 01 00', '012121 01 00') AND STK_68 = 'SUBST614' AND QTYOH_68 = '1' AND (UDFKEY_10 = '4208' OR UDFREF_10 = '4029')

Gives error: Selection not row based in WHERE

waits77
04-15-2004, 10:38 AM
I can live with this if I have to.
Private Sub Load_Record()

Set rsRecords = New ADODB.Recordset
On Error GoTo error1
intNoRecords = 1
Set varProd = Nothing

rsRecords.Open strQuery, cnConnection, adOpenKeyset, adLockOptimistic, adCmdText
varProd = rsRecords.GetRows(2000) ' arbitrary number large enough to get biggest recordset
intRowsProd = UBound(varProd, 2) + 1

If intRowsProd > 0 Then intNoRecords = 0
error1:
If Err.Number = 3219 Then ' if the database contains no matching records
intNoRecords = 1
ElseIf Err.Number = 3021 Then
' don't do anything, just ignore
Else
If Err.Number <> 0 Then
' use standard error message
MsgBox ("Error #: " & Str(Err.Number) & " " & Err.Description)
intNoRecords = 1
End If
End If
rsRecords.Close
Set rsRecords = Nothing
End Sub

MKoslof,
I sure appreciate all your work and help.

MKoslof
04-15-2004, 11:04 AM
OK, well I believe you are stuck with the current method you are using. I did some research, supposedly this will WORK with the OLEDB provider:



rs.Open "Table1", "Provider=PervasiveOLEDB;Data Source=Demodata",
adOpenDynamic, adLockOptimistic, adCmdTable

msgbox rs.recordcount



However, in Pervasive 2000...this will FAIL:



rs.Open "SELECT * FROM Table1", "Provider=PervasiveOLEDB;Data
Source=Demodata", adOpenDynamic, adLockOptimistic

msgbox rs.RecordCount



Basically, it goes on to say that Pervasive 2000 OLE DB is only iRowSet compatible, NOT iCommand compatible. Table names are the only way to do it for now.

So, my suggestion was going to be use a counter, such as i = i+1 until EOF. But the method you are using is comparable to this. Appears that is your only option :). I assume versions 8 and 8.5 are compatible with the iCommand property since I have used it (I think, don't recall what code I have for the few Pervasive apps we have).

waits77
04-15-2004, 12:49 PM
Now what?!

I was running the program over and over while trying to get my ListView column widths set. The first couple of times, everything worked as expected, but now, I'm getting the same error message and I'm NOT getting any records.

All I changed was the FormLoad sub and only the column widths, but now it doesn't work again.

Seems to be working intermittently. Will work a few times and then start giving error messages. If I try again in 10 or 15 minutes, it may start working. This is a test db, a copy of our normal db. I'm the only one accessing this db. However, the engine is being used by several.

MKoslof
04-15-2004, 05:33 PM
Hmm...I would try reloading your Pervasive client. Also, try with a fresh set of test data files...does the problem still occur?

waits77
04-16-2004, 08:27 AM
The problem of intermittent performance is due to the Debug environment. If I stop the execution using the Stop button on the tool bar, the connection doesn't get closed and I'll get an error message the next time I try to run. I put the .Close in the form unload, but that doesn't seem to happen in Debug. If I put the .Close in a command button and use the button to stop the program, everything's OK.

MKoslof
04-16-2004, 08:34 AM
So from VB, where does the error occur. I would really consider reloading your Pervasive Workgroup engine. If you can open and close your recordset object successfully from VB, most likely the Pervasive SQL Data manager is corrupted

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum