Friday, January 31, 2014

SQL Server: use a cursor to fetch rows, then split a comma separated string in a column, and loop all the values

Some time ago I wrote a post about "How to split a comma separated string and loop it's values in SQL Server".
Today an anonymous user asked how does this apply if you are reading comma separated string from a column in a DB. 

The simplest way would probably be to do these kind of thing in a proper programming language (C++, C#, VB, Java.... you name it)
But if you really need to do this in T-SQL you can simply use a SQL Server cursor to enumerate the rows of the table, and then split the string one-by-one.

So, suppose we have a column in a table that contain comma separated values, we ca use a stored procedure with the following code. (as of today this code is UNTESTED, just because I don't have a SQL Server instance installed on my home computer :-) )
DECLARE @valueList varchar(8000) 

DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000) 

DECLARE my_string_table_cursor CURSOR
    FOR SELECT my_column_string FROM my_table

OPEN my_string_table_cursor 


    FETCH NEXT FROM my_string_table_cursor
    INTO @valueList 

    WHILE @@FETCH_STATUS = 0
    BEGIN

        set @pos = 0
        set @len = 0

        WHILE CHARINDEX(',', @valueList, @pos+1)>0
        BEGIN
            set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
            set @value = SUBSTRING(@valueList, @pos, @len)
            --SELECT @pos, @len, @value /*this is here for debugging*/
                
            PRINT @value
            --Here is you value
            --DO YOUR STUFF HERE
            --DO YOUR STUFF HERE
            --DO YOUR STUFF HERE
            --DO YOUR STUFF HERE
            --DO YOUR STUFF HERE

            set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
        END 

        FETCH NEXT FROM my_string_table_cursor 
        INTO @valueList  

    END

CLOSE my_string_table_cursor
DEALLOCATE my_string_table_cursor
The table should contain a column with values like
    row 1:  aa,bb,cc,dwefwef,43truygtfuye,w,
    row 2:  trd , ygu , umhb , bhu,
    row 3:  a,b,c,d,e,f,g,h,i,
    row 4:  123,a,456,bbb,kkk,00000,
And the string in each row must end with a comma ","

SQL Code colored with this syntax highlighter

Saturday, January 11, 2014

Plantronics M165 bluetooth headset teardown - Making a hole for the microphone to fix the low volume and make the other person hear you better

Some month ago I decided I should get a Bluetooth headset for my smartphone, mainly to get less RF to my head :-)
After some research and reviews reading activity I opted for a Plantronics M165 Marque 2 (I bought it on Amazon, they always have a fantastic customer service).
It looked nice, with a practical "hear plugging" system, and the reviews were good.

Effectively the headset is very nice, very light, and fit perfectly in my hears.



BUT, there is an issue, a big one (in my opinion).
The sensitivity of the mike is too low, if there is a bit of noise around me, the person I'm talking to hear the noise instead of my voice.
So, this Plantronics M165 Mk2 works nicely when I'm in a silent room, but if I'm outside near a road it is totally useless, I need to turn it off and recall the person without the headset. This happened to me so many times that I've stopped using this headset.

Just to be clear about the level of noise I'm talking about: when I'm in the office, in the outskirts of the city, then I go on the balcony: we are on the 4th floor, and the nearest road is at about 60mt. Here, the noise of the road is enough that I can't use the headset.

I asked myself why I can't use the headset with such a low noise... maybe that my headset is broken/defective? it is possible...
But I think there is a better explanation: this headset DOESN'T have a hole for the microphone.

Take a look at the following image, a photo of the frontal part of the headset.
There isn't any hole, it's all solid plastic, and the microphone is behind this plastic.
How can it hear my voice?



There is second mike (for noise reduction) that instead it's near some hole facing "the noise" on the opposite side of my mouth.
Here you can see an image of the hole near the "noise reduction" mike.
I've circled them in red because they are so tiny that even with a macro photo you can barely see them. They are very tiny, about 2-3mm x 0,5mm and are "trapped" between the plastic bezel of the headset.



In my opinion, this can't work well.
Any headset I've ever seen have a hole for the mike, why the Plantronics M165 doesn't have it?

