Fetching more than 64 characters from Snowflake DB

Hi, Has anyone seen this before
I’m using a standard odbc connector as xojo does not have a snowflake connector.

Code:
sqlCommand = "Select * From FE_Connections Where upper(Head_Of_Organization) = ‘FALSE’ "
rows = dbCon.SelectSQL(sqlCommand)

The table FE_Connections is defined as follows:-
create or replace table FE_Connections
(
ODBC_INI_FILE Varchar(500)
,DSN Varchar(200)
,Snowflake_Account Varchar(200)
,Snowflake_Username Varchar(200)
,Snowflake_Password Varchar(200)
,Head_Of_Organization Varchar(10)
,Role_To_Use Varchar(200)
,Database_To_Use Varchar(200)
,Schema_To_Use Varchar(200)
,Virtual_Warehouse Varchar(200)
);

The table has already been populated (via xojo) and when looking at the table in the snowflake interface, the value of the ‘Snowflake_Password’ column is D3D52E0C3F640D4BBEA9361037AB56DF014ADA3B0E2B94C33B57C14A99ED2688

When the above code runs (in debug) is can see that the last byte has been replaced with a hex ‘00’
E.g. (see image)
image

In case there is an issue seeing the image - what I see in debug in hex format, showing the last 4 bytes is
32363800. - in char form its
268.

I think this is a bug in XoJo ODBC driver to snowflake but would love to hear other opinion.

Thanks

Seems some kind of bug related to
1 - String functions with an offset by one error, or
2 - Some string processing, used C type string handling and put null at the end instead of string_size + 1.

The level where the bug is needs to be investigated, Xojo, or deeper, going to the ODBC “snowflake” side.

Hi Rick

Appreciate the reply - I have also raised a case with XoJo about this.
What’s really needed as a decent XoJo → Snowflake odbc driver as the default driver does not work with various features. E.g. Database.TableColumns causes the app to hang.
Snowflake is gaining in popularity and is an amazing cloud only DB.

Cheers

It’s not a bug. String uses chr(0) as a terminator internally when displaying them. Put the data into a memory block for better results.

If you’re the same Ian Fickling who works at Snowflake then it’d be appreciated if you’d call this out as your opinion may be biased…

Hi Greg

With respect - I think this is a bug.
In the DB, the character string is 64 bytes and ends with string ‘2688’
When fetched from the DB, the value retrieved ends with ‘268.’ (in which the dot is hex ‘00’).
If what was fetched ended ‘2688.’, then I guess it would be ok, however this is not the case.

It important to get the entire string as its an encrypted password which I decrypt in the app, at the moment this string does not decrypt to the correct value due the early termination of the string.

Strings shorter in length come back fine and decrypt fine.

You mention putting the data into a data block, as I am fairly new to xojo, please can you expand on what that entails

Thanks
Ian

101 marketing happening as we speak

Which version of xojo are you using and on which os version?

32 or 64 bit?

Have you tried it with a string longer than 64?

Hi Steve

Fair point - My apologies.
My intention was not to market it.

Regards
Ian

Hi Julian

XoJo 64 bit on Max OS.

I am about to do that but have a game of golf first :slight_smile:
I’ll try this on the windows version too - Will update later this afternoon.

Wrong. Greg talked about “memory block”:
http://documentation.xojo.com/api/language/me.htmlmoryBlock

How was your practice ?

With respect - I think this is a bug.
In the DB, the character string is 64 bytes and ends with string ‘2688’
When fetched from the DB, the value retrieved ends with ‘268.’ (in which the dot is hex ‘00’).
If what was fetched ended ‘2688.’, then I guess it would be ok, however this is not the case.

I see. Well, I looked though our bug system and can’t find another place where we’ve got this happening and with a cursory look at the code, I don’t see that we’re adding any null characters when returning strings in an ODBC database.

Maybe a Snowflake bug.

Or, an allocated block of memory (preset with zeros), that should receive 64 chars later and received 63…

Any way, Ian needs a Feedback case providing ways to reproduce the case. But first Ian should prove that the same driver using another known back-end does not lose the final char.

It could be. My suspicion is that this field, whatever type it is, is just returning the raw data that’s put in. If these fields have been filled in using our crypto methods, they all return MemoryBlocks, and it’s certainly possible that they return data followed by a null.

I think we need to see the code that was used to generate and add the record as well as how it’s being pulled out to get a better understanding of what’s going on. If it’s a simple ExecuteSQL call, I’d expect this to work correctly, but if it’s using the RowSet.EditRow method, i bet the data is being coerced into a String and causing this issue.

There’s also the possibility that it’s the ODBC driver itself (as opposed to the plugin). Remember that on macOS you need an additional driver (like the one from Actual Technologies) to connect to an ODBC data source.

1 Like

