Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

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

by kurreburre (Acolyte)
on Nov 20, 2012 at 18:17 UTC ( #1004770=note: print w/ replies, xml ) Need Help??


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

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


Comment on Re^2: DBI don't fetch my data...or at least not all records
Re^3: DBI don't fetch my data...or at least not all records
by karlgoethebier (Curate) on Nov 20, 2012 at 18:41 UTC

    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

    «The Crux of the Biscuit is the Apostrophe»

      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
      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

        As tye wrote: "Most likely a bug in DBD::ODBC, but that is just a guess". What about updating DBI and DBD::ODBC? Regards, Karl

        «The Crux of the Biscuit is the Apostrophe»

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1004770]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (14)
As of 2014-11-28 13:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (197 votes), past polls