Android Question Insert SQL string in config.properties file automatically

[Setlodi]

Hi there

I wonder if what I require is possible. I'm creating a subscription system that automatically creates tables in my VPS mysql database for subscribers. So for every user who signs up, a number of tables will be created to store information as they use the app. So every subscriber must have a few SQL strings in the config.properties file that are specific to them. These strings allow them to do CRUD operations in their individual tables.

Now my questions

1. Is it possible to automatically write SQL strings in the config.properties, which will not be located in the jRDC jar file.
2. If not, what would be the alternative?

I prefer to work with the jRDC middleware for obvious security reasons.

 

[Erel]

Don't be afraid to modify jRDC2 source code. It should be quite simple to modify the set of commands at runtime.

 

[Setlodi]

Hi Erel

Thank you so much for your response. I will investigate modifying the jRDC2 code even though I honestly don't have any idea of where to start or how to go about it. I will be grateful if anyone can help me get started with this.

Thanks

 

[Erel]

Check the code in RDCConnector. The RDCHandler calls its GetCommand to get the SQL command.

 

[Setlodi]

Check the code in RDCConnector. The RDCHandler calls its GetCommand to get the SQL command.

Thank you once more. I will start with a simple SQL string like this:

Public Sub GetCommand(Key As String) As String
    Key = "Get_Daily_Report = SELECT * FROM Visits_Table"
    If commands.ContainsKey("sql." & Key) = False Then
        Log("*** Command not found: " & Key)
    End If
    Return commands.Get("sql." & Key)
End Sub

Folow-up questions:

1. Now does this mean I must not include SQL commands in config.properties?
2. Or can I include fixed ones and only dynamic ones in Public Sub GetCommand?
3. What if I have more than one dynamic SQL string?
4. Also, will it work if I add the SQL strings in a CSV file and place it in the same folder as config.properties, which will be on the VPS? Referenced in LoadConfigMap sub?

 

[aeric]

I see you are making thing more and more complicated. Why you want to make every user has his own table?

 

[Setlodi]

I see you are making thing more and more complicated. Why you want to make every user has his own table?

Not every user will have their own table but companies that have purchased devices. This is an app on a specific device that will allow companies to have the following tables, specific to them:

1. Employee table
2. Devices table
3. Reports table
4. Assets table
5. Visits table

Every company that buys devices the system will create the tables when they 'Activate'. All the devices that the company has purchased will be stored in the devices table together with their serial numbers, device ID, OS version as well as my app version. The users of the devices will be in the employee table, with the employee details including usernames and passwords for logging into the app. Basic information will be captured in the management app which is a B4J application. As "visitors" visit the company premises, they are recorded and all the assets that they receive are stored in assets table using the device with B4A app. Managers will be able to log in the management app and generate reports for visitors who entered the company premisses, the assets that they have received, the employee that assisted them, etc.

 

[aeric]

Not every user will have their own table but companies that have purchased devices. This is an app on a specific device that will allow companies to have the following tables, specific to them:

1. Employee table
2. Devices table
3. Reports table
4. Assets table
5. Visits table

Every company that buys devices the system will create the tables when they 'Activate'. All the devices that the company has purchased will be stored in the devices table together with their serial numbers, device ID, OS version as well as my app version. The users of the devices will be in the employee table, with the employee details including usernames and passwords for logging into the app. Basic information will be captured in the management app which is a B4J application. As "visitors" visit the company premises, they are recorded and all the assets that they receive are stored in assets table using the device with B4A app. Managers will be able to log in the management app and generate reports for visitors who entered the company premisses, the assets that they have received, the employee that assisted them, etc.

This doesn't mean you need different tables for different companies.
You can use foreign keys to store the id of the different companies.

 

[Erel]

@aeric advice is very accurate. You don't need to "duplicate" tables. You should instead add a company_id field, which should be indexed, and use it in all queries. This will be more manageable than having many many tables.

 

[Setlodi]

Thank you aeric and Erel. Your suggestions make a lot of sense. I will have to rethink and redesign my system.

The Masters have spoken!!!