Opening a database over a network
Author
Hi,
I''m using BC5.02 and the TQuery (bdto) class to retrieve things from a database. Everything''s working fine on my computer, but I have no idea as to how to read/write stuff to a database in a remote location (over HTTP). I can''t find any tutorials/samples on the subject on the Net either. Can anyone reference an article or give a short rundown on the things I have to do to get it working...
Thanks in advance,
Crispy
I''m confused slightly.
You could use mySQL which allows you to connect via a host if you can get an API for it on whatever BC is (C++?).
It depends what you''re doing. The HTTP bit implies an HTTP Daemon and websites. If you can give me more info I can probably help you out. What exactly are you trying to do?
Al
You could use mySQL which allows you to connect via a host if you can get an API for it on whatever BC is (C++?).
It depends what you''re doing. The HTTP bit implies an HTTP Daemon and websites. If you can give me more info I can probably help you out. What exactly are you trying to do?
Al
Author
Suppose I have a MySQL database running at http:\\23.43.43.34:8889. What I need to do, is retrieve from and write data to it from any location. Basically it''s a highscore system though there''s a little more to it - but it still boils down to contacting a remote database. I was figuring, since the database at a http:\\ address, I''d need to access it over HTTP... no?
Some code to clear things up - it''s pretty straightforward:
This thing works on the local computer. Adding anything network-related to the SQLTEXT string will cause an abnormal termination of the program...
Crispy
Some code to clear things up - it''s pretty straightforward:
#define DATABASENAME "DivePlan"#define SQLTEXT "Select * from \"biolife.db\"" TQuery* Query = new TQuery(); Query->DatabaseName = string(DATABASENAME); Query->SQL->Text = string(SQLTEXT); Query->Open(); This thing works on the local computer. Adding anything network-related to the SQLTEXT string will cause an abnormal termination of the program...
Crispy
With MySQL you require to have the API on the current machine and a MySQL daemon running at the relevant port (just intall mysql on the machine) I believe it talks over TCP/IP as default.
~
_________ ~ __________
|sql query|-~->|sql server|
--------- ~ ----------
~
So for that you would need a sql.h and the mysql API for C++ (or whatever language).
http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html#Cplusplus
You could also send information over HTTP in the form of an HTTP Request with POST Paramaters to a specific page / handler on the HTTP Daemon (Microsoft IIS or Apache (Freeware), etc. At that end you could have Apache with PHP or mod_perl running. Then using some simple scripting (even a quick perl CGI script) you could write directly to the database on that server (again using the mySQL API). You could retrieve data by generating a simple text, csv or XML page which you could request using HTTP.
~
____________ ~ _____ __________ __________
|http request|-~->|httpd|-->|cgi script|-->|sql server|
------------ ~ ----- ---------- ----------
~
(though that makes it look more complex than it is)
The problem with that is then most people would be able to hack a simple http request so you would want to use some encryption or a checksum but we can look into that later.
Here''s a link on how to connect through C++ to SQL Server (Microsoft yuk):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_6_odbcht_1g1g.asp
I''m not sure if these will help as I''ve never learned C++:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_6_odbcht_1oqc.asp
Hope this helps, anything else just post here and I''ll check back. I can easily help you with any Apache setting up, CGI scripts, encryption, security etc.
Al
~
_________ ~ __________
|sql query|-~->|sql server|
--------- ~ ----------
~
So for that you would need a sql.h and the mysql API for C++ (or whatever language).
http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html#Cplusplus
You could also send information over HTTP in the form of an HTTP Request with POST Paramaters to a specific page / handler on the HTTP Daemon (Microsoft IIS or Apache (Freeware), etc. At that end you could have Apache with PHP or mod_perl running. Then using some simple scripting (even a quick perl CGI script) you could write directly to the database on that server (again using the mySQL API). You could retrieve data by generating a simple text, csv or XML page which you could request using HTTP.
~
____________ ~ _____ __________ __________
|http request|-~->|httpd|-->|cgi script|-->|sql server|
------------ ~ ----- ---------- ----------
~
(though that makes it look more complex than it is)
The problem with that is then most people would be able to hack a simple http request so you would want to use some encryption or a checksum but we can look into that later.
Here''s a link on how to connect through C++ to SQL Server (Microsoft yuk):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_6_odbcht_1g1g.asp
I''m not sure if these will help as I''ve never learned C++:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_6_odbcht_1oqc.asp
Hope this helps, anything else just post here and I''ll check back. I can easily help you with any Apache setting up, CGI scripts, encryption, security etc.
Al
quote:Original post by CrispyThat does not make any sense at all. Having "a MySQL database running" would imply having a MySQL server daemon listening on that address. MySQL uses its own protocol, not HTTP. I''m not sure where you got that address from, as you would never prefix the address of a MySQL server with protocol type.
Suppose I have a MySQL database running at http:\\23.43.43.34:8889.
You suggest by your code that you want to communicate directly with the SQL server, but this not done over HTTP. You should use MySQL''s API for that.
Author
quote:Original post by CWizard
That does not make any sense at all. Having "a MySQL database running" would imply having a MySQL server daemon listening on that address. MySQL uses its own protocol, not HTTP. I''m not sure where you got that address from, as you would never prefix the address of a MySQL server with protocol type.
You suggest by your code that you want to communicate directly with the SQL server, but this not done over HTTP. You should use MySQL''s API for that.
See - I didn''t know that
Anyway - I think I finally managed to download the correct "package" (name of the archive: "mysql-3.23.55-win.zip", 13 megs in size) that, among other things, also compiles on my compiler. I''m relatively confused about the API and all of the "packages" out there, however.
This place looks like the real thing (a tutorial for the MySQL API), but it won''t compile on BC5.02. It is stated very clearly in the docs that it requires at least BC5.5.
The 13Mb package I downloaded seems to be somekind of client/server package for web development, but it also contains a set of header files and the necessary dll''s and link libraries. The (only bit of) sample code contained within looks like this:
#include <windows.h>#include <stdio.h>#include <string.h>#include <mysql.h>#define DEFALT_SQL_STMT "SELECT * FROM db"#ifndef offsetof#define offsetof(TYPE, MEMBER) ((size_t) &((TYPE *)0)->MEMBER)#endif/************************************************************ main :-**********************************************************/intmain( int argc, char * argv[] ){ char szSQL[ 200 ], aszFlds[ 25 ][ 25 ], * pszT, szDB[ 50 ] ; int i, j, k, l, x ; MYSQL * myData ; MYSQL_RES * res ; MYSQL_FIELD * fd ; MYSQL_ROW row ; //....just curious.... printf( "sizeof( MYSQL ) == %d\n", sizeof( MYSQL ) ) ; if ( argc == 2 ) { strcpy( szDB, argv[ 1 ] ) ; strcpy( szSQL, DEFALT_SQL_STMT ) ; if (!strcmp(szDB,"--debug")) { strcpy( szDB, "mysql" ) ; printf("Some mysql struct information (size and offset):\n"); printf("net:\t%3d %3d\n",sizeof(myData->net),offsetof(MYSQL,net)); printf("host:\t%3d %3d\n",sizeof(myData->host),offsetof(MYSQL,host)); printf("port:\t%3d %3d\n",sizeof(myData->port),offsetof(MYSQL,port)); printf("protocol_version:\t%3d %3d\n",sizeof(myData->protocol_version), offsetof(MYSQL,protocol_version)); printf("thread_id:\t%3d %3d\n",sizeof(myData->thread_id), offsetof(MYSQL,thread_id)); printf("affected_rows:\t%3d %3d\n",sizeof(myData->affected_rows), offsetof(MYSQL,affected_rows)); printf("packet_length:\t%3d %3d\n",sizeof(myData->packet_length), offsetof(MYSQL,packet_length)); printf("status:\t%3d %3d\n",sizeof(myData->status), offsetof(MYSQL,status)); printf("fields:\t%3d %3d\n",sizeof(myData->fields), offsetof(MYSQL,fields)); printf("field_alloc:\t%3d %3d\n",sizeof(myData->field_alloc), offsetof(MYSQL,field_alloc)); printf("free_me:\t%3d %3d\n",sizeof(myData->free_me), offsetof(MYSQL,free_me)); printf("options:\t%3d %3d\n",sizeof(myData->options), offsetof(MYSQL,options)); puts(""); } } else if ( argc > 2 ) { strcpy( szDB, argv[ 1 ] ) ; strcpy( szSQL, argv[ 2 ] ) ; } else { strcpy( szDB, "mysql" ) ; strcpy( szSQL, DEFALT_SQL_STMT ) ; } //.... if ( (myData = mysql_init((MYSQL*) 0)) && mysql_real_connect( myData, NULL, NULL, NULL, NULL, MYSQL_PORT, NULL, 0 ) ) { if ( mysql_select_db( myData, szDB ) < 0 ) { printf( "Can''t select the %s database !\n", szDB ) ; mysql_close( myData ) ; return 2 ; } } else { printf( "Can''t connect to the mysql server on port %d !\n", MYSQL_PORT ) ; mysql_close( myData ) ; return 1 ; } //.... if ( ! mysql_query( myData, szSQL ) ) { res = mysql_store_result( myData ) ; i = (int) mysql_num_rows( res ) ; l = 1 ; printf( "Query: %s\nNumber of records found: %ld\n", szSQL, i ) ; //....we can get the field-specific characteristics here.... for ( x = 0 ; fd = mysql_fetch_field( res ) ; x++ ) strcpy( aszFlds[ x ], fd->name ) ; //.... while ( row = mysql_fetch_row( res ) ) { j = mysql_num_fields( res ) ; printf( "Record #%ld:-\n", l++ ) ; for ( k = 0 ; k < j ; k++ ) printf( " Fld #%d (%s): %s\n", k + 1, aszFlds[ k ], (((row[k]==NULL)||(!strlen(row[k])))?"NULL":row[k])) ; puts( "==============================\n" ) ; } mysql_free_result( res ) ; } else printf( "Couldn''t execute %s on the server !\n", szSQL ) ; //.... puts( "==== Diagnostic info ====" ) ; pszT = mysql_get_client_info() ; printf( "Client info: %s\n", pszT ) ; //.... pszT = mysql_get_host_info( myData ) ; printf( "Host info: %s\n", pszT ) ; //.... pszT = mysql_get_server_info( myData ) ; printf( "Server info: %s\n", pszT ) ; //.... res = mysql_list_processes( myData ) ; l = 1 ; if (res) { for ( x = 0 ; fd = mysql_fetch_field( res ) ; x++ ) strcpy( aszFlds[ x ], fd->name ) ; while ( row = mysql_fetch_row( res ) ) { j = mysql_num_fields( res ) ; printf( "Process #%ld:-\n", l++ ) ; for ( k = 0 ; k < j ; k++ ) printf( " Fld #%d (%s): %s\n", k + 1, aszFlds[ k ], (((row[k]==NULL)||(!strlen(row[k])))?"NULL":row[k])) ; puts( "==============================\n" ) ; } } else { printf("Got error %s when retreiving processlist\n",mysql_error(myData)); } //.... res = mysql_list_tables( myData, "%" ) ; l = 1 ; for ( x = 0 ; fd = mysql_fetch_field( res ) ; x++ ) strcpy( aszFlds[ x ], fd->name ) ; while ( row = mysql_fetch_row( res ) ) { j = mysql_num_fields( res ) ; printf( "Table #%ld:-\n", l++ ) ; for ( k = 0 ; k < j ; k++ ) printf( " Fld #%d (%s): %s\n", k + 1, aszFlds[ k ], (((row[k]==NULL)||(!strlen(row[k])))?"NULL":row[k])) ; puts( "==============================\n" ) ; } //.... pszT = mysql_stat( myData ) ; puts( pszT ) ; //.... mysql_close( myData ) ; return 0 ;} Mind that this is copy-pasted directly, so the bad formatting is not my doing...
Anyway - this is exactly why I chose software development rather than administration - stuff like this confuses me. For one, I can''t find any help on this "package" or whetever it is called (a wrapper?) that would tell me how I should go about connecting to a remote database. And secondly, I have no clue as to whether the database has been set up properly on the other end. The unfortunate thing is that the guy responsible for that doesn''t know either.
Anyway, supposing everything is OK, can anyone help me fill in the MYSQL structure and establish a connection? If anyone has a readymade application that can establish such a connection, the database is "supposed to be" at: 213.35.160.190:8889 and goes by the name "kaunad" (if that''s important - I honestly do not know)...
I hope I''m not talking complete nonsense here because over the course of time I''ve developed a hefty grudge against databases and now I have to deal with them over the network... Life''s irony I guess
I owe all my knowledge of them to MS Access and how to create a nice-looking form using the wizard...Oh yeah - as far as I know the database hasn''t been set up on OCDB.
quote:
Hope this helps, anything else just post here and I''ll check back. I can easily help you with any Apache setting up, CGI scripts, encryption, security etc.
I really appreciate your willingness to help. I tried setting up an MS Access database on Apache and PHP a while ago and failed miserably - that''s when I though: this thing is not for me, let someone else burn out early trying to figure out the impossible.
Crispy
Ok, I see what you want to do. A quick check revealed that a MySQL server (3.23.53-max-nt) listens on the address you gave.
I have not used the MySQL API alot. A while ago I wanted to make an application that connected to a MySQL server. I then tried the MySQL++ API, which uses container classes and it was really easy getting a connection going. I didn''t get far on that project though, due to other design problems, so I did not get much experience.
I don''t know anything about Borland compilers, and I would suggest using another. Perhaps Dev-C++ would suffice? At MySQL''s site they have a version: "MySQL++ 1.7.1 for Borland C++ 5.* for Windows", which sounds like it would suit you. The MySQL++ package also has somewhat complete documentation on their site.
I have not used the MySQL API alot. A while ago I wanted to make an application that connected to a MySQL server. I then tried the MySQL++ API, which uses container classes and it was really easy getting a connection going. I didn''t get far on that project though, due to other design problems, so I did not get much experience.
I don''t know anything about Borland compilers, and I would suggest using another. Perhaps Dev-C++ would suffice? At MySQL''s site they have a version: "MySQL++ 1.7.1 for Borland C++ 5.* for Windows", which sounds like it would suit you. The MySQL++ package also has somewhat complete documentation on their site.
Author
Yay! Got it working. Works like a charm - wonder how it can take some heat (connection wise) 
Turns out I was using functions for the local host before...
Thanks for the input everyone.
PS - started a thread in the lounge closely related to this one. Anyone interested in some mental contribution, please check it out
Regards,
Crispy
Turns out I was using functions for the local host before...Thanks for the input everyone.
PS - started a thread in the lounge closely related to this one. Anyone interested in some mental contribution, please check it out
Regards,
Crispy
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
