UNC path for connecting to CSV connection

[ztminhas]

Good Morning

I have some code which connects to a CSV folder like this:

Sub getDataFromSheets()
Dim oResult As Object
Dim tblName$
Dim srcTempRs As Object, srcRs As Object
Dim tdsppmExists As Boolean
Dim srcSQL$, colNames$
Dim daCol As New Collection
Dim dataSrc$
	dataSrc ="Y:\"
    getSdbcFlatFileConnection(dataSrc)
    oResult = csvCon.getMetaData().getTables(null, "%", "%", Array("TABLE"))
End Sub

sub getSdbcFlatFileConnection(fileLoc As String) 
Dim sURL$
Dim myDict As Variant
	myDict= getScriptForgeDictionary()
	With myDict
		.Add("Extension", "csv")
		.Add("HeaderLine", True)
		.Add("FieldDelimiter", "	")
		.Add("StringDelimiter", """")
		.Add("DecimalDelimiter",".")
		oParms = .ConvertToPropertyValues()
		.Dispose()
	End With
    sURL="sdbc:flat:" & fileLoc
    csvCon= getConnectionWithUserInfo(sURL,	oParms)
End sub

private Function getConnectionWithUserInfo(sURL As String,oParms As Variant) As com.sun.star.sdbc.XConnection
Dim oManager As object
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getConnectionWithUserInfo= oManager.getConnectionWithInfo(sURL,oParms)
End Function

Function getScriptForgeDictionary() As Variant
	if not GlobalScope.BasicLibraries.isLibraryLoaded("ScriptForge") Then
		GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
	End If
	getScriptForgeDictionary= CreateScriptService("Dictionary")
End Function

This code works fine. However the Y: drive maps to an UNC path. I would like to however use the UNC path instead of the mapped drive. When I use the UNC path, I get the following error:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: The URL “file://server/full_unc_path” is not valid. A connection cannot be created..

So does anyone have any idea to connect to an UNC path?

[mikekaganski]

This might be not important here, and there seems to be some internal conversion in place, but the sdbc:flat: URI expects the file: URL, not a system filepath. Maybe there’s some corner case with that internal conversion with UNC paths. And anyway, the internal conversion is just a workaround for common but invalid URLs. So first try to create the URI using ConvertToURL, like

sURL="sdbc:flat:" & ConvertToURL(fileLoc)

[ztminhas]

This might work, the problem seems to be that the UNC path contains spaces.

[Villeroy]

Spaces are URL encoded to %20. ConvertToURL fails with the following characters:

! 
$
&
'
(
)
*
+
,
/
:
=
@

https://bugs.documentfoundation.org/show_bug.cgi?id=96413

[erAck]

IMHO it is not the task of ConvertToURL() to percent-encode anything except space as %20, as that is the only neither unreserved nor reserved character in an URI and what characters of the reserved characters are allowed depends on the actual URI scheme and protocol.
See RFC 3986 - Uniform Resource Identifier (URI): Generic Syntax and following.

It is the task of the application programmer (or another function) to know which reserved characters act as delimiters in a specific URI scheme and need to be percent-encoded.

Also commented at 96413 – Basic ConvertToURL fails to URL encode many characters.

[karolus]

@erAck

It is the task of the application programmer (or another function) to know which reserved characters act as delimiters in a specific URI scheme and need to be percent-encoded.

from pathlib import Path
p = Path.home() / "Documents" / "some+-*:`&§thing with #89=()"
p.as_uri()
#→'file:///home/ …me… /Documents/some%2B-%2A%3A%60%26%C2%A7thing%20with%20%2389%3D%28%29'

[mikekaganski]

@karolus it’s also possible to percent-encode all Ascii alphanumeric characters. But this is not recommended. The python library is overly cautious, and encodes more than necessary.

The characters listed in the bug are all OK in file URLs. But of course, not only space needs encoding, which is correctly done.

[Villeroy]

I just wanted to point out that a space in the path might not be a source of failure. LibreOffice 7.5 on Windows converts UNC paths into URLs.
When pasted into the Insert>Hyperlink dialog,
\\server02\data\Docs\File - 6918-20190902-100500.pdf
is converted to
file://server02/data/Docs/File%20-%206918-20190902-100500.pdf
which is also the result of Basic’s ConvertToURL function.
And to my surprise both, the UNC and the URL, work equally well with Calc’s HYPERLINK function.

[mikekaganski]

That is the correct URL.

Your warning about the problems in the function is wrong, and so creates confusion.

[Villeroy]

No backslashes in URL. I manually shortened the actual path.