[Search for users]
[Overall Top Noters]
[List of all Conferences]
[Download this site]
| Title: | DBSTARS Conference |
|
| Moderator: | BROKE::BASTINE |
|
| Created: | Wed Feb 02 1994 |
| Last Modified: | Thu Jun 05 1997 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 791 |
| Total number of notes: | 1521 |
788.0. "2 cursors & passthru sql using VB" by BROKE::BITHER () Tue May 27 1997 15:31
-< DEC ODBC Driver >-
================================================================================
Note 1340.0 2 passthru sql cursors in VB cause odbc 3146 err 3 replies
BROKE::BITHER 241 lines 9-MAY-1997 10:10
--------------------------------------------------------------------------------
Hi,
Have a customer using latest sql/services and odbc driver. Also using
Visual Basic passthru to get to Rdb.
Customer says when they do a 2 record set using Visual Basic passthru SQL
they get an ODBC error 3146 which is odbc call failed.
What works:
----------
1 record set NOT using passthru SQL in VB.
1 record set using passthru SQL in VB.
2 record set NOT using passthru SQL in VB. (They don't want to do this since
they say performance is much better using passthru.)
What doesn't work:
-----------------
2 record set using passthru SQL in VB.
They are doing "move nexts" in the VB code.
I asked them to send the following:
1) example of VB code that works
2) example of VB code that doesn't work (so we can get an idea of what
they are doing.)
3) client.log
4) odbcrdb.log
They didn't send the VB code, only the log files which show almost nothing
and appear below. As soon as I get the VB code, I'll post as a reply to
this note. Wanted to get this in anyway in case someone had ideas based
on the info I already have.
Thanks, Diane
-------------------------------
ODBCRDB.LOG
-----------
Oracle ODBC 32 Bit Driver for Rdb Version 2.10.11.0.0
Oracle ODBC 32 Bit Driver for Rdb File Version 2.10.11.0.0
CLIENT.LOG
----------
Log file generated by C:\WINNT\System32\sqsapi32.dll
Oracle Sql/Services Version 7.00
ASSOCIATE LEVEL LOG
----SQLSRV_ASSOCIATE
--------SQLSRV_ASSOCIATE ID: 6911478
--------NODE: 132.147.160.20, USERNAME: FILIA_L_VB, SQLCODE: 0, SQLERRD[0] 0
--------NETWORK TRANSPORT: TCP/IP
ROUTINE LEVEL LOG at 15:33:32 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:33 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:37 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:37 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:37 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:37 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:37 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:37 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:38 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:51 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:51 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:52 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:52 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:52 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:53 on 07-May-1997
----SELECT LIST SQLDA
ROUTINE LEVEL LOG at 15:33:54 on 07-May-1997
----SELECT LIST SQLDA
ASSOCIATE LEVEL LOG
----SQLSRV_RELEASE
--------SQLSRV_ASSOCIATE ID: 6911478
================================================================================
Note 1340.1 2 passthru sql cursors in VB cause odbc 3146 err 1 of 3
M5::JBALOGH 13 lines 9-MAY-1997 13:01
-< need logs or a real error message >-
--------------------------------------------------------------------------------
Well, the 3146 ODBC call failed error is worthless. get the customer to
walk through the errors().description collection or we need to get the
real error from a odbc or client log.
That log you posted was missing just about everything. They need to set
client logging to a higher number (like 7).
We may be able to determine something if we can get the error message.
I actually do recordset.movenext in VB all the time without a problem.
(both with and without passthrough queries).
John
================================================================================
Note 1340.2 2 passthru sql cursors in VB cause odbc 3146 err 2 of 3
BROKE::BITHER 185 lines 14-MAY-1997 15:03
-< log files and vb code available - thanks >-
--------------------------------------------------------------------------------
Thanks John.
You were right about the logs. I pointed them to the rdb odbc driver
help file to see the values to be used and had them resend using 7.
But I don't see the error. They sent:
1. Two client logs (both created at the same time)
2. An odbcrdb.log
3. VB code.
All the files are in
nomahs::nomahs2:[dbither.joanne]client61.log
nomahs::nomahs2:[dbither.joanne]client62.log
nomahs::nomahs2:[dbither.joanne]odbcrdb.log
nomahs::nomahs2:[dbither.joanne]vb.doc (ms word doc)
nomahs::nomahs2:[dbither.joanne]vb.txt
All the log files look too big to post here but I've posted vb.txt below:
Thanks, Diane
-------------------------
Program with the bug:
The program work when we are not using dbsqlpassthrough
Private Sub cmdImprimer_Click()
Dim rsGI As Recordset
Dim sSQLGI As String
Dim rs As Recordset
Dim sSQL As String
Dim rs1 As Recordset
Dim sSQL1 As String
Dim rs2 As Recordset
Dim sSql2 As String
Dim sSQLAccess As String
Dim sMessF As String
Dim sMessA As String
Dim lNoBloc As Long
Dim sCodeProb As String
Dim LNoColPrec As Long
Dim lNbSqib As Long
lNbSqib = 4
pnlMessage.Caption = "Pr�paration de l'impression en cours un moment S.V.P.
..."
pnlMessage.Visible = True
pnlMessage.Refresh
OuvrirBDAccess sMessF, sMessA
'**** V�rifier si pour le groupe_impression s'il y a plus d'un jeu
sSQLGI = "SELECT sigle_client, no_jeu, nom_jeu, no_jeu_int, code_produit,
in
d_algo_dec, gr_barcode, nb_unite_bloc, type_mise_en_cais, type_etiq, ind_repr "
& _
" FROM jeu " + _
" WHERE no_gr_imp_int = " & frmGame.lblNoGrImpInt & _
" ORDER BY no_jeu_int DESC"
Set rsGI = g_db.OpenRecordset(sSQLGI, dbOpenSnapshot, dbSQLPassThrough)
If txtFirstPool <> "" Then
While Not rsGI.EOF
'=========================
'**** Pool de Loterie ****
'=========================
If Val(txtBlocStart) = 0 Then ' pour traitement complet
sSQL = "SELECT no_block, prem_unite, dern_unite, etat_block " + _
" FROM bloc_jeu " + _
" WHERE no_jeu_int = " & Val(frmBlSheet.txtNo_jeu_int) & _
" AND no_pool = " & Val(frmBlSheet.txtFirstPool) & _
" ORDER BY no_block ASC "
Else ' Pour traitement par bloc
' ", nb_bil_sqib" + _
sSQL = "SELECT no_block, prem_unite, dern_unite, etat_block " + _
" FROM bloc_jeu " + _
" WHERE no_jeu_int = " & Val(frmBlSheet.txtNo_jeu_int) & _
" AND no_pool = " & Val(frmBlSheet.txtFirstPool) & _
" AND no_block >= " & Val(txtBlocStart) & " and no_block <= " &
Va
l(txtBlocEnd) & _
" ORDER BY no_block ASC "
End If
Set rs = g_db.OpenRecordset(sSQL, dbOpenSnapshot, dbSQLPassThrough)
Do While Not rs.EOF
sSql2 = " SELECT Code_probl from bloc_probleme " + _
" WHERE no_jeu_int = " & Val(frmBlSheet.txtNo_jeu_int) & _
" AND no_pool = " & Val(frmBlSheet.txtFirstPool) & _
" AND no_bloc = " & rs!no_block & _
" AND categorie = '" & cmbCategorie & "'" & _
" Order by code_probl "
Set rs2 = g_db.OpenRecordset(sSql2, dbOpenSnapshot,
dbSQLPassThrough)
scodeprobl = " "
Do While Not rs2.EOF
sCodeProb = sCodeProb & rs2!Code_probl
rs2.MoveNext
Loop
rs2.Close
sSQL1 = "SELECT no_unite, no_col_bloc, no_ligne_bloc,
code_etat_unite
" + _
" FROM view_unite " + _
" WHERE no_jeu_int = " & Val(frmBlSheet.txtNo_jeu_int) & _
" AND no_pool = " & Val(frmBlSheet.txtFirstPool) & _
" AND no_unite >= " & rs!prem_unite & _
" AND no_unite <= " & rs!dern_unite & _
" ORDER BY no_col_bloc, no_ligne_bloc DESC "
Set rs1 = g_db.OpenRecordset(sSQL1, dbOpenSnapshot,
dbSQLPassThrough)
LNoColPrec = 0
Do While Not rs1.EOF
'ecrire dans access
sSQLAccess = "Insert INTO unite (No_col, No_ligne, " + _
" unite, Code_etat_unite) " + _
" VALUES (" & rs1.no_col_bloc & ", " &
rs1!no_ligne_b
loc & ", " + _
"" & rs1!no_unite & ", """ & rs1!code_etat_unite &
""
") "
g_dbAccess.Execute sSQLAccess
If (rs1.no_col_bloc <> LNoColPrec) Then
LNoColPrec = rs1.no_col_bloc
sSQLAccess = "Insert INTO FdeBloc (Categorie, No_Pool, No_Bloc,
No_col, Nb_ligne, Nom_du_jeu, Nb_Sqib, etatdubloc, codeprobl) " + _
" VALUES (""" & cmbCategorie & """, " &
Val(frmBlSheet.txtFirstP
ool) & ", " & rs!no_block & ", " & rs1.no_col_bloc & ", " + _
"" & rs1.no_ligne_bloc & ", """ + frmGame.lblNomJeu + """, " +
_
"" & lNbSqib & ", """ + rs.etat_block + """, """ + sCodeProb +
"
"") "
g_dbAccess.Execute sSQLAccess
End If
rs1.MoveNext
Loop
rs1.Close
rs.MoveNext
Loop
rs.Close
rsGI.MoveNext
Wend ' groupe impression
rsGI.Close
Else
LongBeep
MsgBox "Vous devez entrer le num�ro de pool!", vbCritical
txtFirstPool.SetFocus
End If
End Sub
================================================================================
Note 1340.3 2 passthru sql cursors in VB cause odbc 3146 err 3 of 3
BROKE::BITHER 12 lines 27-MAY-1997 14:28
-< Customer solved - here is solution fyi >-
--------------------------------------------------------------------------------
Customer resolved this themselves. Putting solution here in case anybody
else needs it.
Diane
--------------------------------
SOLUTION: (in customer's words)
We resolved the problem related with the 2 cursors . The problem was that
in visual basic when you open more then 2 databases you have to create the
workspace. We were using a default workspace for the 2 databases, it seems
that when you are using sqlpassthrough , it gets mixed up.
| T.R | Title | User | Personal
Name | Date | Lines
|
|---|