Hi - I’ve been offline a couple of days.
These are my findings/

  1. Using XoJo Windows
    Driver 64-bit snowflake
    in xojo, using type ODBCDatabase
    Everything works fine.
    63 btye string - ok
    64 byte string - ok
    65 and beyond - ok
    No issues to report

  2. In XoJo Mac
    Driver 64-bit snowflake
    in xojo, using type ODBCDatabase
    I find the following
    63 btye string - ok
    64 byte string - not ok - last byte is x’00’
    65 and beyond - the string is returned as null

We can ignore the code that inserts the row as I have done that (for testing this) in the Snowflake user interface (equivalent of any IDE eg SQL Studio for SQL Server)
Nonetheless this is the code for that

create or replace table FE_Connections
(
  ODBC_INI_FILE        Varchar(500)
 ,DSN                  Varchar(200)
 ,Snowflake_Account    Varchar(200)
 ,Snowflake_Username   Varchar(200)
 ,Snowflake_Password   Varchar(200)
 ,Head_Of_Organization Varchar(10)
 ,Role_To_Use          Varchar(200)
 ,Database_To_Use      Varchar(200)
 ,Schema_To_Use        Varchar(200)
 ,Virtual_Warehouse    Varchar(200)
);



delete from fe_connections;
Insert Into FE_Connections 
( ODBC_INI_FILE                    , DSN      ,Snowflake_Account,  Snowflake_Username , Snowflake_Password , Head_Of_Organization , Role_To_Use  ,Database_To_Use ,Schema_To_Use , Virtual_Warehouse ) 
Values ('/Users/ifickling/odbc.ini','dbMaster','sfpscogs-if_snf_1','IFICKLING'        
        ,'1234567890123456789012345678901234567890123456789012345678901234567890'                  
        ,'FALSE'                   ,'ACCOUNTADMIN', 'MASTER_USAGE','ACCOUNT_USAGE' ,'MASTER_USAGE_WH_ADHOC' );

Select * From FE_Connections Where upper(Head_Of_Organization) = 'FALSE';

Code for testing in XoJo, simply does

// To Connect
app.dbConSF = new ODBCDatabase()
app.dbConSF.DataSource = "SF_CAS2"
app.dbConSF.UserName = "IFICKLING"
app.dbConSF.Password = "xxxxxxx"


Try
  app.dbConSF.Connect
  MessageBox("Connected OK")
Catch error As DatabaseException
  MessageBox("Failed to connect" + error.Message)
  // Connection error
End Try


//To Fetch 
var sqlCommand  As String
var rows As RowSet
sqlCommand = "USE ROLE MASTER_USAGE_SYSADMIN"
app.dbConSF.ExecuteSQL(sqlCommand)

sqlCommand = "USE DATABASE MASTER_USAGE"
app.dbConSF.ExecuteSQL(sqlCommand)

sqlCommand = "USE SCHEMA ACCOUNT_USAGE"
app.dbConSF.ExecuteSQL(sqlCommand)

sqlCommand = "Select * from FE_Connections"
rows = app.dbConSF.SelectSQL(sqlCommand)

Experimenting with different string lengths on the initial insert,
immediately, after the SelectSQL in debug, you can see in the result I provided above.

Possible Conclusion:

  1. bug in snowflake odbc driver for mac.
    However this has not been reported for other tools using mac odbc driver.

  2. bug in Xojo for mac

I cant really drill any deeper than that, any help from here would be appreciated.

@Greg_O_Lone - you mention using a driver such as those provided by Actual Technologies.
They also dont provide a snowflake odbc driver. Snowflake provide on odbc driver.
On the mac, to connect, one has to set environment variable
E.g. System.EnvironmentVariable(“ODBCINI”) =

In the odbc ini file you then specify the actual location of the driver.
e.g
[ODBC Data Sources]
MasterUsage = Snowflake
[MasterUsage]
server = aws_cas2.snowflakecomputing.com
Driver = /opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib
uid = IFICKLING
role = AWS_CAS2_DBUSAGE_SYSADMIN
database = AWS_CAS2_DBUSAGE
schema = ACCOUNT_ADMIN
warehouse = AWS_CAS2_DBUSAGE_WH_ADHOC

In windows you configure the connection using odbc editor.

Regards
Ian

ODBC Manager, free, to avoid such manual tasks. Not the Driver per se.

http://www.odbcmanager.net/

Edit: Guess what? You guys recommend it!

https://docs.snowflake.com/en/user-guide/odbc-mac.html#odbc-manager-optional-recommended

Hi Rick

I was just looking into that as we speak.
Please don’t refer to ‘me’ as Snowflake.

I wish I was good enough to have invented it but I am only an employee, not the inventor. :slight_smile:

Thanks

I wasn’t. I was referring you as part of the team. That’s what I meant by “you guys”.

Fair dinkum.
I say that because we have strict policy over misrepresentation - I am not authorised to represent Snowflake in such a forum. I work there as a solution architect but still love to code.
I’m using xojo to develop some utilities that we could use internally

Well welcome to the Xojo community, I’m sure some folks would love to see a plugin to help access Snowflake DB in a more reliable manner than ODBC!

1 Like