Now, I think I understood the problem: no hole for the mike.
I have also decided that this headset is useless for me, because it can work only in a silent room, which is not the the typical situation where I want to use it.

So maybe I can find a way to fix it? If only I knew where exactly the mike is, then I could make a hole for it.
So, why not try to dismantle it? :-)

I always loved to dismantle things, and I know that try to dismantle a headset can be a one-way street. Sometime they are glued together, and they are so tiny that it's really easy to break something.
Anyway, I should at least try :-)

So here is my venture in dismantling and fixing my headset.
To dismantle it I've used some plastic opening tool and some mini screwdriver.

Typical plastic opening tool

After trying to put the opening tool in any spot I could find, I've been able to open my way into the headset.
In the following image you can see the partly opened headset.
In red I've circled the only existing opening on the headset, them are near the "noise reduction" mike. Through these holes the headset can listen for the ambient noise.
Circled in yellow there is one of the 5 clips that hold the headset together.


  Here are some other images of the partly dismantled headset.
 


After some more pulling and bending I've been able to open the headset.

In the following images you can see the external part of the headset.
Circled in Green there are the 5 clips that keep the headset together.
Circled in Red and Orange there are other 2 clips that hold the headset together.
These last 2 clips are very hard to "unclip" because them are very tiny, and you cant use the typical plastic opening tool to unclip them (because them are placed in the corer of the headset)

As you can see from the photo, the Orange one is missing, I  broke it during the opening procedure... :-(

 

Now we can take a look inside the headset.
Circled in red there are the hooks of the clips.
Circled in green the leds of the headset.
Circled in pink the switch of the main button.
Circled in yellow is the bluetooth antenna.
Circled in blue is what I suppose to be the "noise reduction" mike.



And where is the microphone for the voice?
Let's take a closer look...


 Look at the object circled in red, it looks very similar the the noise reduction mike (circled in blu)


Let me check better.. I can also slip a piece of paper under it, it definitely seem to be the voice mike.


Now I know exactly where I need to make a hole, on the external plastic of the headset :-)
So, let's drill a hole in this deaf plastic shell!
To make the hole I've simply used my old trusty Olfa Cutter

 

And after some hand drilling... Ta-daaaa!
Here is my modded version of the Plantronics M165 Marque 2, now with a hole for the mike, so that the person you are calling can hear you better :-)

Here you can see the voice-microphone through the hole :-)


I reassembled the headset, and I'm happy to tell you that it stay together perfectly, even with a broken clip.

Clearly the headset have now some sign of the surgery procedure it went over :-)

 
 

Now the headset works way better than before, and I can talk on the balcony of the office without any issue! :-)
 

Wednesday, January 1, 2014

Single click conversion form any format to EPUB using Calibre v2.0 - now with batch multiple files conversion, and rename instead than overwrite

One year ago I developed a simple script to convert any document (PDF, RTF, TXT,...) to EPUB format, using Calibre.
The script was very simple to use, just put the script in the Calibre folder, and then drag and drop the file you want to convert on the script.

Today a reader asked for 2 new features:
  • being able to convert multiple files at once
  • don't overwrite the epub file that may already exist
So I implemented these features in an updated version of the script.

Click here to Download the new version of the script.

You need to place this script in the same folder where you have the Calibre executable.

Then you can drag and drop the file you want to convert on the script:

After the conversion, you'll find the new converted EPUB file in the same folder as the original file.

Check the original article for more information about this script, and how to use it.

About the new features
Now, for example, you can select 10 PDF files and drop them on the script, and they'll get converted to EPUB (and Author and Title will be set, check the original article for more information)

If the epub file already exist, now the script will add current date/time to the filename.
Example: you try to convert a file named "Asimov - Nightfall.pdf", the script try to create a file named "Asimov - Nightfall.epub", but if this file already exist the script will then create a file named "Asimov - Nightfall 01_01_2014 21.13.52,34.epub" by adding current date/time to the filename.

This updated script, in theory, can convert up to 400 files at once.

The real limitation came from Windows max command line length, that is limited to 8191characters.
So, in real life, you probably won't be able to drag and drop 400 files on the script, because their full pathname will be longer than 8191 characters.
By "Full pathname" i mean the full path + filename of the files, so a full pathname of a file my look something like "E:\eBook\asimov books\series\Asimov - Empire 1.pdf" whick is long 52 chars.
So if you have files like that, you can convert a maximum of about 8192 / 52 = 157 files at once.
Depending on you filename and folder structure your mileage may vary.

