Limitations

mIRC SQLite

previous page next page
Limitations
There are certain limitations for functions and aggregates. These limitations exists in mSQLite because they exist in the underlying software (eg. mIRC and/or SQLite).
  1. The length of the function name in SQLite can be up to 255 bytes.
  2. Max number of arguments that is supported is 127.
  3. Upon executing a query, the user defined function name with all of its arguments can be up to 940 characters. The reason for this is because mIRC can't handle strings longer than this. For most functions this isn't really a problem as 940 characters is a long string. Also note that mSQLite needs to encode special characters of mIRC to ensure that mIRC will properly interpret the function, which can make the query take more characters than expected.
  4. Lastly, you can't pass binary data to the functions safely, as mIRC isn't binary safe.
Fortunately mSQLite provides you a way around 3 and 4. It defines two SQL helper functions that you can use to pass large amounts of data in a binary-safe manner to user defined functions and back to SQLite:
  • TMPARG ( [ data ] )
    Used to pass large amount of data to an user defined function. mSQLite will write the data to a temporary file and then returns the filename to the user defined function. The user defined function can then read and process the contents of the file. The user defined function should not try to delete the file; mSQLite will do so after the query is done executing. If you call this with no arguments an empty temporary file is created.
    Example
    ; process_data is an user defined function that processes bulk of binary data
    var %sql = SELECT process_data(TMPARG(bytes)) FROM binary_data
    var %request = $sqlite_query(%db, %sql)
  • TMPRES ( file )
    Used to return large amount of data from an user defined function. The user defined function should write the return value to a file and then return the filename. mSQLite will then attempt to open the returned file, read its contents and return them. TMPRES will automatically try to find out whether the data in the file is text or binary. If you want to enforce it as a certain type use the CAST expression. If the user defined function creates the file that it returns, it is responsible for deleting it. You can use TMPARG in conjuction with TMPRES by generating an empty temporary file and using it in the user defined function.
    Code
    ; Example 1, using TMPARG to pass data to a function and TMPRES to return it
    ; The user defined function can simply write the return value to the file generated
    ; by TMPARG which was passed to it as first argument and then return the same filename
    sqlite_exec %db UPDATE binary_data SET bytes = TMPRES(process_data(TMPARG(bytes)))

    ; Example 2, using TMPARG to generate an empty temporary file and passing to the
    ; user defined function, which write data to it and then return the same filename
    sqlite_exec %db UPDATE binary_data SET bytes = TMPRES(generate_data(TMPARG()))

    ; Example 3, not using TMPARG at all, thus leaving the responsibility to
    ; delete the file used to return data to the user defined function
    sqlite_exec %db UPDATE binary_data SET bytes = TMPRES(generate_data())
previous page start next page