Re: DBI don't fetch my data...or at least not all records

[karlgoethebier]

Hi, perhaps you should try 'SELECT 'foo' and see what happens. ( no DUAL in MSSQL...). Regards, Karl.

Update: I think it is not a bad idea to check your connection with a simple query.

[kurreburre]

Hi thanks for trying to help out. Well the connection works fine, as stated bofore if I use the same connection towards another table in the same db, with the same user it works. I have also checked permission on the table and there are no strange permissions and I use trused user on both ODBC and when connection via enteprise manager. But in enterprise manager I get a result

[karlgoethebier]

Update:

Yes, but do you fetch a result if you say SELECT 'foo'? Please feel free to provide more information about what you want to do. I then can try to repeat this with my MSSQL...

I could not repeat it (i'm using SQL Server Native Client 10.0):

SELECT * FROM FXSpots;
ID    LHSCurrencyID    RHSCurrencyID    TenorID    BBTicker WMRic    C
+orrespondingSpotID
6    1    1    1    foo    bar    1
7    1    1    1    foo    bar    1
8    1    1    1    foo    bar    1
9    1    1    1    foo    bar    1
10    1    1    1    foo    bar    1
11    1    1    1    foo    bar    1
12    1    1    1    foo    bar    1

#!c:/perl/bin/perl.exe

use DBI;

my $dbh = DBI->connect("dbi:ODBC:Market_Data_prod", "karl", "secret");

my ( $bbt, $wmr );

my $sql = 'select BBTicker, WMRic from FXSpots where id = 10'; # i can
+ get all..

my $sth = $dbh->prepare($sql);

$sth->execute() || die "ERROR Unable to execute sql statment $sql...+"
+;

$sth->bind_columns( \$bbt, \$wmr );

$sth->fetch;

# print ("BBticker = $bbt WMRic = "); # ...you missed $wmr

print("BBticker = $bbt WMRic = $wmr");

__END__

C:\Dokumente und Einstellungen\karl\Desktop\market_data>market_data.pl
BBticker = foo WMRic = bar

Sorry that i could not help, Regards, Karl

[kurreburre]

Yepp select 'foo' works like a charm. The failing table looks like this

USE [Market_Data_prod]
GO

/****** Object:  Table [dbo].[FXSpots]    Script Date: 11/20/2012 19:4
+6:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FXSpots](
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [LHSCurrencyID] [int] NOT NULL,
    [RHSCurrencyID] [int] NOT NULL,
    [TenorID] [int] NOT NULL,
    [BBTicker] [nvarchar](50) NOT NULL,
    [WMRic] [nvarchar](50) NULL,
    [CorrespondingSpotID] [int] NULL,
 CONSTRAINT [PK_FXSpots] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
+ = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 9
+0) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[FXSpots]  WITH NOCHECK ADD  CONSTRAINT [FK_FXSpots_
+Currencies] FOREIGN KEY([LHSCurrencyID])
REFERENCES [dbo].[Currencies] ([ID])
GO

ALTER TABLE [dbo].[FXSpots] CHECK CONSTRAINT [FK_FXSpots_Currencies]
GO

ALTER TABLE [dbo].[FXSpots]  WITH CHECK ADD  CONSTRAINT [FK_FXSpots_Cu
+rrencies1] FOREIGN KEY([RHSCurrencyID])
REFERENCES [dbo].[Currencies] ([ID])
GO

ALTER TABLE [dbo].[FXSpots] CHECK CONSTRAINT [FK_FXSpots_Currencies1]
GO

and the working table looks like this:

USE [Market_Data_prod]
GO

/****** Object:  Table [dbo].[Indices]    Script Date: 11/20/2012 19:4
+8:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Indices](
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Description] [nvarchar](500) NOT NULL,
    [CurrencyID] [int] NOT NULL,
    [BBTicker] [nvarchar](50) NULL,
    [CRBTicker] [nvarchar](50) NULL,
    [ReutersRic] [nvarchar](50) NULL,
    [CloseTime] [datetime] NULL,
 CONSTRAINT [PK_EquityIndices] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
+ = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 9
+0) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Indices]  WITH NOCHECK ADD  CONSTRAINT [FK_EquityIn
+dices_Currencies] FOREIGN KEY([CurrencyID])
REFERENCES [dbo].[Currencies] ([ID])
GO

ALTER TABLE [dbo].[Indices] CHECK CONSTRAINT [FK_EquityIndices_Currenc
+ies]
GO

[Anonymous Monk]

thanks for the nice effort. However I think it's very strange that you get a result and I don't, hmm very annoying. Anyway thanks once again, I don't have the time to spend more time on this issue ritht now I will do some stuff manually and if I need to access more of that data I will look at it again. Cheers Pär