Как сделать таблицу в purebasic

PureBasic Forums — English

In this hard time, we have some of time for coding. Found here a module for make a customizable table with purebasic.

Version 1.1 beta 1

Install git HERE
make a right click on a directory and choice «Git Bash here»
In the Git terminal paste this command «git clone https://github.com/microdevweb/PB_TABLE»
Open the subdirectory «PB_TABLE» and run «example1.pb «

Make a right click on the directory PB_TABLE and choice «Git Bash here»
Use the command «git pull»
And choise the last branch with «git checkout version.1.1»

Code of the example

Use Pb 5.73 lst and Windows 10

my mother-language isn’t english, in advance excuse my mistakes.

Как сделать таблицу в purebasic

13.1 As I go. again

Welcome to my totally unstructured approach to databases, and may I end up where I want to be. (If I only knew where that would be :-)) I’m going to talk about SQLite and MySQL.

Creating tables
Adding columns
Deleting tables
Multiple instructions
The semicolon
Writing data
Selecting fields
Reading data
Sorting results
Number of columns
Number of rows
Changing data
Deleting data
Commit

W3Schools SQL Tutorial
SQLite SQL functions

no need for external drivers or files
fast, compact, stable
doesn’t use ODBC
multi user on NTFS on a single machine
NOT for multiuser in a network
no need to install aditional drivers

http://www.sqlite.org
PureBasic and SQLite

suitable for large databases, stable
needs installation on client as well as server side
needs ODBC due to license issues
suitable for large multi-user applications on LAN’s and WAN’s

http://www.mysql.com
PureBasic and MySQL

suitable for large databases, stable
needs installation on server side, optional installation on client side
can use either embedded driver or ODBC
suitable for large multi-user applications on LAN’s and WAN’s

http://www.postgresql.com
PureBasic and PostgreSQL

ODBC is an interface to access different sorts of databases, such as MS Access, dBase, DB2, AQL, etcetera. ODBC needs drivers, and Microsoft has included a number of them, but sometimes you might have to add one, for example for MySQL.

32 bits versus 64 bits

Windows at its best! If you run software on a 64 bits platform you may encounter the following error:

SQLite Database Browser

There’s a handy tool for checking our results when messing with SQLite databases, the SQLite Database Browser. I’m not sure if it’s actively maintained (probably not :-)) but it seems to work well for basic things. Download the .zip file and use the program inside to investigate your SQLite database files.

You can test your SQL instructions in this program using the third tab called ‘Execute SQL’. Enter the instructions and then hit the ‘Execute Query’ button.

this program seems to have some trouble using shared access, you may have to exit and restart it to give PureBasic access again to an open database
it also appears to have some troubles with multiple instructions at once (grouped together using the semicolon)
it may be using a different engine than PureBasic (I had troubles with BEGIN / COMMIT)
you may opt for the MySQL Query Browser in combination with MySQL, or the build-in PostgreSQL query browser

AKA talk to the engine.

PureBasic supports different flavours of databases. To include all routines needed for accessing SQLite databases we add to our code:

Talking to SQL

SQL is more or less a programming language, a little like PureBasic. We ‘throw’ instructions at the SQL engine, which in turn takes certain actions. PureBasic has a very limited vocubalary related to databases, as it doesn’t need much. It’s the SQL engine that does the real work, be it SQLite or MySQL or whatever other beast hides behind an ODBC interface.

These are the core commands we need to talk to the SQL engine:

DatabaseError()

DatabaseError() returns the last error the database ran into. With this we can get a textual response from the database engine which further specifies the problem we may have ran into.

DatabaseUpdate()

DatabaseUpdate() sends a command to the database engine, but doesn’t expect a reply. We use this to create tables, add colums, set fields etc. If this command returns a zero, then something went wrong (perhaps the SQL statement was wrong, or the database had problems). In those cases you can get more information about the error using DatabaseError().

DatabaseQuery()

DatabaseQuery() asks the database engine a question, and (may) trigger a number of replies. Each reply is retrieved with NextDatabaseRow(). DatabaseQuery() itself will return zero if it encountered an error.

When you are done with your query you have to call FinishDatabaseQuery() to avoid memory leaks.

