PureBasic Forums - English

Posted: **Tue Nov 17, 2015 12:18 pm**

I would like to confirm but it seems to me that it is not possible to have 2 active queries, or more, for the same database connection.

Thank you.

Posted: **Tue Nov 17, 2015 2:39 pm**

That's right, but you can have one query and one update on the same connection.

Posted: **Tue Nov 17, 2015 3:29 pm**

Thank you very much.

Considering the fact that PB has this limitation I ask if there's any info on what will be new in the next version.

Posted: **Tue Nov 17, 2015 4:12 pm**

Lima wrote:Considering the fact that PB has this limitation I ask if there's any info on what will be new in the next version.

Considering the fact that this has absolutely nothing to do with PB...

https://dev.mysql.com/doc/refman/5.7/en ... ients.html

Multiple threads cannot send a query to the MySQL server at the same time on the same connection

AFAIK, this is basically the essential issue with most database drivers. There are algorithms and procedures to mitigate such behavior, though, depending which one in particular you use.

Posted: **Tue Nov 17, 2015 4:57 pm**

Code: Select all

; First, connect to a database with an employee table
  ;
  If DatabaseQuery(#Database, "SELECT code,name FROM Tableauthors") ; Get all the records in the 'authors' table
  
    While NextDatabaseRow(#Database) ; Loop for each records
      Debug GetDatabaseString(#Database, 0) ; Display the content of the first field (Code)     
      
          ;  At this point I want the books that  this author wrote   
           ; I need
      
             DatabaseQuery(#Database, "SELECT books FROM TableBooks where code="="+GetDatabaseString(#Database, 0)) 
             While NextDatabaseRow(#Database)
                 Debug GetDatabaseString(#Database, 0)
               
             Wend
          ;     I use this in another language
   
    Wend
  
    FinishDatabaseQuery(#Database)

What is written is not possible in PB. The example is only to illustrate

Thank you
EndIf

Posted: **Tue Nov 17, 2015 5:50 pm**

That's an typical ODBC behavior.

Solution: Read all your Queries into LinkedLists...

Posted: **Tue Nov 17, 2015 5:53 pm**

Lima wrote:What is written is not possible in PB. The example is only to illustrate

I understand what you require. First of all, be advised there is no native PB support for MySQL .

However, I think you are doing it wrong - perhaps a SQL subquery approach would settle your issue there?

Posted: **Tue Nov 17, 2015 7:17 pm**

Lima wrote:

Code: Select all

; First, connect to a database with an employee table
  ;
  If DatabaseQuery(#Database, "SELECT code,name FROM Tableauthors") ; Get all the records in the 'authors' table
  
    While NextDatabaseRow(#Database) ; Loop for each records
      Debug GetDatabaseString(#Database, 0) ; Display the content of the first field (Code)     
      
          ;  At this point I want the books that  this author wrote   
           ; I need
      
             DatabaseQuery(#Database, "SELECT books FROM TableBooks where code="="+GetDatabaseString(#Database, 0)) 
             While NextDatabaseRow(#Database)
                 Debug GetDatabaseString(#Database, 0)
               
             Wend
          ;     I use this in another language
   
    Wend
  
    FinishDatabaseQuery(#Database)

What is written is not possible in PB. The example is only to illustrate

Thank you
EndIf

Why not just open 2 connections for 2 querys....

Code: Select all

DBQuery1=OpenDatabase(#PB_Any, blah blah)
DBQuery2=OpenDatabase(#PB_Any, blah blah)

  If DatabaseQuery(#DBQuery1, "SELECT code,name FROM Tableauthors") ; Get all the records in the 'authors' table
 
    While NextDatabaseRow(#DBQuery1) ; Loop for each records
      Debug GetDatabaseString(#DBQuery1, 0) ; Display the content of the first field (Code)     
     
          ;  At this point I want the books that  this author wrote   
           ; I need
     
             DatabaseQuery(#DBQuery2, "SELECT books FROM TableBooks where code="="+GetDatabaseString(#DBQuery1, 0))
             While NextDatabaseRow(#DBQuery2)
                 Debug GetDatabaseString(#DBQuery2, 0)
               
             Wend
             FinishDatabaseQuery(#DBQuery2)
   
    Wend
 
    FinishDatabaseQuery(#DBQuery1)

Posted: **Tue Nov 17, 2015 8:15 pm**

I think two connections to the same database is the most appropriate solution for situations that I need to resolve.

The subquery would be possible in the example presented.
However the example just shows a situation in which it would be interesting the possibility of more than one query in parallel to the same database. But there are other situations where you will need to know SQL very well to solve everything in one query

A big thank you to all who helped

Posted: **Wed Nov 18, 2015 1:48 am**

@lima
You have to learn more sql , not advanced but enough to know that the advantage of having a database is having it do the selection for you. There is a time penalty when you request mysql to select something over and over again like on your loop to select the books of each author. You might want to select authors then save them to a PB MAP close connection then open again and loop MAP to select all books for all authors you selected.

or

Code: Select all

 SELECT Tablebooks.books AS books, Tableauthors.authors as authors FROM Tablebooks 
           LEFT JOIN Tableauthors ON Tablebooks.auid = Tableauthors.auid AND (Tablebooks.genre = 'war' )
         WHERE ( Tableauthors.status = 'alive'  AND Tablebooks.type = 'new')

and loop and nextdatabaserow until you are done. Double check syntax but a LEFT JOIN would do the trick.
(added some selections to give the sample a better look and assumed both tables had an 'auid' = authorid field )

Thank you.
Norm.

PureBasic Forums - English

Mysql and PB queries

Mysql and PB queries

Re: Mysql and PB queries

Re: Mysql and PB queries

Re: Mysql and PB queries

Re: Mysql and PB queries

Re: Mysql and PB queries

Re: Mysql and PB queries

Re: Mysql and PB queries

Re: Mysql and PB queries

Re: Mysql and PB queries