How can I insert special characters into Advantage SQL database?

Question

I'm trying to insert characters like µ as varchar but it doesn't work, I get error message which says

cannot convert Unicode characters to Codepage characters

because it's medical document, I'm not allowed to change any data. The program is written in vb.net, it inserts csv data into Advantage SQL so I was trying this function

 Public Shared Function Convert(ByVal input As String) As String
      Dim OriginalCodierung As System.Text.Encoding = System.Text.Encoding.GetEncoding("utf-8")
        Dim iso8859 As System.Text.Encoding = System.Text.Encoding.GetEncoding("iso-8859-1")
        Dim enc As System.Text.Encoding = System.Text.Encoding.Default

       Dim OriginalBytes As Byte() = OriginalCodierung.GetBytes(input)
        Dim result As Byte() = System.Text.Encoding.Convert(OriginalCodierung, iso8859, OriginalBytes)
        Dim s As String = enc.GetString(result)
        Return s

    End Function

which encodes utf8 in iso8859 but it didn't really help.

How can I insert the data with those characters without changing the data?

I'm not familiar with that database but in other databases I've used, `varchar` stores only single-byte characters. In SQL Server for instance, you need to use `nvarchar` for double-byte characters. Does your database have a corresponding data type? If not then I think that you'd have to store the data as bytes rather than as characters. — jmcilhinney, Apr 29 '16 at 10:57
Advantage database Server has only char https://devzone.advantagedatabase.com/dz/webhelp/Advantage8.1/server1/adt_field_types_and_specifications.htm — Sparkm4n, May 02 '16 at 13:42

score 3 · Accepted Answer · answered Apr 29 '16 at 11:18

i don't know if this will answer your question:

Use parametrized queries:

using (SqlCommand com = new SqlCommand("INSERT INTO your_Table (column) " +
                                       "VALUES (@INPUTVALUE)", con))
    {
    com.Parameters.AddWithValue("@INPUTVALUE", @"""%$#@?,.;");
    com.ExecuteNonQuery();
    }

To escape special characters in a LIKE expression you prefix them with an escape character. You get to choose which escape char to use with the ESCAPE keyword. (as MSDN References)

For example this escapes the % symbol, using \ as the escape char:

select * from your_table where yourcolumnfield like '%15\% off%' ESCAPE '\'

If you don't know what characters will be in your string, and you don't want to treat them as wildcards, you can prefix all wildcard characters with an escape char, eg:

set @msearchString = replace(
                replace(
                replace(
                replace(@myString,'\','\\'),
                        '%','\%'),
                        '_','\_'),
                        '[','\[')

(Note that you have to escape your escape char too). Then you can use something like this:

select * from table where yourcolumnfield like '%' + @msearchString + '%' ESCAPE '\'

well, actally it does not really convert These characters but i fortunately found solution, i just had to Change the Encoding to 1252 like here: Dim iso8859 As System.Text.Encoding = System.Text.Encoding.GetEncoding(1252 ) thanks anyway — Sparkm4n, May 02 '16 at 14:18

How can I insert special characters into Advantage SQL database?

1 Answers1