SQL: Creating tables

In a database we can have multiple tables. Each table consists of rows and colums, like this:

TABLE: cars

COLUMN: firstname COLUMN: lastname COLUMN: brand COLUMN: colour

jan peter balkenende volvo black

michael schumacher ferrari red

johnny fireman mack red

mick jagger cadillac black

Each table has a name, so we can store multiple names in a database. In this case ‘cars’. Let’s create the table first. The SQLite command for creating a table with one column ‘firstname’ would be:

In PureBasic we’d have to send that instruction to the SQL engine using:

We could add columns to an existing table using this:

Multiple instructions

You could send the above to SQLite using multiple DatabaseUpdate() commands:

Of course, we can also delete tables using:

Use them. Though some dialects do not seem to care, it’s better to use them. The results of a wrongly composed SQL statement can be disastrous.

13.3 Reading and writing data

Can’t read from an empty table, so let’s start with writing. (I should have called this ‘writing and reading data’, I guess. )

We’ve build our table using CREATE TABLE and now we need to put in some data. In SQLite we use INSERT and that would look something like this:

Reading and sorting

In SQL we can read from a table using the SELECT keyword. If we would like to get all rows where the colour of the car is black, we’d use:

To return the results in a specific order, we’d add ORDER BY:

how many rows are returned upon our query?
how many columns does our selection feature?

Want to know in advance how many rows a query will return? Use the SQL COUNT function:

Changing and deleting

You can change any field using the following SQL command UPDATE. The following line changes each and every colour of the cars belonging to the person with the last name ‘balkenende’:

SQLite types.

in SQLite, any type of data may be stored in any column
SQLite tries to autoformat data according to the defined column type

Whilst SQLite converts the datatypes for us on the fly, we still need to store them into PureBasic variables. For your convenience we got a few different models on offer.

For reading we can use GetDatabaseLong() and its brethern, as listed below:

variable type read write

.b byte a.b = GetDatabaseLong() x.s = Str(a.b)

.w word b.w = GetDatabaseLong() x.s = Str(b.w)

.l long c.l = GetDatabaseLong() x.s = Str(c.l)

.i integer d.i = GetDatabaseQuad() x.s = Str(d.i)

.q quad e.q = GetDatabaseQuad() x.s = Str(e.q)

.f float f.f = GetDatabaseFloat() x.s = StrF(f.f)

.d double g.d = GetDatabaseDouble() x.s = StrD(g.d)

.s string s.s = GetDatabaseString() x.s = s.s

To be committed, or not to be committed, that’s the question

And indeed it is.

In SQLite, data is immediately stored in the database (‘autocommit’) as soon as a query or update is send. That is, if we don’t tell SQLite to wait. It is possible to group a number of instructions together, and have them executed at once. If, for some reason, we want to cancel our transaction, we can do a rollback before we commit ourselves.

Again, the Survival Guide is not about teaching you SQL or even SQLite 🙂 so have a good look at the SQLite documentation for the different transaction types.

The extracted SQLite instructions (doesn’t work when entered on the ‘Execute SQL’ panel inside the SQLite Database Browser, perhaps because that one uses an older version of SQLite?).

I never would. Never. At least not in your face 🙂

store the images elsewhere and link to them
store the images as Base64
store the images as Yenc
do the real thing with Blob’s

It may sound funny, but sometimes the right way to store images inside a database is not to store them inside the database. euh. argh! 🙂

Seriuosly, imagine you have a relative small database and a large collection of AutoCad drawings, stored on some drive. Those drawings are updated using AutoCad, so AutoCad needs access to them. It would be a pain in the . if you would have to export the drawings each and every time from the database, store them locally, edit them, then import them back into the database.

In those cases it may be a very valid option to simply create a text field with a path to the document.

One note though: it may be wise to think in advance how to deal with (absolute) paths, and what must be done if files are moved from one drive to another. Manual renaming may not be such a ‘comfortable’ option.

