Hi, swapping some code from using database record / insertRecord to SQLExecute Insert Into for speed - is there a known way to escape endofline characters in a string when using Insert Into? Many thanks. Dave
not sure if I get your issue. Does EndOfLine not work for you? Something like:
Var example1 as string = "First line"
Var example2 as string = "Second line"
Var mySQLvariable as string = example1 + EndOfLine + example2
Vice-verse if you want to get rid of them, you could use
myString.ReplaceAll(EndOfLine, " ")
Hah, I learned again something new. Is there a difference in speed? When was this added … 
Difference in coverage. EndOfLine is a function that returns the appropriate string for the platform it is on. ReplaceLineEndings replaces all types of line ending strings.
It is quite common in my code to have
tsData = tsData.ReplaceLineEndings(EndOfLine.Unix)
tsData = tsData.ReplaceAll(EndOfLine.Unix, "SomethingElse")
1 Like
If you want speed use ReplaceLineEndingsMBS.
1 Like
+1, that’s the one I’m using indeed and one explanation why I seem to loose somehow track on what has been added to Xojo :-).
ReplaceLineEndings has been around since API 1.0
I wouldn’t consider it added recently.
I’m really curious how different the MBS plugin is. I tend to lean toward Xojo Framework functions that exist unless there’s a significant reason to use the plugin. Another example of something I really don’t use is NameWithoutExtensionMBS. Plugins are just such a bog on the IDE lately that I’d rather have Xojo code solutions.
Update: Not that I don’t use plugins, I only use them for things that I can’t already accomplish in Xojo.
I found using ReplaceAll(SQLString, endOfLine, “’ || CHR(10) '”) works a treat.
There was a certain point at which I recall the documentation explicitly stating to use ReplaceLineEndings over ReplaceAll(EndOfLine). I don’t know when or where I saw it, but I’d agree.
ReplaceLineEndingsMBS is 20-30% faster than ReplaceLineEndings. But the most important part is that it doesn’t give you the beachball for large files.
1 Like
By using ReplaceAll (not tried ReplaceLineEndings yet), I’ve got the time from 4m 15 down to 1m using Insert Into - I guess this shows that databaserecord method is much slower!
I think I’m forced to use ReplaceAll as cubeSQL expects || CHR(10) || to indicate endofline. Unless someone can tell me otherwise!
I second that in general. But here is the/my issue:
Let’s go back to let’s say 2005: You want to achieve something which is not available in the core, but you are bumping into a plugin function, which you bought already and which is doing the job. It is working and you are happy. You might be so happy that next time you are just re-using the same approach. And it is then difficult to check if Xojo added something new, which potentially might replace what you did with the plugin. Or the plugin (that’s often the case) is either faster or has more options … There is currently not one week passing where I’m not learning something new, which is do-able in Xojo but where i used a different approach (aka plugins) in the past.
Another example: I learned only via my conversion work of Joe Strout’s String Utils through feedback by @Robert_Livingston that string.EndsWidth now exists in Xojo. When Joe initially wrote his module it didn’t (was added with 2019r2).
Does anyone know a resource where we can see the history of all commands? I’m not aware of any.
Please don’t do this. Use a Prepared Statement and insert the string as-is, don’t try to massage the data yourself.
The new ExecuteSQL (and SelectSQL) will do this for you.
3 Likes
The reading is boring, but usually the release notes have that.
1 Like
Of course, it is always somewhere ;-). I even know where to look: https://documentation.xojo.com/Resources:Release_Notes
But it is no fun finding what really changed in terms of new commands :-(.
But the above solution works for me, so I don’t see the point using anything else - if it works, it works! And it’s bloomin’ fast!
Are you familiar with “sql injection”?
There is a right way to do this, and that’s not the right way, even if it works in your limited tests.
2 Likes
I am not sure how accurate this is but this is my best effort. It is an Excel file.:
1 Like