Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

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

by karlgoethebier (Vicar)
on Nov 20, 2012 at 17:46 UTC ( #1004767=note: print w/ replies, xml ) Need Help??


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

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.

«The Crux of the Biscuit is the Apostrophe»


Comment on Re: DBI don't fetch my data...or at least not all records
Download Code
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
    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

      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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (9)
As of 2015-07-05 21:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (68 votes), past polls