Brand new to spiderbasic

Just starting out? Need help? Post your questions and find answers here.
collectordave
Posts: 3
Joined: Mon Nov 23, 2020 9:00 am

Brand new to spiderbasic

Post by collectordave »

Hi All

I have a database programme written in PureBasic.

The database is quite large.

How would I get to grips with this in SpiderBasic?

Download the database to the computer memory?

Then use it.

Can the database then be stored on the local computer and loaded simply each time it starts.

Probably a basic question but any help appreciated.

CD
User avatar
Peter
Posts: 1086
Joined: Mon Feb 24, 2014 10:17 pm
Location: 127.0.0.1:9080
Contact:

Re: Brand new to spiderbasic

Post by Peter »

When you program web applications, you have to rethink a bit. Here it is unusual for the client (browser) to access a local database directly. The database is usually located on the server and the client accesses it using HTTPRequest() (in conjunction with a server programming language (PHP, ASP, CGI, ...)).

Greetings ... Peter
collectordave
Posts: 3
Joined: Mon Nov 23, 2020 9:00 am

Re: Brand new to spiderbasic

Post by collectordave »

So if i get this right.

Spider basic cannot open a local database.

Spiderbasic cannot download a database from an internet site.

Or I also need to learn php etc to get it to work?
User avatar
Peter
Posts: 1086
Joined: Mon Feb 24, 2014 10:17 pm
Location: 127.0.0.1:9080
Contact:

Re: Brand new to spiderbasic

Post by Peter »

collectordave wrote:Spider basic cannot open a local database.
No. SpiderBasic supports local databases in SQLite format. See: https://www.spiderbasic.com/documentati ... index.html

But as written: They are local databases. If User A makes a change to the database, User B does not notice it. And if User A is using another computer or opens another browser, the changes are not visible there either.
collectordave wrote:Spiderbasic cannot download a database from an internet site.
No. SpiderBasic can download any file from the server (if the server allows it). But also here it does not make sense to download a (large) database and upload it again when changes are made.
collectordave wrote:Or I also need to learn php etc to get it to work?
That is an advantage in any case. :)

You could also have a look at SpiderBite. So you can write database accesses on the server with PureBasic. But here you must have a web server on which CGI can be executed.
collectordave
Posts: 3
Joined: Mon Nov 23, 2020 9:00 am

Re: Brand new to spiderbasic

Post by collectordave »

No one mentioned uploading the database again!

The database in question is filled with information of use to individuals and any changes they make to the database are their personal changes not to be shared with other users so the database is never 'uploaded again'.

It is quite a simple operation.

If database exists prompt user if update required if so download the database.

If database does not exist allow user to download view the data and make their personal changes etc.

Attempting to download the database to disk using SpiderBasic just returns an error code whereas simply typing the url into the address bar downloads the file to disk.

Even if the file was downloaded I can find no commands that will copy the local database to memory and allow it to be opened.

The open database command expects a buffer address not a filename.

CD
the.weavster
Posts: 220
Joined: Sat Mar 01, 2014 3:02 pm

Re: Brand new to spiderbasic

Post by the.weavster »

If it's a web app and you store the database locally it will be gone if the user clears their browser cache. It really is better to have the database on the server. I suggest you DuckDuckGo "JSON-RPC" for an outline of how you can make SpiderBasic exchange data with a server side language (PB, PHP, ...)

Despite the rants you find online imo PHP is actually quite a nice, productive scripting language.
User avatar
Peter
Posts: 1086
Joined: Mon Feb 24, 2014 10:17 pm
Location: 127.0.0.1:9080
Contact:

Re: Brand new to spiderbasic

Post by Peter »

collectordave wrote:Attempting to download the database to disk using SpiderBasic just returns an error code whereas simply typing the url into the address bar downloads the file to disk.
First of all, you should make sure that your web server allows files to be downloaded using JavaScript.