So, how can we store binary informaton then? We could store them in a string (though we have to make sure the resulting string complies to the rules, ie. no CHR(0) NULL characters are allowed). One way to do this is use Base64 encoding, this is an old encoding scheme used to transport binary data over media that do not support binary data. The example from the help file shows how to use this with a string as an example. Of course nothing would stop you to encode an image, just point towards the place in memory where the image is located, and pass on the length. See the help file.

(By the way, you need to switch off ‘Enable ASM Inline Support’ in ‘Compiler Options’ for the sample below, otherwise the PureBasic compiler thinks test, dec and enc are assembly instructions :-))

Yenc is the modern day version of Base64. It’s immense popular due to its use in newsgroups where it is used to spread binaries. PureBasic does not have a native Yenc onboard but you can code one.

Still not done. Sorry.

4.40b1 brought us the Blob. (Actually, I think McDonalds was first, but I might be wrong :-)) There’s little explanation in the not-yet-ready docs of the beta, but thanks to my exceptional sleuthing talents (and the answers in the forum :-)) here’s what’s going on:

A blob is a set of raw, binary data. You can store anything in it, for example images or sounds.
It may not always be the best option to store images inside your database. Design carefully.

SetDatabaseBlob()

With the statement SetDatabaseBlob() we prepare some data to be ‘blobbed’. If you look at the DatabaseUpdate() statement in the sample code above, you will find two questionmarks. We’re using these questionmarks as placeholders, and these questionmarks / placeholders will be replaced with with our blob data upon execution.

Column 0 aka blob1_data) — first placeholder / questionmark number 0 (remember: start counting at zero) — it is replaced by the data we prepared with SetDatabaseBlob() in this case the memory contents at @a
Column 1 aka nouse — we store ‘1’.
Column 2 aka blob2_data — second placeholder / questionmark number 1 — it is replaced by the data we prepared, in this case the memory contents at @b.

GetDatabaseBlob()

Zoom in on the sample code above, and look for these three lines:

The second parameter of GetDatabaseBlob() speficies the table column number. This is NOT the same as the ‘index’ used by SetDatabaseBlob().

DatabaseColumnSize()

The command DatabaseColumnSize() returns the size of the specified column of the selected row. This means you can insert a blob and later retrieve first the blob size, allocate memory, then retrieve the blob data using the following approach:

You can have multiple databases, and multiple tables. It is possible to retrieve information on a table, but this only works after you have executed a SELECT statement.

The code below uses DatabaseColums() to retrieve the number of columns, then shows the name of each column using the DatabaseColumnName() command.

Note to self: check and expand.

I f an SQL database is an essential part of your solution, you should consider PostgreSQL. MySQL has some license issues, and needs an ODBC connector.

MySQL is one of the most well-known SQL server packages. Note that there is dispute about the usability / legality in a commercial environment, due to some license restrictions. It appears to me that you can use it, but that you will have to use ODBC to avoid license issues. You may consider PostgreSQL as an alternative.

Again, this is not a tutorial on SQL, MySQL, or ODBC. I’m only interested in setting things up so I can use PureBasic to mess around with them 🙂 Still it is worth it to have another look at the issue in case we’d become too succesful 🙂

Some notes on the license issue. I’m not a lawyer (otherwise I would be working for a patent troll, be indencently rich, and not be writing this :-)) so take the following at face value. In other words, my humble opinion, your mileage may vary, at your own risk, disclaimers etc. etc. etc.

Unless you BUY a license of MySQL you need to adhere to the GPL. As far as I can tell nothing is going to stop you from using MySQL in a non-commercial or commercial project, as long as you adhere to the license. The key is the term ‘derivative work’.

1. Embedding GPL’ed SOURCE in your code would clearly force you to use the GPL license for your whole product.

2. STATIC LINKING of a GPL’ed library makes the GPL’ed code and resulting program part of your program and thus forces you to use the GPL license.

3. DYNAMIC LINKING is a bit more tricky. Strictly spoken the functionality of the DLL becomes part of your program, but the code itself does not. The jury is still out on this one. If your program would use functionality exposed by an under GPL developed and distributed DLL, which DLL was developed and distributed for such a purpose, and which DLL is not distributed as part of your program, then you’re probably not obliged to go GPL. However, it’s inside the grey zone.

