Not a coding question.

[ken_anthony]

This may seem like a coding question, but it is not. Which is why I'm posting under general discussions. I just need some validation for reasons I will try to make clear. You may not want to read any further because I'm about to get a bit long winded. For those that brave this ride with me you have my sincere appreciation.

I've been programming since 1975 but haven't worked professionally since 2004. I live on the meager scraps of disability, Both my mind and body failing me. I have to put my nose to the monitor to read at times because my eyes just refuse to focus. I purchase PureBasic in the hope I still have enough brainpower left to produce a product that might generate some additional income in my failing years. If one thing doesn't work I will push forward with another. I don't see that I have any choice. Before I forget, I'm using 32bit Mint 15rc.

So I absolutely have to be able to connect and use a SQL database. Without that, I'm dead in the water. I thought I'd be making a decision about a host in a few months, but today was amazingly productive. The work I got done today was half of what I'd planned for the next two months. I'm very happy about that. So now the next steps are upon me.

To keep this as short as possible, I will skip a few things, so forgive me. Back in the day, using SQL Server or Access in Windows was completely transparent to me. I just worried about getting my SQL queries right because the connection was a given. Connecting to hundreds of customers databases was just part of a days work (not all at the same time, but over time.) Some may knock O.O.P. but it makes some things extremely trivial. Using ADO I would spin through disconnected recordsets in thousands of places in my code. The fact is you don't need to read through thousands of pages of documentation to get things done. At it's core I just needed variations on about a dozen lines of code.

So I've read everything on the subject (thanks for all the links, but no more please.) In VB I would set db object which only required four methods: OpenDB or ReleaseRS, then one that returned an ADO recordset and the other that would return the count of records updated. That was it.

I'm going with PostgreSQL so OpenDB will be:

Code: Select all

UsePostgreSQLDatabase()
result = OpenDatabase(#DatabaseEnum, "Filename", "UserName", "PW") ; ignoring result for now.

Except what I'm reading is a bit unclear in that "FileName" is more like a connection string that includes:

host: Name of host to connect to.
hostaddr: Numeric IP address of host to connect to.
port: Port number to connect to at the server host.
dbname: The database name. Defaults to be the same as the user name.
connect_timeout:

But I'm not seeing the format for that? Even assuming I get most of that from my host.

So I need to be able to remotely administer PostgreSQL so I can create a db and assign names and passwords.

Docs say I can use #PB_Any, but that does not seem right? Don't I want to always know the constant I'm assigning?

The I spin throught data, but is this right?

Code: Select all

         DatabaseQuery(#DatabaseEnum, "SELECT * FROM TableName;")
         While NextDatabaseRow(0)
                 FieldValue$ = GetDatabaseString(#DatabaseEnum, ColumnNumber)
          Wend

Don't I need to check EOF first? Isn't this starting with the second row?

Sorry if I sound like a feeble old man (which in fact is what I am these days.) I really just need one working example which I really can't do until I've paid for a host. Again, I'm not looking for solutions, just validation that I'm not being incredibly stupid. OTOH, if from the foregoing you do see something I'm obviously missing that will save me hours of running around the bushes (and forgive me for typos, my eyes have just gone into refuse to focus mode)...

I suspect that no young folks will understand this post and many older will not either. Thanks for hanging in there with me.

[So do I hit the submit button now, or just delete this whole psychotic episode?]

[the.weavster]

I'm going with PostgreSQL so OpenDB will be:

Code: Select all

UsePostgreSQLDatabase()
result = OpenDatabase(#DatabaseEnum, "Filename", "UserName", "PW") ; ignoring result for now.

Except what I'm reading is a bit unclear in that "FileName" is more like a connection string that includes:

host: Name of host to connect to.
hostaddr: Numeric IP address of host to connect to.
port: Port number to connect to at the server host.
dbname: The database name. Defaults to be the same as the user name.
connect_timeout:

But I'm not seeing the format for that?

http://www.connectionstrings.com/postgre-sql

Even assuming I get most of that from my host.

Is the database server being hosted on the internet?
Are you planning to connect to it directly from your PB program?
Danger Wil Robinson, danger!

So I need to be able to remotely administer PostgreSQL so I can create a db and assign names and passwords.

This is a nice program to do that job

Docs say I can use #PB_Any, but that does not seem right? Don't I want to always know the constant I'm assigning?

You can define it yourself and pass it in as the first parameter or use #PB_Any and get it returned as result.

Code: Select all

         DatabaseQuery(#DatabaseEnum, "SELECT * FROM TableName;")
         While NextDatabaseRow(0)
                 FieldValue$ = GetDatabaseString(#DatabaseEnum, ColumnNumber)
          Wend

Don't I need to check EOF first? Isn't this starting with the second row?

When the recordset is returned the cursor is positioned before the first record, the While loop is testing for EOF.

[ken_anthony]

Wow. That was a really great reply. You cleared up a lot of confusion for me. Greatly appreciated.

Is the database server being hosted on the internet?
Are you planning to connect to it directly from your PB program?
Danger Wil Robinson, danger!

I suspect I know a few of the reasons, but why?

I am going to have utilities running against the db on the host so I probably could just write a client but what does that buy me? (Other than the questionable value for me of writing a server.) It just seems simpler to access the db over the net. I've done it before in the past. I am concerned about security, but the database is going to be there regardless of how I access it. I definitely need it to be able to handle lots of clients (thousands per server.)

[the.weavster]

Is the database server being hosted on the internet?
Are you planning to connect to it directly from your PB program?
Danger Wil Robinson, danger!

I suspect I know a few of the reasons, but why?

I am going to have utilities running against the db on the host so I probably could just write a client but what does that buy me? (Other than the questionable value for me of writing a server.) It just seems simpler to access the db over the net. I've done it before in the past. I am concerned about security, but the database is going to be there regardless of how I access it. I definitely need it to be able to handle lots of clients (thousands per server.)

By default PostgreSQL doesn't allow remote connections, you have to explicitly enable it. If you're going to use a regular web hosting service it's also likely by default they will not allow remote connections direct to the database either.

If the PostgreSQL database server is configured to accept remote connections then you should use SSL or some form of secure tunneling protocol like SSH or otherwise what your sending to and from the server will be travelling across an unsecured network in clear text.

[ken_anthony]

an unsecured network in clear text

Nothing in the data itself would be sensitive. Player passwords would be using one way encryption. Even I wouldn't be able to recover those. I just need to be sure nobody can log into the database and screw with it. The only sensitive data is the connection string. Is there any way to just encrypt that?

[ken_anthony]

I just checked some web games on the internet and none of them are using any type of security that I can see. That includes login and player passwords.

[citystate]

one thing the.weavster didn't mention, unless your constant #DatabaseEnum is equal to 0, you'll run into problems in the while loop. Perhaps change is to read something like this:

Code: Select all

DatabaseQuery(#DatabaseEnum, "SELECT * FROM TableName;")
While NextDatabaseRow(#DatabaseEnum) 
  FieldValue$ = GetDatabaseString(#DatabaseEnum, ColumnNumber)
Wend

[ken_anthony]

It's always the little things, isn't it.

Thank you, citystate.

ken