For further informations see: viewtopic.php?p=7669#p7669
collectordave wrote:The open database command expects a buffer address not a filename.
No, you can use a database that is stored in your browser's LocalStorage. You can use a file name for this (see also the example with a persistent database (https://www.spiderbasic.com/documentati ... nt.sb.html)).

With the following code you can download a database from your server (remember the CORS problem from above) and save it under a file name of your choice in the LocalStorage. Afterwards you use the regular SpiderBasic database commands.

Code: Select all

Procedure DownloadFileAndSaveToLocalStorage(DatabaseUrl.s, LocalDatabaseFilename.s, Callback)
  
  ! var oReq = new XMLHttpRequest();
  ! oReq.open("GET", v_databaseurl, true);
  ! oReq.responseType = "arraybuffer";
  ! 
  ! oReq.onload = function(oEvent) {
  !   var arrayBuffer = oReq.response;
  !   var byteArray = new Int8Array(arrayBuffer);
  !   LocalForage.setItem('sbfs_' + v_localdatabasefilename, byteArray);
  !   v_callback();
  ! };
  ! 
  ! oReq.send();  
  
EndProcedure

Procedure ReadFileCallback(Status, Filename$, File, SizeRead)
  
  Select Status
      
    Case #PB_Status_Loaded

      Debug "Database file found and loaded: " + Filename$

      *DatabaseBuffer = ExportFileMemory(File)

      If OpenDatabase(0, *DatabaseBuffer)
        Debug "OpenDatabase(): Success"
        CloseDatabase(0)
      Else
        Debug "OpenDatabase(): Failed"
      EndIf
      
    Case #PB_Status_Error
      
      Debug "Database not found in localstorage: " + Filename$
      
  EndSelect
  
EndProcedure

Procedure ReadDownloadedFile()
  
  ReadFile(0, "test.db", @ReadFileCallback(), #PB_LocalStorage)
  
EndProcedure

DownloadFileAndSaveToLocalStorage("[UrlToYourDatabase]", "test.db", @ReadDownloadedFile())
pecket
Posts: 4
Joined: Wed Jun 26, 2019 6:32 pm

Re: Brand new to spiderbasic

Post by pecket »

Peter wrote: Mon Nov 23, 2020 2:35 pm
collectordave wrote:Spider basic cannot open a local database.
No. SpiderBasic supports local databases in SQLite format. See: https://www.spiderbasic.com/documentati ... index.html

But as written: They are local databases. If User A makes a change to the database, User B does not notice it. And if User A is using another computer or opens another browser, the changes are not visible there either.
collectordave wrote:Spiderbasic cannot download a database from an internet site.
No. SpiderBasic can download any file from the server (if the server allows it). But also here it does not make sense to download a (large) database and upload it again when changes are made.
collectordave wrote:Or I also need to learn php etc to get it to work?
That is an advantage in any case. :)

You could also have a look at SpiderBite. So you can write database accesses on the server with PureBasic. But here you must have a web server on which CGI can be executed.
Sorry for necroing this old thread, but I just found Spiderbite (been quite passive since registration, awaiting for more features) and this is a work of art. Completely awesome, thank you!
bmld76
Posts: 26
Joined: Wed May 24, 2023 5:10 pm

Re: Brand new to spiderbasic

Post by bmld76 »

Hello, that Is an exemple, it is how I access to my MySql database with php. I serialize data with PHP like this : § field separator end / data separator

data1/dat2/data3/data4§ data1/dat2/data3/data4§ data1/dat2/data3/data4§.......

I use the procedure splitstring to unserialize.

To test you must work on the server so I create a DebugApp, It is a debug windows on line.

Enjoy

SpiderBasic exemple

Code: Select all


; Exemple d'acces à une bae MySql

Enumeration Gadget 
  #ListeCaisse
  #FenetreCaisse
EndEnumeration

