Re: FMTONLY problems
- From: "Bob" <not provided>
- Date: Wed, 30 May 2007 09:34:25 +1200
"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:OQAeAwYoHHA.4516@xxxxxxxxxxxxxxxxxxxxxxx
You failed to provide the ADO code that is causing this behavior.
I'm using delphi, which has a wrapper around the ADO objects. I spent a
whole lot of time trying to work out what it was doing that was causing the
problem.
The first time it runs, it creates the bad FMTONLY statment and runs. The
second time it it does the bad FMTONLY, sets SET NO_BROWSETABLE OFF, runs
the SQL 3 times, SET NO_BROWSETABLE ON, does a correct FMTONLY, SET
NO_BROWSETABLE OFF, then executes the SQL again.
Below is the simplest example that I could come up with that exibits the
issue. Below that is a script to create a test database.
procedure TForm1.FailTest;
var
Conn: _Connection;
Cmd: _Command;
Records: _Recordset;
ADOCommandConstructionObj: ADOCommandConstruction;
ICommandObj: ICommand;
begin
Conn := CoConnection.Create;
Cmd := CoCommand.Create;
Records := CoRecordset.Create;
Conn.Open(
'Provider=SQLNCLI.1;' +
'Integrated Security=SSPI;' +
'Persist Security Info=False;' +
'Initial Catalog=TestDB;' +
'Data Source=.\SQL2005',
'', '', adConnectUnspecified);
Cmd.Set_ActiveConnection(Conn);
Cmd.CommandText :=
'IF NOT EXISTS ( ' + CRLF +
' SELECT SystemScanfileKey ' + CRLF +
' FROM SystemScanfileScan ' + CRLF +
' WHERE SystemScanfileKey = ? ' + CRLF +
' AND ScanKey = ? ' + CRLF +
' ) ' + CRLF +
'BEGIN ' + CRLF +
' INSERT INTO SystemScanfileScan ' + CRLF +
' (SystemScanfileKey, ScanKey) ' + CRLF +
' VALUES ' + CRLF +
' (?, ?) ' + CRLF +
' SELECT 1 ' + CRLF +
'END ' + CRLF +
'ELSE ' + CRLF +
' SELECT 0 ' + CRLF ;
OleCheck(Cmd.QueryInterface(ADOCommandConstruction,
ADOCommandConstructionObj));
OleCheck(ADOCommandConstructionObj.OLEDBCommand.QueryInterface(ICommand,
ICommandObj));
// there was a whole lot more code here to get parameter types, names and
direction etc
Cmd.Parameters.Append(Cmd.CreateParameter('P1', adInteger, adParamInput,
0, 1));
Cmd.Parameters.Append(Cmd.CreateParameter('P2', adInteger, adParamInput,
0, 1));
Cmd.Parameters.Append(Cmd.CreateParameter('P3', adInteger, adParamInput,
0, 1));
Cmd.Parameters.Append(Cmd.CreateParameter('P4', adInteger, adParamInput,
0, 1));
Records.Open(Cmd, EmptyParam, adOpenKeyset, adLockOptimistic, adCmdText);
end;
USE [master]
GO
CREATE DATABASE [TestDB]
GO
USE [TestDB]
GO
CREATE TABLE [dbo].[Scan](
[ScanKey] [int] IDENTITY(1,1) NOT NULL,
[Barcode] VARCHAR(13) NOT NULL
CONSTRAINT [PK_Scan] PRIMARY KEY CLUSTERED
(
[ScanKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SystemScanfile](
[SystemScanfileKey] [int] IDENTITY(1,1) NOT NULL,
[Name] VARCHAR(15) NOT NULL,
CONSTRAINT [PK_SystemScanfile] PRIMARY KEY CLUSTERED
(
[SystemScanfileKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SystemScanfileScan](
[SystemScanfileKey] [int] NOT NULL,
[ScanKey] [int] NOT NULL,
CONSTRAINT [PK_SystemScanfileScan] PRIMARY KEY CLUSTERED
(
[SystemScanfileKey] ASC,
[ScanKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SystemScanfileScan] WITH CHECK ADD CONSTRAINT
[FK_SystemScanfileScan_Scan] FOREIGN KEY([ScanKey])
REFERENCES [dbo].[Scan] ([ScanKey])
GO
ALTER TABLE [dbo].[SystemScanfileScan] WITH CHECK ADD CONSTRAINT
[FK_SystemScanfileScan_SystemScanfile] FOREIGN KEY([SystemScanfileKey])
REFERENCES [dbo].[SystemScanfile] ([SystemScanfileKey])
.
- Follow-Ups:
- Re: FMTONLY problems
- From: Bob Barrows [MVP]
- Re: FMTONLY problems
- References:
- FMTONLY problems
- From: Bob
- Re: FMTONLY problems
- From: Bob Barrows [MVP]
- FMTONLY problems
- Prev by Date: Re: Joining two tables with fields that have the same names
- Next by Date: Re: Can SQL do this?
- Previous by thread: Re: FMTONLY problems
- Next by thread: Re: FMTONLY problems
- Index(es):
Relevant Pages
|
|
