We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Databases

vorlon
vorlon asked
on
Medium Priority
248 Views
Last Modified: 2013-12-25
Hi all.
I have an Access database sitting on a NT server. I do not have direct access to this server (ability to install or configure software). However, I had my hosting service provider create a Data Source Name (DNS) for this database so that it can be accessed using ODBC. I need to write a CGI script to access this database. Is there a standard way of doing this?

My first test failed because I created a script in Delphi but the BDE was not installed on the server. I'm thinking of using Visual Basic to access the database. Primarily, I don't have 1 single question, but would like to get some comments from everyone on this. What do think is the workable solution to accessing a database using a script? This doesn't necessarily mean I have to stick with an Access database...

Many thanks for those who can provide any ideas. I will award the points to the individual that sparks the correct, workable answer.
Jorge
Comment
Watch Question

Author

Commented:
Edited text of question

Commented:
It depends on your experience what is easiest, i would try things in this order:

- When ASP is installed on the server, you can use ASP
- when perl plus perl::ODBC is installed you can use perl
- use Visual Basic




I've been working on a similar project, and here is basically what I have done. I transferred the database to a SQL server, because it is VERY easy to access SQL from C or C++. My CGI program is simply an executable written in C, that accesses the database and puts the requested data in a table. The nice thing about doing it this way, is that it is pretty easy to use the same program to set up a form, and allow you to create simple on-the-fly queries. Also, C and SQL are both relatively quick, meaning that there is no noticable delay for the data to be retrieved from the database.

Author

Commented:
wrussell,
Thanks for your reply. Your comments seems very interesting. Only two things:
1) I do know SQL, but don't have the slighest idea on how to convert, or setup a SQL server. Even if I did, if this needs some sort of setup on at the server level, I might be at a loss because I have very limited access to the server.

2) I am very familiar with C but never used it to access database tables (only used it for straight through processing). What kind of libraries are needed?

To summarize, I know sql and C almost very well, but never used the two together especially on an SQL sever. I would be very interested, and thankfull for any insight you might be able to provide. If you can help me, please send me an email at santos@traxxinc.com.

Many thanks,
Jorge
There are a couple ways of doing this.  If the access database is already setup as an odbc source and you can access it you can use any type of programming you want.

1.  I would use cold fusion (available at www.allaire.com)  It is simple easy to use and a very dynamic programming language.  although it is not free and that is the main problem.

2.  Asps are great if you are using iis.  it is relativly easy to use asp to do this job.

Once a database is setup as an ODBC source ANY script in Any language will be able to access it so choose your sword.

Technocrat

3.  VB or Java script.  If you do not know vb or java script real well I wouldn't bother but if you have knowledge in vb already it is not hard to do.

Commented:
I'd replace Access with Sybase SQL Anywhere for NT.
To access it, I'd use Perl with ODBC or SybPerl.

Author

Commented:
petergo,
Unfortunatly, I don't have any saying in this. I can only use what my hosting service provider provides.

thanks for the comment anyway...

Commented:
Tocallo:

If you´re using Access, and don´t access to the server, I would definiteley recommend using Visual Basic CGI, although you could also use Delphi or C++ but it will be a lot harder.  If by any chance you can talk to your Service providers, talk them into buying and/or installing Cold Fusion available at www.allaire.com This will make data base and web scripting a breeze  to use.

Regards
jorge

Author

Commented:
jconde,
The service provider will not install any other software other than what they already have. Unfortunatly, i'm beginning to think they don't know what they're doing. I tried a couple of vb scripts but keep hitting walls with everything I try. The most frustracting fact is that the scripts all work on my local machine.

I think the service provider does not have the software correctly installed, but who am I to judge: I'm a simple customer, who might drop them one of these days.

Vorlon

Author

Commented:
jconde,
The service provider will not install any other software other than what they already have. Unfortunatly, i'm beginning to think they don't know what they're doing. I tried a couple of vb scripts but keep hitting walls with everything I try. The most frustracting fact is that the scripts all work on my local machine.

I think the service provider does not have the software correctly installed, but who am I to judge: I'm a simple customer, who might drop them one of these days.

Vorlon

Commented:
Hi,
You can try IIS with ODBC support (use some scripts).

Commented:
I would reccomend trying ASP.. They might have it installed...

Use something like this:

<%
Param = Request.QueryString("Param")
Data = Request.QueryString("Data")
%>
<% intCurRecord = Request.QueryString("CurRecord") %>
<%
If IsObject(Session("YourDSNHere_conn")) Then
    Set conn = Session("yourDNSHere_conn")
Else
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "YourDNSHere","",""
    Set Session("YourDNSHere_conn") = conn
End If
%>
<%
    sql = "SELECT * FROM TableNameinDB "
    If cstr(Param) <> "" And cstr(Data) <> "" Then
        sql = sql & " WHERE [" & cstr(Param) & "] = " & cstr(Data)
    End If
    ' Order by here ---> sql = sql & "ORDER BY table.whatever;"    
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
%>
put your script stuff in here... to get a field value use:
<% variable = rs.Fields("field_in_db_name") %>

<%
rs.MoveNext  ' this move to the next record
loop%>

Author

Commented:
MasseyM,
My hosting service provider does not allow ASP, nor did I ever used it.

I used another alternative: Text files.

Thanks for the info anyway..

Vorlon

Commented:
then delete your question...

Author

Commented:
MasseyM,
Normally I would but there's no option to do that. The only otpion I had was to grade it or reopen it.

Sorry for the inconvenience,
Vorlon
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.