Announcement

Collapse
No announcement yet.

SQL_RESCOLBint problem

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL_RESCOLBint problem

    Hello

    I’ve got a question I cannot find online anywhere.

    I’m using SQL_RESULTCOLUMNBint to retrieve a SQL Big Int field and I’m trying to store them in a Powerbasic Quad field.
    I’m getting strange values which show up in SQL_RESULTCOLUMNText as [ Chr$(236) ][ CHR$(147) ][ CHR$(2) ] with a bunch of chr$(0), or variations on this.

    I’m using SQL Server 2008 with SQL server native client 10.0 (which I would expect to have the proper support for big Int) and SQL Tools 2.1. I’m having the same problem as http://www.powerbasic.com/support/pb...SQL_RESCOLBint but I don’t know how to get the odbc info on this and I'm not using MySQL.


    Strangely, I do NOT have this problem when I get a new sessionID using a statement with the @@Identity when I insert a NEW user or session (since the current lookup failed).

    Did anyone have this problem already or maybe knows where to look for… ??

    Below you'll find the test program showing the error and the @@identity (which succeeds) and the table design.


    Many regards

    Jeroen


    Code:
    FUNCTION PBMAIN () AS LONG
    
        IF NOT SQL_OKAY( SQL_Authorize( %MY_SQLT_AUTHCODE) ) THEN   MSGBOX "Authorize failed" : EXIT FUNCTION
        IF NOT SQL_OKAY( SQL_Init ) THEN    MSGBOX "Authorize failed" : EXIT FUNCTION
    
        IF SQL_OKAY( SQL_OpenDB( "DRIVER=sql server native client 10.0;SERVER=ELI11B\SQL08;UID=" & $SQL_MYSTNDUID & ";PWD=" & $SQL_MYSTNDPWD & ";DATABASE=EURAIL;" ) ) THEN
    
    '        SQL_SetOptionSInt
            IF SQL_OKAY( SQL_Stmt( %SQL_STMT_IMMEDIATE, "Select UserID From Log_Sessions where SessionID = 142194" ) ) THEN
                MSGBOX "colcount=" & STR$( SQL_RESCOLCOUNT )        ' =1
                SQL_Fetch %FIRST_ROW
                MSGBOX SQL_ResColText(1)                            ' just characters like [ CHR$(232) ][ CHR$(147) ]
                MSGBOX SQL_ResColBInt(1)                            ' now in letters.. not a number!!
                MSGBOX SQL_ResColSInt(1)                            ' 
    
                MSGBOX SQL_ERRORQUICKALL                            ' just the regular: changed db to eurail; change language to English.. that's it.
    
    
                IF SQL_OKAY( SQL_Stmt( %SQL_STMT_IMMEDIATE, "insert into Log_Sessions ( ChannelID, ProfileID, UserID, UserInput, LogLevel,  ReplaceString, DialogIn, Prefix) values ( 1, 1, 168945, 'second question to test session', 0, ' ', ' ', ' ' )") ) THEN
                    
                    MSGBOX "colcount=" & STR$( SQL_RESCOLCOUNT )        ' =0
                    
                    IF SQL_OKAY( SQL_Stmt( %SQL_STMT_IMMEDIATE, "select @@identity " ) ) THEN
                        SQL_Fetch %FIRST_ROW
                        MSGBOX SQL_ResColBInt(1)                            ' PROPER NUMBER!!!
                        
                    ELSE
                        MSGBOX "Stmt @@ident failed"
                    END IF
                ELSE
                    MSGBOX "Stmt insert failed"
                END IF
            ELSE
                MSGBOX "Stmt Open failed"
            END IF
        ELSE
            MSGBOX "DB Open failed"
        END IF
    
    END FUNCTION

    Code:
    USE [EURAIL]
    GO
    
    /****** Object:  Table [dbo].[Log_Sessions]    Script Date: 08/20/2009 15:05:00 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[Log_Sessions](
    	[SessionID] [bigint] IDENTITY(1,1) NOT NULL,
    	[UserID] [bigint] NOT NULL,
    	[LogDate] [datetime] NOT NULL,
    	[UserInput] [char](1024) NOT NULL,
    	[LogLevel] [int] NULL,
    	[ReplaceString] [char](50) NULL,
    	[DialogIn] [char](50) NOT NULL,
    	[Prefix] [char](50) NULL,
    	[ChannelID] [smallint] NOT NULL,
    	[ProfileID] [smallint] NOT NULL,
     CONSTRAINT [PK_Log_Sessions] PRIMARY KEY CLUSTERED 
    (
    	[SessionID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING ON
    GO
    
    ALTER TABLE [dbo].[Log_Sessions] ADD  CONSTRAINT [DF_Log_Sessions_UserID]  DEFAULT ((0)) FOR [UserID]
    GO
    
    ALTER TABLE [dbo].[Log_Sessions] ADD  CONSTRAINT [DF_Log_Sessions_LogDate]  DEFAULT (getdate()) FOR [LogDate]
    GO
    
    ALTER TABLE [dbo].[Log_Sessions] ADD  CONSTRAINT [DF_Log_Sessions_Printinfo]  DEFAULT ((0)) FOR [LogLevel]
    GO
    
    ALTER TABLE [dbo].[Log_Sessions] ADD  CONSTRAINT [DF__Log_Sessi__Chann__0CA6A535]  DEFAULT ((0)) FOR [ChannelID]
    GO
    
    ALTER TABLE [dbo].[Log_Sessions] ADD  CONSTRAINT [DF__Log_Sessi__Profi__0D9AC96E]  DEFAULT ((0)) FOR [ProfileID]
    GO

  • #2
    Jeroen --

    SQL_ResultColumnBInt returns an 8-character string that can (almost always) be converted to a QUAD value by using PowerBASIC's CVQ function.

    -- Eric Pearson, Perfect Sync Software
    "Not my circus, not my monkeys."

    Comment


    • #3
      Jeez, thanks. I completely overlooked that one.

      Regards
      Jeroen

      Comment

      Working...
      X