Inside the script...
Here is a color-coded version of the EPUB conversion script, the same script you can download here.
(color coded version of the script created by the courtesy of http://hilite.me/ using the 'native' profile, and then tweaking color by hand)

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
@echo off
setlocal EnableDelayedExpansion

rem  find script path
set scriptPath=%~dp0
set scriptPath=%scriptPath:~0,-1%
rem "%scriptPath%\pyprogram.exe" /myparam=123_abcd


IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")







SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")







SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")







SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")







SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")







SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")







SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")







SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")







SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")







SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")

SHIFT
IF EXIST "%~1" (CALL :epubConvert "%~1")



rem this goto if here to skip the code of the confesion function
goto :endOfBatchfile


rem start of a sub function
:epubConvert
        set filename=%~n1

        REM split authors from title using the first "-" as separator
        REM using ! and EnableDelayedExpansion instead of % so that filename containing parenthesis wont cause issue
        FOR /F "usebackq tokens=1* delims=-" %%a in ('!filename!') do (
          set autore=  %%a  
          set titolo=  %%b  
        ) 

        rem trim authors name extra space from left/right
        for /f "tokens=* delims= " %%a in ("!autore!") do set autore=%%a
        set autore=%autore%##
        set autore=%autore:                ##=##%
        set autore=%autore:        ##=##%
        set autore=%autore:    ##=##%
        set autore=%autore:  ##=##%
        set autore=%autore: ##=##%
        set autore=%autore:##=%
        echo. Authors: "%autore%"


        rem trim titles extra space from left/right
        for /f "tokens=* delims= " %%a in ("!titolo!") do set titolo=%%a
        set titolo=%titolo%##
        set titolo=%titolo:                ##=##%
        set titolo=%titolo:        ##=##%
        set titolo=%titolo:    ##=##%
        set titolo=%titolo:  ##=##%
        set titolo=%titolo: ##=##%
        set titolo=%titolo:##=%
        echo. Title: "%titolo%"


        echo.
        echo.

        REM if the filename doesent contains any "-" then %titolo% will be empty
        IF "%titolo%"=="" (
          set titolo=!autore!
          set autore=unknown 
        )


        set InputfileParameters=--remove-paragraph-spacing
        set PDFInputfileParameters=
        set EpubInternalHTMLsplitSize=--flow-size 50

        IF "%~x1"=="PDF" set PDFInputfileParameters=--unwrap-factor 0.25

        set outputFileName=%~dp1%autore% - %titolo%.epub

        REM If filename alrteady exist, use a different name (I'll append current date/time to create a uinique filename)
        IF EXIST "%outputFileName%" (
              rem Get current date
              SET DT=%date%
              rem remove character invalid for filename
              SET DT=!DT:\=_!
              SET DT=!DT:/=_!
              SET DT=!DT:-=_!
              SET DT=!DT:.=_!
              SET DT=!DT::=_!

              rem Get current time
              SET TM=%time%
              rem remove character invalid for filename
              SET TM=!TM:\=.!
              SET TM=!TM:/=.!
              SET TM=!TM:-=.!
              SET TM=!TM::=.!

              set outputFileName=%~dp1%autore% - %titolo% !DT! !TM!.epub
        )



        "%scriptPath%\ebook-convert.exe" "%~1" "%outputFileName%" %InputfileParameters% %PDFInputfileParameters% %EpubInternalHTMLsplitSize% --authors "%autore%" --title "%titolo%"

        IF %ERRORLEVEL% NEQ 0 pause

rem end of sub function
goto :eof

rem end of the batch file
:endOfBatchfile

This batch file is very similar to the old one.
All the main code to do the conversion is included in a Sub function called epubConvert.
At the beginning of the file I've added about 400 lines of command like Switch / Call epubConvert %1- Switch / Call epubConvert %1 - Switch / Call epubConvert %1 - ...


By implementing these feature I even discovered a new batch command (shift) that I've never used before, it's very useful if you need to manage more than 9 command line parameters :-)