SQL_RESCOLBint problem

[jeroen brouwers]

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

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

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

[Eric Pearson]

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

[jeroen brouwers]

Jeez, thanks. I completely overlooked that one.

Regards
Jeroen