The mystery of lost values: Understanding ASCII vs. UTF-8 in Database Queries

The mystery of lost values: Understanding ASCII vs. UTF-8 in Database Queries

Understanding ASCII vs. UTF-8 in Database Queries: A Practical Guide


When dealing with databases, understanding how different character encodings impact queries is crucial. Two common encoding standards are ASCII and UTF-8. This blog post delves into their differences, how they affect case-sensitive queries, and provides practical examples to illustrate these concepts.

ASCII vs. UTF-8: What’s the Difference?


ASCII (American Standard Code for Information Interchange)


  • Description: A character encoding standard using 7 bits to represent each character, allowing for 128 unique symbols. These include control characters (like newline), digits, uppercase and lowercase English letters, and some special symbols.
  • Range: 0 to 127.


UTF-8 (8-bit Unicode Transformation Format)


  • Description: A variable-width character encoding capable of encoding all 1,112,064 valid character code points in Unicode using one to four 8-bit bytes. UTF-8 is backward compatible with ASCII.
  • Range: Can represent characters in a much wider range, including all characters in all languages, as well as many symbols and special characters.


ASCII and UTF-8 Position Examples


Let’s compare the positions of some characters in both ASCII and UTF-8:

Character ASCII Position UTF-8 Position
A 65 65
B 66 66
Y 89 89
Z 90 90
[ 91 91
\ 92 92
] 93 93
^ 94 94
_ 95 95
` 96 96
a 97 97
b 98 98
y 121 121
z 122 122
Last ASCII (DEL) 127 127
ÿ Not present 195 191 (2 bytes)

Case Sensitivity in Database Queries


Case sensitivity can significantly impact database queries, as different encoding schemes represent characters differently.


ASCII Example


-- Case-sensitive query in ASCII-encoded database
SELECT * FROM users WHERE username = 'Alice';
-- This will not return rows with 'alice', 'ALICE', etc.

UTF-8 Example


-- Case-sensitive query in UTF-8 encoded database
SELECT * FROM users WHERE username = 'Ålice';
-- This will not return rows with 'ålice', 'ÅLICE', etc.

Practical Example with Positions


For ASCII, the characters included in the range >= 'A' and <= 'z' are:

  • A has a position of 65.
  • a has a position of 97.

In a case-sensitive search, these positions are distinct, so A is not equal to a.

For UTF-8, the characters included in this range are the same since UTF-8 is backward compatible with ASCII for characters in this range.


Query Example


Let’s demonstrate a query example for usernames within the range >= 'A' and <= 'z'.

-- Query for usernames in the range 'A' to 'z'
SELECT * FROM users WHERE username >= 'A' AND username <= 'z';

Included Characters


Based on the ASCII positions, the range >= 'A' and <= 'z' includes:

  • All uppercase letters: A to Z (positions 65 to 90)
  • Special characters: [, \, ], ^, _, and ` (positions 91 to 96)
  • All lowercase letters: a to z (positions 97 to 122)

Practical Example with Positions


Given the following table:

-- Create a table
    username VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin

-- Insert some users
INSERT INTO users (id, username) VALUES (1, 'Alice');   -- A = 65, l = 108, i = 105, c = 99, e = 101
INSERT INTO users (id, username) VALUES (2, 'alice');   -- a = 97, l = 108, i = 105, c = 99, e = 101
INSERT INTO users (id, username) VALUES (3, 'Ålice');   -- Å = 195 133, l = 108, i = 105, c = 99, e = 101
INSERT INTO users (id, username) VALUES (4, 'ålice');   -- å = 195 165, l = 108, i = 105, c = 99, e = 101
INSERT INTO users (id, username) VALUES (5, 'Z');       -- Z = 90
INSERT INTO users (id, username) VALUES (6, 'z');       -- z = 122
INSERT INTO users (id, username) VALUES (7, 'ÿ');       -- ÿ = 195 191
INSERT INTO users (id, username) VALUES (8, '_special');-- _ = 95, s = 115, p = 112, e = 101, c = 99, i = 105, a = 97, l = 108
INSERT INTO users (id, username) VALUES (9, 'example'); -- e = 101, x = 120, a = 97, m = 109, p = 112, l = 108, e = 101

