PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

Additional characters in databases PostgreSQL, SQLite

https://www.purebasic.fr/english/viewtopic.php?t=57416

Page 2 of 2

Re: [Done] Additional characters in databases PostgreSQL, SQ

Posted: Thu Aug 13, 2015 1:48 pm
by Fred
It's because we store everything in the database as UTF-8, so if you change your executable format from ASCII to Unicode, you won't have issue. To be honest, you need to use the PB unicode mode if you want to do serious database development, as ASCII is just calling for a lot of issue.

Re: [Done] Additional characters in databases PostgreSQL, SQ

Posted: Tue Aug 25, 2015 9:24 am
by Psych
A little bit disappointed that this is marked as 'done'.
I dont think it is done at all.
I'm just running into this problem reading utf8 strings from a database. Coming across this thread is fortuitous.
What is wrong with simply reading the bytes and putting those bytes into the string? Its the developers responsibility to display/convert utf strings as its always been, afterall, a utf ascii string is just an ascii string.
So basically I have to have to convert and recompile everything, libraries, source, userlibs, includes all to work with unicode now if I want to use databases?
I am baffled why pb would go backwards and break existing functionality, I can only assume that the database functions were convoluted to accommodate unicode, converting utf to wide chars and such, and replacing utf characters with the dreaded '?' mark when running in ascii mode, its its ascii mode, just dont convert anything, give us the bytes as they are, please.

Re: [Done] Additional characters in databases PostgreSQL, SQ

Posted: Tue Aug 25, 2015 11:09 am
by Fred
I will see to add a #PB_Ascii flag to GetDatabaseString() so it would return ascii only chars.

Re: Additional characters in databases PostgreSQL, SQLite

Posted: Thu Aug 27, 2015 6:31 pm
by Psych
That would be most helpful, thanks.

Re: Additional characters in databases PostgreSQL, SQLite

Posted: Fri Apr 22, 2016 1:32 pm
by lenski
To see which characters PureBasic gives to the SQLite-database (PostgreSQL is the same)

Code: Select all

Define char$, i.w, string.s=""
Define DatabaseFile.s = GetCurrentDirectory()+"Database.sqlite"

UseSQLiteDatabase()

