Connect to a database

Just starting out? Need help? Post your questions and find answers here.
berdeja
Posts: 2
Joined: Wed Oct 04, 2017 1:49 pm

Connect to a database

Post by berdeja »

it is possible to connect to a database like MySql
as used in vb via Recordset !Field
User avatar
Peter
Posts: 1086
Joined: Mon Feb 24, 2014 10:17 pm
Location: 127.0.0.1:9080
Contact:

Re: Connect to a database

Post by Peter »

berdeja wrote:it is possible to connect to a database like MySql as used in vb via Recordset !Field
For this you need a serverside component like CGI, PHP, ASP, ... and than you can connect it with HTTPRequest().

Greetings ... Peter

P.S.: Please do not use large fontsizes in your postings.
loulou2522
Posts: 51
Joined: Wed Mar 18, 2015 5:52 am

Re: Connect to a database

Post by loulou2522 »

HI peter,
May i have a real example ?
Thanks
User avatar
Peter
Posts: 1086
Joined: Mon Feb 24, 2014 10:17 pm
Location: 127.0.0.1:9080
Contact:

Re: Connect to a database

Post by Peter »

It's not easy to post a working example. Are you familiar with PHP? Or can you run a PB CGI on your web server? Do you have a MySQL database that you can connect to?
loulou2522
Posts: 51
Joined: Wed Mar 18, 2015 5:52 am

Re: Connect to a database

Post by loulou2522 »

Hi Peter,
I am not familiar with PHP but i understand PHP and can programm in this language.
1 - I have a mysql database on my personnal website which is on an IIS server a
2 - I access these mysql database in ASP programming
3- I can execute a cgi on my local webserrver
Thanks
User avatar
Paul
Posts: 195
Joined: Wed Feb 26, 2014 6:46 pm
Location: Canada
Contact:

Re: Connect to a database

Post by Paul »

Since you can run CGI programs, here is a quick example using PureBasic to make the CGI and SpiderBasic to call it.
I have a MySQL database on the Server called "mydatabase" with a table called "myusers" and two columns, "username" and "email"

The PureBasic CGI is compiled as sqlcgi.exe and placed in CGI-BIN folder on server along with marialibdb.dll (my CGI is compiled as 64bit so the 64bit version of the DLL is used)

Code: Select all

UseMySQLDatabase(GetCurrentDirectory()+"libmariadb.dll")
If Not InitCGI() Or Not ReadCGI()
  End
EndIf


user$=CGIParameterValue("username")

sql=OpenDatabase(#PB_Any,"host=localhost port=3306 dbname=mydatabase","myusername","mypassword",#PB_Database_MySQL)
If sql
  If DatabaseQuery(sql,"select email from myusers where username='"+user$+"';")
    If NextDatabaseRow(sql)
      result$=GetDatabaseString(sql,0)
    EndIf
  EndIf 
  CloseDatabase(sql)
  Else
  result$=DatabaseError()
EndIf 

WriteCGIHeader("Access-Control-Allow-Origin", "*")  ;<----------- Remove for Final Release  !!!!!!
WriteCGIHeader(#PB_CGI_HeaderContentType,"text/html",#PB_CGI_LastHeader)      
WriteCGIString(user$+" : "+result$) 
And the SpiderBasic app that talks to the CGI...

Code: Select all

#Window_Main=1

Enumeration 1
  #Gadget_Main_Text
  #Gadget_Main_User
  #Gadget_Main_Search
EndEnumeration

Procedure.i Window_Main()
  If OpenWindow(#Window_Main,0,0,200,150,"Email Search",#PB_Window_SystemMenu|#PB_Window_ScreenCentered|#PB_Window_Invisible)
      TextGadget(#Gadget_Main_Text,25,20,150,20,"Enter User Name:")
      StringGadget(#Gadget_Main_User,25,45,150,20,"")
      ButtonGadget(#Gadget_Main_Search,115,85,60,20,"Search")
      HideWindow(#Window_Main,#False)
    ProcedureReturn WindowID(#Window_Main)
  EndIf
EndProcedure


Global url.s="https:/mydomain.com/cgi-bin/sqlcgi.exe"



Procedure HttpGetEvent(Success, Result$, UserData)
  If Success
    MessageRequester("Results: "+result$)
    Else
    Debug "Error: No Connection"
  EndIf
EndProcedure
 
;- GadgetEvent Loop
Procedure GadgetEvent()
  GadgetID=EventGadget()

  Select GadgetID
    Case #Gadget_Main_Search
      HTTPRequest(#PB_HTTP_Post, url, "username="+GetGadgetText(#Gadget_Main_User), @HttpGetEvent())
  EndSelect
EndProcedure

;- Main Loop
If Window_Main()
  BindEvent(#PB_Event_Gadget,@GadgetEvent())
EndIf
loulou2522
Posts: 51
Joined: Wed Mar 18, 2015 5:52 am

Re: Connect to a database

Post by loulou2522 »

HI Paul,
I follow your recommandation on installing CGI. I verify in Purebasic that i can access to mysql Database (it's right)
When I launch the programm on localhost in SpiderBasic it refuse to execute saying ""Error: No Connection"
I try the same thing on my website and the result was the same. Have you anidea about these problem and how to solve it ?
Thanks
User avatar
Paul
Posts: 195
Joined: Wed Feb 26, 2014 6:46 pm
Location: Canada
Contact:

Re: Connect to a database

Post by Paul »

Did you compile as 32bit or 64bit and what does your server require?
Did you copy the appropriate 32 or 64bit version of marialibdb.dll as well?
Is your server set up to execute a CGI app from the specified folder? (enabled EXE CGI extensions)

What is the response if you call it straight from your web browser?
http://domain.com/cgi-bin/sqlcgi.exe
loulou2522
Posts: 51
Joined: Wed Mar 18, 2015 5:52 am

Re: Connect to a database

Post by loulou2522 »

THanks Paul,
With an exe from purebasic

Code: Select all

UseMySQLDatabase()

If Not InitCGI() Or Not ReadCGI()
  End
EndIf

user$=CGIParameterValue("username")
sql=OpenDatabase(#PB_Any,"host=xxx  port=3306 dbname=xxxx","xxx",xx",#PB_Database_MySQL)
If sql
  If DatabaseQuery(sql,"select user from login_user where nom='"+user$+"';")
    If NextDatabaseRow(sql)
      resultat$=GetDatabaseString(sql,0)   
     Else 
      resultat$=""
    EndIf
  EndIf 
  CloseDatabase(sql)
Else
  resultat$=DatabaseError()
EndIf 
WriteCGIHeader("Access-Control-Allow-Origin", "*")  ;<----------- Remove for Final Release  !!!!!!
WriteCGIHeader(#PB_CGI_HeaderContentType,"text/html",#PB_CGI_LastHeader)   
WriteCGIString(user$+"="+resultat$) 
I have a problem because the instruct
WriteCGIString(user$+"="+resultat$)
return the value of user$ but no the value of resultat$
User avatar
Peter
Posts: 1086
Joined: Mon Feb 24, 2014 10:17 pm
Location: 127.0.0.1:9080
Contact:

Re: Connect to a database

Post by Peter »

Post Reply