4. If the whole GPL’ed program is an integral element of your solution, and you would install it (perhaps automatically) as part of your (commercial) solution, you’d be entering the grey zone as well. Most definitely if you would hide such an installation from the user (as part of a commercial package).

The above doesn’t stop you from using MySQL. Using functionality provided / exposed by MySQL ODBC connector software, or communicating with the MySQL server is not considered derivative work so should be fine. You (probably) cannot automagically install MySQL and / or the MySQL ODBC connector, and you (definitely) cannot embed a MySQL library in your program. But nothing is going to stop you from installing or using a MySQL platform as part of a commercial and / or non-GPL’ed solution, it just cannot be part of your program.

The above was written in 2010. Who knows what has changed since then, so go and check that license before doing something stupid 🙂

13.22 Installation server side.

You might want to run this on a dedicated machine, server, or virtual machine. I used VirtualBox to setup such a ‘dedicated’ MySQL server on a virtual machine. Read more about using VirtualBox here. MySQL needs one side to be a server, and the other to be a client, but nothing is stopping you to install server and client software on the same machine. (Frankly, if you’re just going to use MySQL as a simple local database, there’s very little reason to use a dedicated machine, but then again why are you not using SQLite then?)

There are complete packages around including tools, configuration etc. such as Xampp. They may make your life easier, especially if you’re interested in building applications for the web. I may revisit Xampp once I find the courage to use PureBasic for web applications. For now, it’s though enough as it is 🙂

Machine and Windows

Create a VM if you’re going to use VirtualBox or something similar. You may consider assigning a fixed IP to your server. I changed the name of my dedicated MySQL server to ‘sqlserver’.

MySQL 5.1.38 and MySQL Essentials 5.1

1. Download the MySQL server essentials package and install it. I used 5.1 and ticked the option ‘custom’ as I like to think of myself as an expert (which, obviously, I am not :-)). Note: when reinstalling MySQL it couldn’t start the service, no matter what I tried. In the end, I cloned a new VM and reinstalled. (I should have used a snapshot in VirtualBox but I forgot to create one :-)) I think I messed up a password which wasn’t removed during de-installation, but that’s the good thing about imaging and / or virtual machines. restoring is a lot easier.

2. The default port is 3306. Tick the box ‘add firewall exception’ if you’re using Windows firewall. Note: this may not suffice, and you might have to allow traffic manually! When in doubt, check functionality by shutting down the firewall temporary. Oh, and if you are using the host name on the client to find the server, enable ‘file and printer sharing’ on that server otherwise the client won’t find the host!

3. Tick ‘best support for multilingualism’. Install as a Windows service and have it launched automatically. Also tick the option ‘include bin directory in path’. Next etc.

4. Modify security settings and enter a new root password. For simplicity I used the password ‘root’ here. not very safe, but this is a test environment, not a production server. Next etc.

5. Hit the ‘execute’ button and hope it doesn’t crash during installation. (It did here twice. ) Next etc.

Tada. You’ve now got your own MySQL server up and running as a service on your virtual machine. Congratulations.

Don’t forget to open the appropriate port(s) in your firewall.

MySQL GUI Tools 5.0

Note: these seem to have been replaced by the MySQL Workbench.

These tools make your life a little easier, unless you’re a die-hard that likes the command prompt.

1. Download and install the GUI tools package and install it. I used 5.0 and did install all except the MySQL migration toolkit. Next, install, finish etc, you know the drill.

2. Under Windows Start / Programs you’ll find a new folder MySql. Start the MySQL Tray Monitor. Click on it with the RMB.

3. Switch ON the option Monitor Options / Launch Monitor After Login.

4. There’s another tool you’ll find there called MySQL Administrator. You may want to drag it onto the desktop for quick access.

You can now test your setup.

Note that you can install the MySQL GUI Tools on your clients, if you want to. Especially the MySQL Query Browser may come in handy if you want to experiment with the SQL language itself.

MySQL Workbench 5.2 CE

This seems to have replaced the MySQL GUI Tools. I’ve tried this one on a client to execute queries and it worked fine.

server host: localhost
port: 3306
username: root
password: root

Adding a user

