Gambas ONE

Posted: **Wednesday 23rd November 2022 4:17am**

I'm running into a bit of quirky behavior and am wondering if anybody else has experienced it or can confirm it.

My program has an edit field to enter a database name, which in my rough draft I don't edit check, then the program either creates or loads the file depending upon whether the file exists.

'=============================================================================
Public Sub CreateDatabaseFile(ArgDirName As String, ArgFileName As String)

        Dim theDB As Connection = New Connection
        
        With theDB
          .Type = "sqlite3"  
          .Host = ArgDirName
          .Name = ""
          .Open()
          .Databases.Add(ArgFileName)
          .Close
        End With

End
'=============================================================================
Public Sub OpenDatabase(ArgDirName As String, ArgFileName As String)

        With MyDB
          .Type = "sqlite3"  
          .Host = ArgDirName
          .Name = ArgFileName
          .Open()
        End With

End
'=============================================================================

Here is the weird part, trying to add a filename in the create routine with any special character, except underscore, doesn't work. However, if you give it a plain name, rename the file externally, the open routine works just fine.

Before I code a workaround, e.g. create a plain named file and rename it, I'd like to know if there is something going on that I am not aware of. I haven't done that much DB programming in Gambas.

Thanks,
Ced

Posted: **Wednesday 23rd November 2022 7:12am**

Hi Cedron.
What are the special characters that are giving you the issue ?

Posted: **Wednesday 23rd November 2022 7:21am**

confirmed Behavior ...

but found this:

Naming Conventions for SQLite
Each database, table, column, index, trigger, or view has a name by which it is identified
and almost always the name is supplied by the developer.The rules governing how a
valid identifier is formed in SQLite are set out in the next few sections.

Valid Characters
An identifier name must begin with a letter or the underscore character, which may be
followed by a number of alphanumeric characters or underscores. No other characters
may be present.

Posted: **Wednesday 23rd November 2022 12:27pm**

Generally, and not specifically to SQLite, the engines tend to dislike (with extreme prejudice) anything but actual data with spaces in it. I think that must be because they rely on parsers that detect spaces as word boundaries.

But just as a "schoolroom" example, consider naming some file "a b c d goldfish". This would be a legal filename and would appear in a file manager fine, BUT just consider running

Code: Select all

less a b c goldfish

in a virtual terminal. What is going to happen?
Well, SQLite is generally not even as clever as bash.
b

Posted: **Wednesday 23rd November 2022 2:55pm**

The naming conventions of databases have always been a pain. With sqlite and others, going forward, I am using split_with_underscore, all lower case names, no other special characters. But that is within the database.

This is apparently a Gambas' specific problem. The OS (Ubuntu on Raspberry Pi) has no problem, nor does the sqlite application. The quirk is that I can open a database with these characters, but I can't create one the regular way.

The name pattern that blew up first was: something{secret}goes~here

I am using the name as a four element data row in higher level databases, but prefer symbolically meaningful delimiters. I could also use three underscores instead.

I was thinking maybe my code wasn't proper. Should I report this as a bug?

Gambas ONE

Allowable characters in sqlite3 database file names

Allowable characters in sqlite3 database file names

Re: Allowable characters in sqlite3 database file names

Re: Allowable characters in sqlite3 database file names

Re: Allowable characters in sqlite3 database file names

Re: Allowable characters in sqlite3 database file names