differences between INSERT and LOAD

Classic Servoy
1

Due to the fact that this forum cant handle strange characters. I made a text-file with my problem…please see attached file.
Thanx in advance…

forumtopic.txt (738 Bytes)

2

The insert works, so your database accepts non-ascii characters.

The load statement specifies format ‘ASCII’ (as opposed to BCP). I guess that only ascii characters are allowed by the load statement.
Did you get the data from a sybase table? In that case you may use BCP for export and import format.

Please note that this import problem is not servoy-related.

Rob

3

rgansevles:
Did you get the data from a sybase table? In that case you may use BCP for export and import format.

I am using Filemaker to generate the text-files (we have an conversiontool in Filemaker which has a lot of intelligence in converting old clients to our Filemaker software and now we are using it for out Servoy software).

At first I started with using INSERTS…but the amount of data is such that I want to use LOAD and ran into this problem

4

rgansevles:
The load statement specifies format ‘ASCII’ (as opposed to BCP). I guess that only ascii characters are allowed by the load statement.

Using BCP didn’t do the trick either :-(
I guess I have to use to a conversion-tables to convert the non-ascii signs

5

tweetie:

rgansevles:
The load statement specifies format ‘ASCII’ (as opposed to BCP). I guess that only ascii characters are allowed by the load statement.

Using BCP didn’t do the trick either :frowning:
I guess I have to use to a conversion-tables to convert the non-ascii signs

This should work fine. I think you are just running into charset conversion issues. What collation does your database use? What charset are you using on the machine where the file and database server reside?
What charset is in use on the machine where you are displaying the result set (if it is a different machine)?

6

jhinsperger:

tweetie:

rgansevles:
The load statement specifies format ‘ASCII’ (as opposed to BCP). I guess that only ascii characters are allowed by the load statement.

Using BCP didn’t do the trick either :-(
I guess I have to use to a conversion-tables to convert the non-ascii signs

This should work fine. I think you are just running into charset conversion issues. What collation does your database use? What charset are you using on the machine where the file and database server reside?
What charset is in use on the machine where you are displaying the result set (if it is a different machine)?

I am using UTF8…and stuff is getting more confusing by the day. I have one database that works fine with Load and non-ascii…and I have a database that doesn’t work fine :shock:

7

If you are using UTF8 in your database file, that explains the problem.
The scenario would be as follows:

  1. The server assumes (because it has no other way of knowing) that your data file is encoded UTF8. However, the data is encoded in charset cp1252 or something similar. So the server stores the cp1252 bytes into the UTF8 database when you use LOAD TABLE.
  2. When you fetch the data from a client application, the server will translate the data from UTF8 to your client character set (again, probably 1252). The problem is the data in the database file is not UTF8, so the translation performed garbles your data.

The basic problem is that when you use LOAD TABLE in 9.0.2, the server assumes that the data in the file is encoded using the same code page as the database. In version 10, the LOAD TABLE (and UNLOAD TABLE) statement has a new option which allows you to specify the encoding of the data file so the server can translate into the database charset correctly when the data is stored in the database.

For 9.0.2, you have a couple of options:

  1. Make sure your datafile is in the UTF8 charset before executing LOAD TABLE.
  2. Load the datafile into a temporary table using LOAD TABLE, and use the csconvert(…) function to translate it from the charset of the file into the database charset and store it permanently.
  3. Use the DBISQL INPUT statement. This will push the data through the client communication layer, and it will be converted from the client charset to the database charset automatically. This would be the same as executing multiple INSERT statements.

I hope this helps. This collation and charset translation can be very confusing. I would encourage you to read the “International Languages and Character Sets” chapter in the SQL Anywhere documentation.

8

jhinsperger:
I hope this helps. This collation and charset translation can be very confusing. I would encourage you to read the “International Languages and Character Sets” chapter in the SQL Anywhere documentation.

Thankx for the info…it is confusing indeed. Especially when I have 2 db’s with UTF8 and both behave differently.
I am gonna try the your suggestions…thankx.