Start the MySQL Administrator on your new SQL server and log in as root. Select Use Administration / Add New User. I added a new user called ‘user’ with password ‘user’. (Yes, I’m a very creative person.)

Creating a database

server host: localhost
port: 3306
username: root
password: root
default schema: test

Obvously, you should only create it once. executing CREATE DATABASE PUREBASIC a second time will throw an error. It already existed 🙂

In all future calls we might use the MySQL Query Browser with ‘default schema’ set to purebasic, we just needed that build-in ‘test’ database this one time to start up the query browser and create our own first database. The program is supposed to let us connect to the MySQL service without a database given, but I didn’t get through without one.

In the 2013 MySQL Workbench I received an error. Adding semicolons fixed it:

13.23 Installation client side

There is more than one way to talk to the MySQL server, but from within PureBasic the easiest one is using ODBC.

MySQL ODBC Connecter

1. Download the MySQL ODBC connector for Windows and run it.

2. Look for Start / Programs / Administrative Tools / Data Sources (ODBC) and start it. This tool may be located somewhere else on your machine, for example on my Windows 7 box it could be accessed via Control Panel / Data Sources (ODBC).

data source name: mysql
server: sqlserver (this is the name I gave my dedicated MySQL server)
posrt:3306
user: root
password: root
database: purebasic

64 bit users be aware! There are TWO different versions of ODBC on your machine!

Typically if you run into the following error, you’re using the wrong one (mostly by trying to access the 64 bits ODBC from a 32 bits application):

Anyway, if things worked out well, you should be seeing something like this:

4. Hit the ‘Test’ button. If the ODBC connector cannot connect to our MySQL Server then most likely a firewall is causing the problem, either on the client or on the server. Try it with the firewalls turned off. If that works, you might test with the IP address of the server instead of its name, ortry

5. Run the program below. It should create a little table and produce the same results as our SQLite version.

Of course, you could also install the MySQL Workbench on your client machine, to verify the results of your code, and experiment with SQL queries outside of PureBasic.

13.24 SQLite to MySQL

From a PureBasic and SQL point of view there is little difference between MySQL and SQLite. It’s important to keep in mind that MySQL is more strict on data types.

install the neccessary software on server and client
create a database and users on the server
speficy the right database on the client (ODBC configuration)
remove the creation of a local file (not needed for MySQL)
replace UseSQLiteDatabase() with UseODBCDatabase()
change the parameters for the OpenDatabase() command
add semicolons
use BEGIN and COMMIT

In SQLite with a single user application you might skip BEGIN / COMMIT but in multi user applications in a network I would strongly advise to use them.

PostgreSQL is ‘the other’ open source database, but it doesn’t suffer from GPL issues. This means that PostgreSQL drivers / libraries can be linked with / embedded in other programs. Which is exactly what PureBasic did 🙂

You will always need to install PostgreSQL on the server side. You may chose to use ODBC on the client side, or use the onboard drivers of PureBasic.

I found the installation and configuration of MySQL marginally easer, and the MySQL GUI tools are nice, especially the MySQL Query Browser. However PostgreSQL contains a similar tool, and if you look around on the Internet you’ll find some alternatives, I’m sure.

13.26 Installation server side

PostgreSQL needs one side to be a server, and the other to be a client, but nothing is stopping you to install server and client software on the same machine. (Frankly, if you’re just going to use PostgreSQL as a simple local database, there’s very little reason to use a dedicated machine, but then again why are you not using SQLite then?)

Machine and Windows

Create a VM if you’re going to use VirtualBox or something similar. You may consider assigning a fixed IP to your server. I created a new VM and changed its name to ‘sqlserver’. In fact, I installed MySQL and ProgreSQL on the same VM without any problems.

PostgreSQL 8.4.1

1. Download PostgreSQL 8.4.1. Use the regular package and install it.

2. The default port is 5432. Choose eventual passwords wisely. (I did not, so ‘postgres’ it is, everywhere :-)) Install.

3. Look for a file called ‘pg_hba.conf’. It’s in the PostgreSQL folders somewhere. If your local network runs in the 192.168.0.x range, then you will have to add the following line:

5. You may want to put the link ‘Postgress pgAdmin III’ on your desktop for easy access.