Query Execution


-- Execute the query
SELECT * FROM users WHERE username >= 'A' AND username <= 'z';

Query Result


This query will include the following usernames based on the range:

  • Alice (A = 65, l = 108, i = 105, c = 99, e = 101)
  • Z (Z = 90)
  • example (e = 101, x = 120, a = 97, m = 109, p = 112, l = 108, e = 101)
  • _special (_ = 95, s = 115, p = 112, e = 101, c = 99, i = 105, a = 97, l = 108)
  • alice (a = 97, l = 108, i = 105, c = 99, e = 101)
  • z (z = 122)

However, it will not include:

  • Ålice (Å = 195 133, l = 108, i = 105, c = 99, e = 101, outside the specified range)
  • ålice (å = 195 165, l = 108, i = 105, c = 99, e = 101, outside the specified range)
  • ÿ (ÿ = 195 191, outside the specified range)



Understanding the differences between ASCII and UTF-8 character positions and ranges is crucial when performing case-sensitive queries in databases. For example, querying for usernames within the range >= 'A' and <= 'z' will include a specific set of characters based on their ASCII positions, impacting which rows are returned in your query results.

By grasping these concepts, you can ensure your database queries are accurate and efficient, especially when dealing with different encoding schemes.

The Shift Towards Object Identifiers (OIDs):Why Compound Keys in Database Tables Are No Longer Valid

The Shift Towards Object Identifiers (OIDs):Why Compound Keys in Database Tables Are No Longer Valid

Why Compound Keys in Database Tables Are No Longer Valid




In the realm of database design, compound keys were once a staple, largely driven by the need to adhere to normalization forms. However, the evolving landscape of technology and data management calls into question the continued relevance of these multi-attribute keys. This article explores the reasons why compound keys may no longer be the best choice and suggests a shift towards simpler, more maintainable alternatives like object identifiers (OIDs).


The Case Against Compound Keys


Complexity in Database Design


  • Normalization Overhead: Historically, compound keys were used to satisfy normalization requirements, ensuring minimal redundancy and dependency. While normalization is still important, the rigidity it imposes can lead to overly complex database schemas.
  • Business Logic Encapsulation: When compound keys include business logic, they can create dependencies that complicate data integrity and maintenance. Changes in business rules often necessitate schema alterations, which can be cumbersome.

Maintenance Challenges


  • Data Integrity Issues: Compound keys can introduce challenges in maintaining data integrity, especially in large and complex databases. Ensuring the uniqueness and consistency of multi-attribute keys can be error-prone.
  • Performance Concerns: Queries involving compound keys can become less efficient, as indexing and searching across multiple columns can be more resource-intensive compared to single-column keys.


The Shift Towards Object Identifiers (OIDs)


Simplified Design


  • Single Attribute Keys: Using OIDs as primary keys simplifies the schema. Each row can be uniquely identified by a single attribute, making the design more straightforward and easier to understand.
  • Decoupling Business Logic: OIDs help in decoupling the business logic from the database schema. Changes in business rules do not necessitate changes in the primary key structure, enhancing flexibility.


Easier Maintenance


  • Improved Data Integrity: With a single attribute as the primary key, maintaining data integrity becomes more manageable. The likelihood of key conflicts is reduced, simplifying the validation process.
  • Performance Optimization: OIDs allow for more efficient indexing and query performance. Searching and sorting operations are faster and less resource-intensive, improving overall database performance.


Revisiting Normalization


Historical Context


  • Storage Constraints: Normalization rules were developed when data storage was expensive and limited. Reducing redundancy and optimizing storage was paramount.
  • Modern Storage Solutions: Today, storage is relatively cheap and abundant. The strict adherence to normalization may not be as critical as it once was.

Balancing Act


  • De-normalization for Performance: In modern databases, a balance between normalization and de-normalization can be beneficial. De-normalization can improve performance and simplify query design without significantly increasing storage costs.
  • Practical Normalization: Applying normalization principles should be driven by practical needs rather than strict adherence to theoretical models. The goal is to achieve a design that is both efficient and maintainable.

ORM Design Preferences