; This procedure unserialise the return text
Procedure.i SplitString(String.s, Delimiter.s, Array Output.s(1))
  Define n, i
  n = 1 + CountString(String, Delimiter)
  Dim Output.s(n - 1)
  For i = 0 To n-1
    Output(i) = Trim(StringField(String, 1+i, Delimiter))
  Next i
EndProcedure

; useful function to debug online
Procedure DebugApp(Texte1.s,Texte2.s)
  OpenWindow (#PB_Any,10,500,1000,100,"Debug")
  ScrollAreaGadget(#PB_Any,0,0,1000,100,1000,500)
  TextGadget(#PB_Any,3,3,980,25,texte1)
  TextGadget(#PB_Any,3,20,980,25,texte2)
  CloseGadgetList()
EndProcedure  

Procedure CloseWindowEvent()
  CloseWindow(EventWindow()) ; Close the specific window
EndProcedure

Procedure HttpGetCaisse(Success, Result.s, UserData)
  ; traitement menu en vente direct
  Define i, j, k, MaxItem
  Dim Requete.s(0)
  Dim Donnee.s(0)
  If Success
    SplitString(Result, "§", Requete())  
    MaxItem = Val(Requete(0)) ; nombre d'item useful or not
    For i = 1 To ArraySize(Requete())-1
      SplitString(Requete(i), "/", Donnee())  ; separe les données de l'enregistrement
      AddGadgetItem(#ListeCaisse, -1, Donnee(0)+Chr(10)+Donnee(4)+Chr(10)+Donnee(1)+Chr(10)+Donnee(2)+Chr(10)+Donnee(3))
    Next
  Else
    debugApp ("Erreur caisse","")
  EndIf
  
EndProcedure

  OpenWindow( #FenetreCaisse, 10, 10, 600, 600, "Caisse "+ FormatDate("%dd-%mm-%yyyy %hh:%ii", Date()) , #PB_Window_SystemMenu | #PB_Window_ScreenCentered ) 
  ListIconGadget(#ListeCaisse,10,10,600-20,600-20,"ID",10)
  AddGadgetColumn(#ListeCaisse, 1, "Date", 30)
  AddGadgetColumn(#ListeCaisse, 2, "Montant", 30)
  AddGadgetColumn(#ListeCaisse, 3, "Caisse", 20)
  AddGadgetColumn(#ListeCaisse, 4, "Ope", 100) 
  BindEvent(#PB_Event_CloseWindow, @CloseWindowEvent())

HTTPRequest(#PB_HTTP_Get, "requete_caisse.php", "", @HttpGetCaisse())  ; initialse la caisse
    
    

PHP exemple

Code: Select all

<?php
/*
echo '<pre>';
print_r($_POST);
print_r($_GET);
print_r($_SESSION);
echo '</pre>';
*/

include ("../../intranet/commun/connect.php");

$db = mysql_connect($dbhost, $dbuname, $dbpass)or die('Erreur de connexion '.mysql_error());
mysql_select_db($dbname,$db) or die('Erreur de selection '.mysql_error());

$dt = new \DateTime();

$sql = "SELECT id,date,montant,solde,ope FROM  delices.caisse WHERE date ='".$dt->format('d-m-Y')."' ORDER BY id DESC" ;
//echo "$sql<br>";
$req = mysql_query($sql) or die('<br />Erreur SQL5 56 !<br>'.$sql.'<br>'.mysql_error());
echo mysql_num_rows($req)."§";  //retourne le nombre d'enregistrement 

while($tab = mysql_fetch_object($req))
{
	echo $tab -> id;
    echo "/";
	echo $tab -> montant;	
    echo "/"; 
	echo $tab -> solde;
    echo "/"; 
	echo $tab -> ope;
    echo "/"; 	
   	echo $tab -> date;
    echo "/"; 			  
	echo "§"; // fin enregistrement
}

?>
</div>
</body>
</html>
Post Reply