Handling Binary Data

mIRC SQLite

previous page next page

One of the nicest features of mIRC SQLite is its ability to deal with binary data painlessly by interacting with mIRC binary variables.

mIRC SQLite extends the SQLite query language syntax by adding support for mIRC-like binary variables. This means that you can actually use &bvar; syntax inside queries! mIRC SQLite will then correctly translate the binary var to actual binary data and use it in queries. For example:

Code

sqlite_exec %db INSERT INTO binary_data (key, bytes) VALUES ('backup', &bvar;)
sqlite_exec %db UPDATE binary_data SET bytes = &bvar; WHERE key = 'backup'

That's it! Simple and effective.

To read binary data from a database you first need to query it:

Code

var %request = $sqlite_query(%db, SELECT bytes FROM binary_data WHERE key = 'backup')

You have then two ways to fetch it:

sqlite_fetch_row
When you fetch a result with binary data $sqlite_fetch_row will create a binary variable on the specified hash table for the binary field.

Code

; When dealing with big chunks of binary data you should always specify either $SQLITE_NUM or $SQLITE_ASSOC
; If you don't mSQLite needs to take a copy of the same binary data twice, which will consume twice as much memory.
if ($sqlite_fetch_row(%request, row, $SQLITE_ASSOC)) {
; Read the binary data to a binary variable, returns the number of bytes in binary variable
var %size = $hget(row, bytes, &bvar;)
}

sqlite_fetch_single
Fetching binary data with $sqlite_fetch_single is a bit more simple, since it only fetches the first field. To fetch data to a binary variable, just specify the name of the binary variable as second argument. This changes the functionality of sqlite_fetch_single to return the size of the binary variable on success, instead of the column data.

Code

; Returns size of bvar on success
var %size = $sqlite_fetch_single(%request, &bvar;)
if (%size != $null) {
; We now have the binary data in &bvar;
}

That's all for reading binary data into binary variables. Don't forget to free the result with sqlite_free as big chunks of binary data can take quite a bit of memory.

The following example demonstrates how to work with binary data in mSQLite by copying a file. Please note that this example is purely for demonstrating purposes and shouldn't be used to copy files in real scripts, as there are much more simple and efficient ways of doing so. The code is throughly commented so it should be easy to follow.

Example

/*
** mIRC SQLite Demonstration Script
**           Binary data handling
** --------------------------------
** Save as sqlite_binary.mrc and do
** /load -rs sqlite_binary.mrc
**
** This script demonstrates how to
** use binary variables with mSQLite.
**
** Usage:
**  /copyfile source destination
**
** Try with:
**  /copyfile mirc.exe mirc.exe.backup
**
** This simply copies the mIRC.exe file.
** Unpractical as there are better
** ways to copy a file, but it does
** so by writing the binary data to
** a SQLite database first and then
** querying the data from the database.
**
** This example is only for demonstration
** purposes, so support for filenames that
** have spaces in their path is not added.
*/

alias copyfile {
  var %src = $1, %dst = $2
  if (!%src || !%dst) {
    echo 4 -a Error: Too few arguments.
    return
  }

  ; Make sure source file exists
  if (!$isfile(%src)) {
    echo 4 -a Error: File $qt(%src) doesn't exist.
    return
  }

  ; Get the size of the source file and dest dir
  var %size = $file(%src).size, %dir = $nofile(%dst)
  if (!%dir) { %dir = $mircdir }

  ; Make sure it exists
  if (!$isdir(%dir)) {
    echo 4 -a Error: Directory $qt(%dir) doesn't exist.
    return
  }

  ; Make sure source and dest files aren't the same file
  if (%src == %dst) {
    echo 4 -a Error: You can't copy the file to the same file.
    return
  }

  ; All fine, open files database and create table for files, if one doesn't exist
  var %db = $sqlite_open(files.db)
  sqlite_exec %db CREATE TABLE IF NOT EXISTS files (file, size)

  ; Start copy
  echo 3 -a Copying...

  ; Read the contents of the source file to a binary variable and write it to database
  bread %src 0 %size &src;_bytes
  sqlite_exec %db INSERT INTO files (file, size) VALUES ( &src;_bytes , %size )

  ; Make sure there wasn't an error
  if (%sqlite_errno == $SQLITE_OK) {
    ; No errors
    echo 3 -a File written to database.

    ; Get the ID of the file
    var %file_id = $sqlite_last_insert_rowid(%db)

    ; Now let's query the db for the file
    var %sql = SELECT file, size FROM files WHERE ROWID = %file_id
    var %request = $sqlite_query(%db, %sql)
    if (%request) {
      ; No errors
      echo 3 -a File read from database.

      ; Fetch the results
      if ($sqlite_fetch_row(%request, row)) {
        ; Get the bytes to a new binary variable, and make sure the size is same as before
        %size = $hget(row, file, &dst;_bytes)
        if (%size == $hget(row, size)) {
          ; All good! Write the new file. :)
          bwrite %dst 0 %size &dst;_bytes
          echo 3 -a Done!
        }
        else {
          echo 4 -a Error: Size mismatch
        }
      }
      else {
        echo 4 -a Error: %sqlite_errstr
      }

      ; Free the query result
      sqlite_free %request
    }
    else {
      echo 4 -a Error: %sqlite_errstr
    }
  }
  else {
    echo 4 -a Error: %sqlite_errstr
  }

  ; Close db
  sqlite_close %db
}

previous page start next page