Object-Relational Mappers (ORMs)


  • Design with OIDs in Mind: Many ORMs, such as XPO from DevExpress, were originally designed to work with OIDs rather than compound keys. This preference simplifies database interaction and enhances compatibility with object-oriented programming paradigms.
  • Support for Compound Keys: Although these ORMs support compound keys, their architecture and default behavior often favor the use of single-column OIDs, highlighting the practical advantages of simpler key structures in modern application development.



The use of compound keys in database tables, driven by the need to fulfill normalization forms, may no longer be the best practice in modern database design. Simplifying schemas with object identifiers can enhance maintainability, improve performance, and decouple business logic from the database structure. As storage becomes less of a constraint, a pragmatic approach to normalization, balancing performance and data integrity, becomes increasingly important. Embracing these changes, along with leveraging ORM tools designed with OIDs in mind, can lead to more robust, flexible, and efficient database systems.

Getting Started with Stratis Blockchain Development Quest: Running Your First Stratis Node

Getting Started with Stratis Blockchain Development Quest: Running Your First Stratis Node

Getting Started with Stratis Blockchain Development: Running Your First Stratis Node

Stratis is a powerful and flexible blockchain development platform designed to enable businesses and developers to build, test, and deploy blockchain applications with ease. If you’re looking to start developing for the Stratis blockchain, the first crucial step is to run a Stratis node. This article will guide you through the process, providing a clear and concise roadmap to get your development journey underway.

Introduction to Stratis Blockchain

Stratis offers a blockchain-as-a-service (BaaS) platform, which simplifies the development, deployment, and maintenance of blockchain solutions. Built on a foundation of the C# programming language and the .NET framework, Stratis provides an accessible environment for developers familiar with these technologies. Key features of Stratis include smart contracts, sidechains, and full node capabilities, all designed to streamline blockchain development and integration.

Why Run a Stratis Node?

Running a Stratis node is essential for several reasons:

  • Network Participation: Nodes form the backbone of the blockchain network, validating and relaying transactions.
  • Development and Testing: A local node provides a controlled environment for testing and debugging blockchain applications.
  • Decentralization: By running a node, you contribute to the decentralization and security of the Stratis network.


Before setting up a Stratis node, ensure you have the following:

  • A computer with a modern operating system (Windows, macOS, or Linux).
  • .NET Core SDK installed.
  • Sufficient disk space (at least 10 GB) for the blockchain data.
  • A stable internet connection.

Step-by-Step Guide to Running a Stratis Node

1. Install .NET Core SDK

First, install the .NET Core SDK, which is necessary to run the Stratis Full Node. You can download it from the official .NET Core website. Follow the installation instructions for your specific operating system. I recommend having all DotNetCore SDKs because the source code for most of the Stratis solutions target really an old framework version like.NET Core 2.1 so it’s better to have multiple choices of framework in case you need to re-target for compatibility

.NET Core Versions

  • .NET Core 3.1 (LTS)
  • .NET Core 3.0
  • .NET Core 2.2
  • .NET Core 2.1 (LTS)
  • .NET Core 2.0
  • .NET Core 1.1
  • .NET Core 1.0

Installation Links

Download .NET Core SDKs

2. Clone the Stratis Full Node Repository

Next, clone the Stratis Full Node repository from GitHub. Open a terminal or command prompt and run the following command:

git clone

This command will download the latest version of the Stratis Full Node source code to your local machine.

3. Build the Stratis Full Node

Navigate to the directory where you cloned the repository:

cd StratisFullNode

Now, build the Stratis Full Node using the .NET Core SDK:

dotnet build

This command compiles the source code and prepares it for execution.

4. Run the Stratis Full Node

Once the build process is complete, you can start the Stratis Full Node. Use the following command to run the node:

cd Stratis.StraxD
dotnet run -testnet

This will initiate the Stratis node, which will start synchronizing with the Stratis blockchain network.

5. Verify Node Synchronization

After starting the node, you need to ensure it is synchronizing correctly with the network. You can check the node’s status by visiting the Stratis Full Node’s API endpoint in your web browser:


here is more information about the possible ports for the API depending on which network you want to use (test or main) and which command did you use to start up the API