OpenWindow(0, 0, 0, 300, 300, "", #PB_Window_SystemMenu)
StringGadget(1, 10, 10, 200, 20, "")
EditorGadget(2, 10, 40, 200, 100)

If OpenDatabase(0, DatabaseFile, "", "") = 0
  If CreateFile(0, DatabaseFile) = 0
    End
  EndIf  
  CloseFile(0)
  If OpenDatabase(0, DatabaseFile, "", "") = 0
    End
  EndIf
  DatabaseUpdate(0, "CREATE TABLE test (name TEXT)")
  For i=128 To 255
    string + Chr(i)
  Next 
  DatabaseUpdate(0, "INSERT INTO test (name) VALUES ('"+string+"')")
EndIf  

If DatabaseQuery(0, "SELECT * FROM test")
   While NextDatabaseRow(0)
      string = GetDatabaseString(0, 0)
      SetGadgetText(1, string)
      SetGadgetText(2, string)
      Debug string+Chr(10)
      Debug "write"+Chr(9)+"char"+Chr(9)+"read"+Chr(10)
      For i=1 To 128
        char$ = Mid(string, i, 1)
        Debug Str(i+127)+Chr(9)+char$+Chr(9)+Asc(char$)
      Next
    Wend
   FinishDatabaseQuery(0)
EndIf

CloseDatabase(0)

Repeat : Until WaitWindowEvent() = #PB_Event_CloseWindow
Delete the file Database.sqlite (if exists)
1. Compile/run the code with ASCII (write to the SQLite-database = first run).

The output of the program
when writing with ASCII and reading with ASCII:

Code: Select all

write	char	read

128	?	63
129		129
130	?	63
131	?	63
132	?	63
133	?	63
134	?	63
135	?	63
136	?	63
137	?	63
138	?	63
139	?	63
140	?	63
141		141
142	?	63
143		143
144		144
145	?	63
146	?	63
147	?	63
148	?	63
149	?	63
150	?	63
151	?	63
152	?	63
153	?	63
154	?	63
155	?	63
156	?	63
157		157
158	?	63
159	?	63
160	 	160
161	¡	161
162	¢	162
163	£	163
164	¤	164
165	¥	165
166	¦	166
167	§	167
168	¨	168
169	©	169
170	ª	170
171	«	171
172	¬	172
173	­	173
174	®	174
175	¯	175
176	°	176
177	±	177
178	²	178
179	³	179
180	´	180
181	µ	181
182	¶	182
183	·	183
184	¸	184
185	¹	185
186	º	186
187	»	187
188	¼	188
189	½	189
190	¾	190
191	¿	191
192	À	192
193	Á	193
194	Â	194
195	Ã	195
196	Ä	196
197	Å	197
198	Æ	198
199	Ç	199
200	È	200
201	É	201
202	Ê	202
203	Ë	203
204	Ì	204
205	Í	205
206	Î	206
207	Ï	207
208	Ð	208
209	Ñ	209
210	Ò	210
211	Ó	211
212	Ô	212
213	Õ	213
214	Ö	214
215	×	215
216	Ø	216
217	Ù	217
218	Ú	218
219	Û	219
220	Ü	220
221	Ý	221
222	Þ	222
223	ß	223
224	à	224
225	á	225
226	â	226
227	ã	227
228	ä	228
229	å	229
230	æ	230
231	ç	231
232	è	232
233	é	233
234	ê	234
235	ë	235
236	ì	236
237	í	237
238	î	238
239	ï	239
240	ð	240
241	ñ	241
242	ò	242
243	ó	243
244	ô	244
245	õ	245
246	ö	246
247	÷	247
248	ø	248
249	ù	249
250	ú	250
251	û	251
252	ü	252
253	ý	253
254	þ	254
255	ÿ	255
2. Compile/run the code with UNICODE (read the database = without deleting before)

The output of the program
when writing with ASCII and reading with UNICODE:

Code: Select all

write	char	read

128	€	8364
129		129
130	‚	8218
131	ƒ	402
132	„	8222
133	…	8230
134	†	8224
135	‡	8225
136	ˆ	710
137	‰	8240
138	Š	352
139	‹	8249
140	Œ	338
141		141
142	Ž	381
143		143
144		144
145	‘	8216
146	’	8217
147	“	8220
148	”	8221
149	•	8226
150	–	8211
151	—	8212
152	˜	732
153	™	8482
154	š	353
155	›	8250
156	œ	339
157		157
158	ž	382
159	Ÿ	376
160	 	160
161	¡	161
162	¢	162
163	£	163
164	¤	164
165	¥	165
166	¦	166
167	§	167
168	¨	168
169	©	169
170	ª	170
171	«	171
172	¬	172
173	­	173
174	®	174
175	¯	175
176	°	176
177	±	177
178	²	178
179	³	179
180	´	180
181	µ	181
182	¶	182
183	·	183
184	¸	184
185	¹	185
186	º	186
187	»	187
188	¼	188
189	½	189
190	¾	190
191	¿	191
192	À	192
193	Á	193
194	Â	194
195	Ã	195
196	Ä	196
197	Å	197
198	Æ	198
199	Ç	199
200	È	200
201	É	201
202	Ê	202
203	Ë	203
204	Ì	204
205	Í	205
206	Î	206
207	Ï	207
208	Ð	208
209	Ñ	209
210	Ò	210
211	Ó	211
212	Ô	212
213	Õ	213
214	Ö	214
215	×	215
216	Ø	216
217	Ù	217
218	Ú	218
219	Û	219
220	Ü	220
221	Ý	221
222	Þ	222
223	ß	223
224	à	224
225	á	225
226	â	226
227	ã	227
228	ä	228
229	å	229
230	æ	230
231	ç	231
232	è	232
233	é	233
234	ê	234
235	ë	235
236	ì	236
237	í	237
238	î	238
239	ï	239
240	ð	240
241	ñ	241
242	ò	242
243	ó	243
244	ô	244
245	õ	245
246	ö	246
247	÷	247
248	ø	248
249	ù	249
250	ú	250
251	û	251
252	ü	252
253	ý	253
254	þ	254
255	ÿ	255
This output I would expect when writing UNICODE

3. Delete the file Database.sqlite
Compile/run the code with UNICODE (write to the SQLite-database = first run).

The output of the program
when writing with UNICODE and reading with UNICODE:

Code: Select all

write	char	read

128	€	128
129		129
130	‚	130
131	ƒ	131
132	„	132
133	…	133
134	†	134
135	‡	135
136	ˆ	136
137	‰	137
138	Š	138
139	‹	139
140	Œ	140
141		141
142	Ž	142
143		143
144		144
145	‘	145
146	’	146
147	“	147
148	”	148
149	•	149
150	–	150
151	—	151
152	˜	152
153	™	153
154	š	154
155	›	155
156	œ	156
157		157
158	ž	158
159	Ÿ	159
160	 	160
161	¡	161
162	¢	162
163	£	163
164	¤	164
165	¥	165
166	¦	166
167	§	167
168	¨	168
169	©	169
170	ª	170
171	«	171
172	¬	172
173	­	173
174	®	174
175	¯	175
176	°	176
177	±	177
178	²	178
179	³	179
180	´	180
181	µ	181
182	¶	182
183	·	183
184	¸	184
185	¹	185
186	º	186
187	»	187
188	¼	188
189	½	189
190	¾	190
191	¿	191
192	À	192
193	Á	193
194	Â	194
195	Ã	195
196	Ä	196
197	Å	197
198	Æ	198
199	Ç	199
200	È	200
201	É	201
202	Ê	202
203	Ë	203
204	Ì	204
205	Í	205
206	Î	206
207	Ï	207
208	Ð	208
209	Ñ	209
210	Ò	210
211	Ó	211
212	Ô	212
213	Õ	213
214	Ö	214
215	×	215
216	Ø	216
217	Ù	217
218	Ú	218
219	Û	219
220	Ü	220
221	Ý	221
222	Þ	222
223	ß	223
224	à	224
225	á	225
226	â	226
227	ã	227
228	ä	228
229	å	229
230	æ	230
231	ç	231
232	è	232
233	é	233
234	ê	234
235	ë	235
236	ì	236
237	í	237
238	î	238
239	ï	239
240	ð	240
241	ñ	241
242	ò	242
243	ó	243
244	ô	244
245	õ	245
246	ö	246
247	÷	247
248	ø	248
249	ù	249
250	ú	250
251	û	251
252	ü	252
253	ý	253
254	þ	254
255	ÿ	255
4. Compile/run the code with ASCII (read the database = without deleting before)

The output of the program
when writing with UNICODE and reading with ASCII:

Code: Select all

write	char	read

128	€	128
129		129
130	‚	130
131	ƒ	131
132	„	132
133	…	133
134	†	134
135	‡	135
136	ˆ	136
137	‰	137
138	Š	138
139	‹	139
140	Œ	140
141		141
142	Ž	142
143		143
144		144
145	‘	145
146	’	146
147	“	147
148	”	148
149	•	149
150	–	150
151	—	151
152	˜	152
153	™	153
154	š	154
155	›	155
156	œ	156
157		157
158	ž	158
159	Ÿ	159
160	 	160
161	¡	161
162	¢	162
163	£	163
164	¤	164
165	¥	165
166	¦	166
167	§	167
168	¨	168
169	©	169
170	ª	170
171	«	171
172	¬	172
173	­	173
174	®	174
175	¯	175
176	°	176
177	±	177
178	²	178
179	³	179
180	´	180
181	µ	181
182	¶	182
183	·	183
184	¸	184
185	¹	185
186	º	186
187	»	187
188	¼	188
189	½	189
190	¾	190
191	¿	191
192	À	192
193	Á	193
194	Â	194
195	Ã	195
196	Ä	196
197	Å	197
198	Æ	198
199	Ç	199
200	È	200
201	É	201
202	Ê	202
203	Ë	203
204	Ì	204
205	Í	205
206	Î	206
207	Ï	207
208	Ð	208
209	Ñ	209
210	Ò	210
211	Ó	211
212	Ô	212
213	Õ	213
214	Ö	214
215	×	215
216	Ø	216
217	Ù	217
218	Ú	218
219	Û	219
220	Ü	220
221	Ý	221
222	Þ	222
223	ß	223
224	à	224
225	á	225
226	â	226
227	ã	227
228	ä	228
229	å	229
230	æ	230
231	ç	231
232	è	232
233	é	233
234	ê	234
235	ë	235
236	ì	236
237	í	237
238	î	238
239	ï	239
240	ð	240
241	ñ	241
242	ò	242
243	ó	243
244	ô	244
245	õ	245
246	ö	246
247	÷	247
248	ø	248
249	ù	249
250	ú	250
251	û	251
252	ü	252
253	ý	253
254	þ	254
255	ÿ	255
This output I would expect when writing ASCII

Maybe the output to databases in DatabaseUpdate was exchanged between ASCII and UNICODE
All times are UTC+01:00
Page 2 of 2

Powered by phpBB® Forum Software © phpBB Limited