6. Start up pgAdmin. Connect to (doubleclick) the PostgreSQL server (localhost port 5432 user postgres password postgres).

7. RMB on ‘databases’ and add a new database called ‘purebasic’.

Don’t forget to open the appropriate port(s) in your firewall.

The PostgreSQL query browser

PostgreSQL also includes a query browser. To use it do the following:

1. Start up pgAdmin.

2. Select ‘purebasic’ under ‘Databases’.

3. EIther select Tools / Query tool, or hit [Control] + [E]. Note that some commands that work under MySQL don’t work on PostgreSQL.

13.27 Installation client side

Seriously, you could do an ODBC client side install, but PureBasic already contains the PostgreSQL library, so there’s no real need.

Use the following code to check if your PostgreSQL setup is working:

Of course, I couldn’t help myself and had to try 🙂

1. Get the ODBC connector software, the one I used I found on the PostgreSQL website, under file browser / odbc / versions / msi / psqlodbc_08_04_0100.zip.

2. Unzip and install it.

2. Look for Start / Programs / Administrative Tools / Data Sources (ODBC) and start it. This tool may be located somewhere else on your machine.

data source: postgresql
server: sqlserver
port: 5432
user: postgres
password: postgres
database: purebasic

6. Try the following program:

13.28 MS SQL Express 2008

Well, there should be no reason why not to try MicroSoft’s latest 🙂 unfortunately I’m running XP in the VM, so I can’t test it with 2012. Let’s try it with an older version then: SQL Express 2008 on Windows XP.

Server side

Unfortunately, it’s not as easy to setup. I’ve got no clue about all those MS SQL options, but the ones below got me started. Information on this page helped me to get started.

Note that I wanted explictly to communicate with the database via TCP, port, and SQL user / password. If you’re using NT authentication this just might be a lot easier.

create a VM or build a real server
make sure the VM has at least 256 MB (I managed to run MySQL, PostGreSQL and MS SQL simultaneously in 384 MB, let’s not talk performance :-))
download and install .NET 3.5 SP1, you might have to reboot
download and install Windows Power Shell 1.0
download and install Windows Installer 4.50
do a Windows update to see if you’ve missed any patches (I got lots of ‘m, took ages to install)
download and install MS SQL Express 2008
in the SQL Server Installation Center, pick ‘stand-alone installation’
next accept next etc. etc.
Feauture Selection / Database Engine Services
Server Configuration / Account: NT AUTHORITY\SYSTEM
Database Engine Configuration: Mixed Mode, add a password (for example ‘apekop’), add some users
next / next / install etc. and finish installation
Start / Programs / Microsoft SQL Server 2008 / Configuration Tools / SQL Server Configuration Manager
RMB on SQL Server Network Configuration / Protocols, enable
RMB on SQL Server Network Configuration / Protocols, properties
tab Protocol
1. Enabled: yes
2. Listen All: yes
1. IP1 / Active: yes
2. IP1 / Enabled: yes
3. IP1 / IP Address: 192.168.0.71 (my server’s address)
4. IP1 / TCP Dynamic Ports: empty
5. IP1 / TCP Port: 1433
6. IP2 / Active: no
7. IP2 / Enabled: no
8. IPAll / TCP Dynamic Ports: empty
9. IPAll / TCP Port: 1433
1. connect to a database
2. Server name: 192.168.0.71
3. Authentication: SQL Server Authentication
4. Login: sa
5. Password: apekop
1. New Query
2. CREATE DATABASE purebasic
Client side
1. Add SQL Server
2. Name: sqlexpress (this is the name I used in my PureBasic code)
3. Server: 192.168.0.71
4. Next
5. select SQL Server authentication
6. Client Configuration
  1. Server alias: 192.168.0.71
  2. TCP/IP
  3. Server name: 192.168.0.71
  4. unselect Dynamically determine port (you may have to click a few times before you can enter a port number)
  5. Port number: 1433
  6. Ok
  Use the following code to check if your SQL Express 2008 setup is working:
  Похожие публикации:

Как сделать таблицу в purebasic

PureBasic Forums — English

Как сделать таблицу в purebasic

Добавить комментарий Отменить ответ