To run the API in a specific port you can use the following code

StraxTest (dotnet run -testnet -apiport=38221)






You should see a JSON response indicating the node’s current status, including its synchronization progress.


Congratulations! You have successfully set up and run your first Stratis node. This node forms the foundation for your development activities on the Stratis blockchain. With your node up and running, you can now explore the various features and capabilities of the Stratis platform, including deploying smart contracts, interacting with sidechains, and building blockchain applications.

As you continue your journey, remember that the Stratis community and its comprehensive documentation are valuable resources. Engage with other developers, seek guidance, and contribute to the growing ecosystem of Stratis-based solutions. Happy coding!


Previous articles

Discovering the Simplicity of C# in Blockchain Development with Stratis | Joche Ojeda


Discovering the Simplicity of C# in Blockchain Development with Stratis

Discovering the Simplicity of C# in Blockchain Development with Stratis


Blockchain technology has revolutionized various industries by providing a decentralized and secure way to manage data and transactions. At the heart of this innovation are smart contracts—self-executing contracts with the terms directly written into code. My journey into blockchain development began with the excitement of these possibilities, but it also came with challenges, particularly with the Solidity programming language. However, everything changed when I discovered the Stratis platform, which supports smart contracts using C#, making development much more accessible for me. In this article, I’ll share my experiences, challenges, and the eventual breakthrough that came with Stratis.

Challenges with Solidity

Solidity is the most popular language for writing smart contracts on Ethereum, but it has a steep learning curve. My background in programming didn’t include a lot of JavaScript-like languages, so adapting to Solidity’s syntax and concepts was daunting. The process of writing, testing, and deploying smart contracts often felt cumbersome. Debugging was a particular pain point, with cryptic error messages and a lack of mature tooling compared to more established programming environments.

The complexity and frustration of dealing with these issues made me seek an alternative that could leverage my existing programming skills. I wanted a platform that was easier to work with and more aligned with languages I was already comfortable with. This search led me to discover Stratis.

Introduction to Stratis

Stratis is a blockchain development platform designed to meet the needs of enterprises and developers by offering a simpler and more efficient way to build blockchain solutions. What caught my attention was its support for C#—a language I was already proficient in. Stratis allows developers to create smart contracts using C#, integrating seamlessly with the .NET ecosystem.

This discovery was a game-changer for me. The prospect of using a familiar language in a robust development environment like Visual Studio, combined with the powerful features of Stratis, promised a much smoother and more productive development experience.

Why Stratis Stood Out

The primary benefit of using C# over Solidity is the familiarity and maturity of the development tools. With C#, I could leverage the rich ecosystem of libraries, tools, and frameworks available in the .NET environment. This not only sped up the development process but also reduced the time spent on debugging and testing.

Stratis offers a comprehensive suite of tools designed to simplify blockchain development. The Stratis Full Node, for instance, provides a fully functional blockchain node that can be easily integrated into existing applications. Additionally, Stratis offers a smart contract template for Visual Studio, making it straightforward to start building and deploying smart contracts.

Another significant advantage is the support and community around Stratis. The documentation is thorough, and the community is active, providing a wealth of resources and assistance for developers at all levels.


Transitioning from Solidity to Stratis was a pivotal moment in my blockchain development journey. The challenges I faced with Solidity were mitigated by the ease and familiarity of C#. Stratis provided a robust and efficient platform that significantly improved my development workflow.

In the next article, I will dive into the practical steps of setting up the Stratis development environment. We’ll cover everything you need to get started, from installing the necessary tools to configuring your first Stratis Full Node. Stay tuned for a detailed guide that will set the foundation for your journey into C# smart contract development.

Solid Nirvana: The Ephemeral State of SOLID Code

Solid Nirvana: The Ephemeral State of SOLID Code

The Ephemeral State of SOLID Code: Capturing the Perfect Snapshot

In the world of software development, the SOLID principles are often upheld as the gold standard for designing maintainable and scalable code. These principles — Single Responsibility, Open/Closed, Liskov Substitution, Interface Segregation, and Dependency Inversion — form the bedrock of robust object-oriented design. However, achieving a state where code fully adheres to these principles is a fleeting moment, much like capturing a perfect snapshot in time.

