[Search for users]
[Overall Top Noters]
[List of all Conferences]
[Download this site]
| Title: | DEC ODBC Driver |
| Notice: | DEC ODBC Driver V2.0 Now Available |
| Moderator: | SQLSRV::MAVRIS |
|
| Created: | Tue Dec 29 1992 |
| Last Modified: | Fri Jun 06 1997 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 1357 |
| Total number of notes: | 4864 |
1314.0. "Does VB passthru SQL work in READ ONLY mode??" by BROKE::BASTINE () Fri Mar 21 1997 10:45
I have a customer trying to write a VB application using DAO.
He generates some SQL with his program and then uses VB passthru SQL to
execute the SQL statements he generated. Unfortunately his application
is failing with a 3146 error, which is ODBC call failed.
I had him turn on client logging and the error is that he is trying to
insert during a READ ONLY transaction, but he isn't setting the transaction
to read only. Here is a snippet from the client log file.... my question
is this:
I know that MSAccess passthru SQL will only allow read only transactions, unless
you use compound statements (or something like that) to specifically set the
transacation otherwise. Does VB passthru SQL work the same way? How/what
can he to in order to pass an insert through passthru SQL to his Rdb db?
.
.
.
ROUTINE LEVEL LOG
----SQLSRV_EXECUTE_IMMEDIATE
--------SQL STATEMENT
------------len: 54, value: DECLARE TRANSACTION READ ONLY WAIT CONSISTENCY LEVEL
------------ 2
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET LENGTH: 68
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 18, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE_IMMEDIATE
------------SQL STATEMENT
----------------SQLSRV_ASCII_STRING, len: 54
--------------------len: 54, value: DECLARE TRANSACTION READ ONLY WAIT CONSISTEN
--------------------CY LEVEL 2
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: read
----PACKET LENGTH: 20
PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 18, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE_IMMEDIATE ACK
------------STATUS
----------------SQLSRV_GENERALIZED_NUMBER, len: 1
--------------------len: 1, value: 0
------------EXECUTE PARAMETER
----------------SQLSRV_GENERALIZED_NUMBER, len: 1
--------------------len: 1, value: 0
--------END OF MESSAGE
ROUTINE LEVEL LOG
----SQLSRV_PREPARE
--------SQL STATEMENT
------------len: 150, value: INSERT INTO INSURANCE_Policies ( Insurance_policie
------------s_stamp, Insurance_Effective_Date ) VALUES ( '01-Jan-1997 00:00:00.
------------00', '30-Dec-1899 23:59:59.99')
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET LENGTH: 164
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 19, PACKET SEQUENCE: 0
--------SQLSRV_PREPARE
------------SQL STATEMENT
----------------SQLSRV_ASCII_STRING, len: 150
--------------------len: 150, value: INSERT INTO INSURANCE_Policies ( Insurance
--------------------_policies_stamp, Insurance_Effective_Date ) VALUES ( '01-Ja
--------------------n-1997 00:00:00.00', '30-Dec-1899 23:59:59.99')
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: read
----PACKET LENGTH: 21
PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 19, PACKET SEQUENCE: 0
--------SQLSRV_PREPARE ACK
------------STATEMENT ID
----------------SQLSRV_GENERALIZED_NUMBER, len: 7
--------------------len: 7, value: 3542352
--------END OF MESSAGE
ROUTINE LEVEL LOG
----SQLSRV_EXECUTE
--------STATEMENT ID
------------3542352
--------EXECUTE_FLAG: 0
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET LENGTH: 25
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 20, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE
------------STATEMENT ID
----------------SQLSRV_GENERALIZED_NUMBER, len: 7
--------------------len: 7, value: 3542352
------------REPEAT COUNT
----------------len: 2, value: 1
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: read
----PACKET LENGTH: 99
PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 20, PACKET SEQUENCE: 0
--------ERROR ACK
------------ERROR_VALUE_TAG
----------------SQLSRV_GENERALIZED_NUMBER, len: 4
--------------------len: 4, value: -817
------------SPECIFIC_ERROR_TAG
----------------SQLSRV_GENERALIZED_NUMBER, len: 1
--------------------len: 1, value: 0
------------SPECIFIC_ERROR_TEXT_TAG
----------------SQLSRV_ASCII_STRING, len: 72
--------------------len: 72, value: %RDB-E-READ_ONLY_TRANS, attempt to update du
--------------------ring a read-only transaction
--------END OF MESSAGE
ROUTINE LEVEL LOG
----SQLSRV_RELEASE_STATEMENT
--------STATEMENT ID
------------[0] 3542352
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET LENGTH: 25
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 21, PACKET SEQUENCE: 0
--------SQLSRV_RELEASE_STATEMENT
------------STATEMENT COUNT
----------------len: 2, value: 1
------------STATEMENT ID
----------------SQLSRV_GENERALIZED_NUMBER, len: 7
--------------------len: 7, value: 3542352
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: read
----PACKET LENGTH: 19
PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 21, PACKET SEQUENCE: 0
--------SQLSRV_RELEASE_STATEMENT ACK
------------STATUS
----------------SQLSRV_GENERALIZED_NUMBER, len: 1
--------------------len: 1, value: 0
------------STATEMENT COUNT
----------------len: 2, value: 1
--------END OF MESSAGE
ROUTINE LEVEL LOG
----SQLSRV_EXECUTE_IMMEDIATE
--------SQL STATEMENT
------------len: 54, value: DECLARE TRANSACTION READ ONLY WAIT CONSISTENCY LEVEL
------------ 2
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET LENGTH: 68
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 22, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE_IMMEDIATE
------------SQL STATEMENT
----------------SQLSRV_ASCII_STRING, len: 54
--------------------len: 54, value: DECLARE TRANSACTION READ ONLY WAIT CONSISTEN
--------------------CY LEVEL 2
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: read
----PACKET LENGTH: 106
PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 22, PACKET SEQUENCE: 0
--------ERROR ACK
------------ERROR_VALUE_TAG
----------------SQLSRV_GENERALIZED_NUMBER, len: 2
--------------------len: 2, value: -1
------------SPECIFIC_ERROR_TAG
----------------SQLSRV_GENERALIZED_NUMBER, len: 1
--------------------len: 1, value: 0
------------SPECIFIC_ERROR_TEXT_TAG
----------------SQLSRV_ASCII_STRING, len: 81
--------------------len: 81, value: %SQL-F-NO_DCLTXN, You cannot declare a trans
--------------------action while a transaction is active.
--------END OF MESSAGE
ROUTINE LEVEL LOG
----SQLSRV_PREPARE
--------SQL STATEMENT
------------len: 150, value: INSERT INTO INSURANCE_Policies ( Insurance_policie
------------s_stamp, Insurance_Effective_Date ) VALUES ( '01-Jan-1997 00:00:00.
------------00', '30-Dec-1899 23:59:59.99')
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET LENGTH: 164
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 23, PACKET SEQUENCE: 0
--------SQLSRV_PREPARE
------------SQL STATEMENT
----------------SQLSRV_ASCII_STRING, len: 150
--------------------len: 150, value: INSERT INTO INSURANCE_Policies ( Insurance
--------------------_policies_stamp, Insurance_Effective_Date ) VALUES ( '01-Ja
--------------------n-1997 00:00:00.00', '30-Dec-1899 23:59:59.99')
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: read
----PACKET LENGTH: 21
PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 23, PACKET SEQUENCE: 0
--------SQLSRV_PREPARE ACK
------------STATEMENT ID
----------------SQLSRV_GENERALIZED_NUMBER, len: 7
--------------------len: 7, value: 3542352
--------END OF MESSAGE
ROUTINE LEVEL LOG
----SQLSRV_EXECUTE
--------STATEMENT ID
------------3542352
--------EXECUTE_FLAG: 0
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET LENGTH: 25
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 24, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE
------------STATEMENT ID
----------------SQLSRV_GENERALIZED_NUMBER, len: 7
--------------------len: 7, value: 3542352
------------REPEAT COUNT
----------------len: 2, value: 1
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: read
----PACKET LENGTH: 99
PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 24, PACKET SEQUENCE: 0
--------ERROR ACK
------------ERROR_VALUE_TAG
----------------SQLSRV_GENERALIZED_NUMBER, len: 4
--------------------len: 4, value: -817
------------SPECIFIC_ERROR_TAG
----------------SQLSRV_GENERALIZED_NUMBER, len: 1
--------------------len: 1, value: 0
------------SPECIFIC_ERROR_TEXT_TAG
----------------SQLSRV_ASCII_STRING, len: 72
--------------------len: 72, value: %RDB-E-READ_ONLY_TRANS, attempt to update du
--------------------ring a read-only transaction
--------END OF MESSAGE
| T.R | Title | User | Personal
Name | Date | Lines |
|---|
| 1314.1 | use a compound statement | M5::JBALOGH | | Fri Mar 21 1997 10:58 | 19 |
| The db engine for VB is the same as for Access and the same rules
apply. These products specificly set the transaction mode to read only
before executing the statement.
The only way to do this is to use a compound statement. I have done
this many times in the past on my own applications.
begin
set transaction read write reserving whatever;
do work;
commit;
end
Be sure to commit or rollback the transaction before the end.
John
PS: The term and implementation of SQLPassThrough is specific to
Microsoft tools. This is a non-issue with native ODBC.
|
| 1314.2 | thx | BROKE::BASTINE | | Fri Mar 21 1997 11:36 | 5 |
| Thanks John!
That's what I thought.
Renee
|
| 1314.3 | What dies first if they don't commit/rollback ? | NOMAHS::SECRIST | Rdb WWS; [email protected] | Mon Mar 24 1997 14:55 | 13 |
|
; Be sure to commit or rollback the transaction before the end.
I have a customer who never commits or rolls back (see 1318.0).
What if compound statements are not involved ? Will it allow
you to keep executing SELECTs until you run out of memory on
the client or server and die ? Would AUTOCOMMIT influence a
read only transaction or only insert/update/deletes ?
Regards,
rcs
|