What Does It Mean for Code to Be in a SOLID State?

A SOLID state in source code is a condition where the code perfectly aligns with all five SOLID principles. This means:

  • Single Responsibility Principle (SRP): Every class has one, and only one, reason to change.
  • Open/Closed Principle (OCP): Software entities should be open for extension but closed for modification.
  • Liskov Substitution Principle (LSP): Subtypes must be substitutable for their base types.
  • Interface Segregation Principle (ISP): No client should be forced to depend on methods it does not use.
  • Dependency Inversion Principle (DIP): Depend on abstractions, not concretions.

In this state, the codebase is a model of clarity, flexibility, and robustness. But this state is inherently transient.

The Moment of SOLID Perfection

The reality of software development is that code is in a constant state of flux. New features are added, bugs are fixed, and refactoring is a continuous process. During these periods of active development, maintaining perfect adherence to SOLID principles is challenging. The code may temporarily violate one or more principles as developers refactor or introduce new functionality.

The truly SOLID state can thus be seen as a snapshot — a moment frozen in time when the code perfectly adheres to all five principles. This moment typically occurs:

  • Post-Refactoring: After a significant refactoring effort, where the focus has been on aligning the code with SOLID principles.
  • Before Major Changes: Just before starting a new major feature or overhaul, the existing codebase might be in a perfect SOLID state.
  • Code Reviews: Following a rigorous code review process, where adherence to SOLID principles is explicitly checked and enforced.
  • Milestone Deliveries: Before delivering a major milestone or release, when the code is thoroughly tested and cleaned up.

The Nature of Active Development

Active development is a chaotic process. As new requirements emerge and priorities shift, developers might temporarily sacrifice adherence to SOLID principles for the sake of rapid progress or to meet deadlines. This is a natural part of the development cycle. The key is to recognize that while the code may deviate from these principles during active development, the goal is to continually steer it back towards a SOLID state.

The SOLID State as Nirvana

Achieving a perfect SOLID state can be likened to reaching nirvana — an ideal that is almost impossible to fully attain. Just as nirvana represents a state of ultimate peace and enlightenment, a perfectly SOLID codebase represents the pinnacle of software design. However, this state is incredibly difficult to reach and even harder to maintain. Therefore, it is more practical to view adherence to SOLID principles as a spectrum rather than a binary state.

Measuring SOLID Adherence

Instead of aiming for an elusive perfect state, it’s more pragmatic to measure adherence to SOLID principles using metrics. Tools and techniques can help quantify how well your code aligns with each principle, providing a percentage that reflects its current state. These metrics can include:

  • Class Responsibility: Assessing the number of responsibilities each class has to evaluate adherence to SRP.
  • Change Impact Analysis: Measuring the extent to which modifications to the code require changes in other parts of the system, reflecting adherence to OCP.
  • Subtype Tests: Ensuring subclasses can replace their base classes without altering the correctness of the program, in line with LSP.
  • Interface Utilization: Analyzing the usage of interfaces to ensure they are not overly broad, adhering to ISP.
  • Dependency Metrics: Evaluating dependencies between high-level and low-level modules, supporting DIP.

By regularly measuring these metrics, developers can maintain a clear view of how their code is evolving in relation to SOLID principles. This approach allows for continuous improvement and helps teams prioritize refactoring efforts where they are most needed.

Embracing the Snapshot

Understanding that a perfectly SOLID state is a temporary snapshot can help developers maintain a healthy perspective. It’s crucial to strive for SOLID principles as a guiding star but also to accept that deviations are part of the journey. Regular refactoring sessions, continuous integration practices, and diligent code reviews are essential practices to frequently bring the code back to a SOLID state.


In conclusion, a SOLID state of source code is a valuable but ephemeral achievement, akin to reaching nirvana in the realm of software development. It represents a moment of perfection in the ongoing evolution of a software project. By recognizing this, developers can better manage their expectations and maintain a balance between striving for perfection and the practical realities of software development. Embrace the snapshot of SOLID perfection when it occurs, but also understand that the true measure of a healthy codebase is its ability to evolve while frequently realigning with these timeless principles, using metrics and percentages to guide the way.