Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2016
    Posts
    8

    Unhappy Unanswered: Fetching Data from SQL Server

    HI Brilliant Database users

    I have an external Database Connection to Microsoft SQL Server 2014. My aim is to retrieve the data from that server into Brilliant database and all I did is

    1. Connect to External Database (gave the path to the server which follows "odbc;Microsoft ODBC Driver 11 for SQL Sever;SERVER=server_name;Database=DB_Test;") I did not specify authentication details because i access it through windows authentication.
    2. Inserted an External Database Query (SELECT * FROM Test_Table)
    3. Close External DB

    Now when i click on the button to fetch the records it prompts me to select a Data Source (DSN file), then i have to create one, following the instructions given;
    1. Choose the Driver (in my case is ODBC Driver 11 for SQL Server)
    2. Give the file name
    3. Choose the server name (I had to choose the appropriate one from the list provided)
    4. Finally test the connection and proved that is successfull

    The problem is, every time i click on the view data button, it wants me to select that DSN file. Is there no way to make it automatically use that same DSN file every time i want to execute the query other than selecting it time and again?

    Now the main problem i face is, after i choose the DSN File nothing is happening. it does not give the records i want and it does not even give error notifications either.

    At first i thought it is a connection problem and i challenged it by giving a non-existing database name on External database connection string/path but it proved me wrong by producing a connection error notification. (i had to correct that)
    I then tested connection by closing External DB before the SQL Query, and it produced an error notification that the SQL Query cannot be executed because there is no connection (I corrected that as well). And that was enough to show me that my connection to the database is fine

    But now why is it just quite? where are the records am trying to fetch? (There are three records from the server).
    Can someone please help me on how to view the data fetched from the server if at all my SQL Query is fine.

    I guess the solution will lead me into doing the INSERT INTO server from Brilliant server as well

    Thank you in advanced

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I have zero experience with Brilliant, but I know that for ODBC connection strings you need either "Trusted_Connection=yes" or "Username=<your user name>" which usually also requires "Password=<the user names password>" in order for authentication to work.

    If you are running Brilliant Server, keep in mind that the ODBC conenction will use the Windows Credentials of the account that runs the server, which is probably not your developer account or the end user's account.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2016
    Posts
    8
    Quote Originally Posted by Pat Phelan View Post
    I have zero experience with Brilliant, but I know that for ODBC connection strings you need either "Trusted_Connection=yes" or "Username=<your user name>" which usually also requires "Password=<the user names password>" in order for authentication to work.

    If you are running Brilliant Server, keep in mind that the ODBC conenction will use the Windows Credentials of the account that runs the server, which is probably not your developer account or the end user's account.

    -PatP
    I have now tried to specified the the UID and the PWD, and instead of using the "External DB SQL Query" I used the "For each... Cycle" as per an example on the link http://www.brilliantdatabase.com/onl...l/cycles.htm#5. It is still behaving the same way.

    I now have a feeling that i need to specify the area like maybe "Multiline text field" where the results will be displayed OR the main question is "Where will the records from the server be displayed?"

  4. #4
    Join Date
    Mar 2016
    Posts
    8

    Unhappy

    Quote Originally Posted by Pat Phelan View Post
    I have zero experience with Brilliant, but I know that for ODBC connection strings you need either "Trusted_Connection=yes" or "Username=<your user name>" which usually also requires "Password=<the user names password>" in order for authentication to work.

    If you are running Brilliant Server, keep in mind that the ODBC conenction will use the Windows Credentials of the account that runs the server, which is probably not your developer account or the end user's account.

    -PatP
    I now tried include the UID and PWD putting the "Trusted_Connection=Yes" it still behaving the same. I then used the "For each record from SQL Query (External DB) cycle" as per the example on link http://www.brilliantdatabase.com/onl...l/cycles.htm#5 and still no improvement

    Now i have a feeling that maybe i should specify something like record set or multiline field where the fetched data will be displayed, and i for now do not have a clue on how to do that (if at all that's what i should do to display the data)

  5. #5
    Join Date
    Mar 2016
    Posts
    8
    I now included the UID and the PWD in the connection string, I also tried the "Trusted_Connection=yes;" but it still behaves the same. I also used instead of the "External DB SQL Query" the "For each record SQL Query Cycle" as per an example on the link http://www.brilliantdatabase.com/onl...l/cycles.htm#5 but still getting the same behavior.

    I now have a feeling that i need to specify somewhere on the interface where the results should be display after being fetched from the external database. maybe something like a "multiline text field" but do not have a clue on that one

  6. #6
    Join Date
    Jan 2014
    Posts
    18
    I played with SQL connection long time ago... This worked fine for me. BDB and MySQL database was on the same computer.

    To connect:
    1 [$resault] = Connect to External DB (odbc;DRIVER={MySQL ODBC 5.1 Driver};SERVER=XXX.X.X.1;DATABASE=test;UID=root;PW D=XXXXXX;OPTION=3;, country_list)
    2 If "BDB form" And ([$resault] = "1") then
    3 Show Message (Connected!, Connected!, Ok)
    4 Else
    5 Show Message (Failed!, Failed!, Ok)
    6 End If

    To disconnect:
    1 Close External DB ()

    To Write to SQL:
    1 External DB SQL Query (UPDATE country_list<BR>SET id='[id]', country='[country name]'<BR><BR>, Line Break, Comma (,))

    To read from SQL:
    1 [$resault] = External DB SQL Query (select country<BR>from country_list<BR>where id = '[id]', Line Break, Comma (,))
    2 Set field value: [field name] = "[$resault]"

    Hope this helps.
    rimpet

  7. #7
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    Hi
    From memory you can use reports to export data. Its awhile since I have worked with Longtion but fairly sure the demo database has example under "basic report" with options to export to PDF, text and XLS. I would have to revisit program but again from memory there were file reading functions and text manipulation functions that should allow import from csv files. I don't recall a direct import operation. One way around this might be to use an external program for import if you are using MDB files. The same might apply if using an OBDC connection to MYSQL or the like. I found MDB easier but started to explore OBDC with some problems (lack of experience). One problem to watch here is creating new records and trying to overwrite auto-id's which creates an error. Keep these fields off form (use calculated) so Longtion does not try to write to locked fields in table.
    Like I said have not used for a while - focused more on Android databases currently.
    Regards
    David

  8. #8
    Join Date
    Mar 2016
    Posts
    8
    Quote Originally Posted by rimpet789 View Post
    I played with SQL connection long time ago... This worked fine for me. BDB and MySQL database was on the same computer.

    To connect:
    1 [$resault] = Connect to External DB (odbc;DRIVER={MySQL ODBC 5.1 Driver};SERVER=XXX.X.X.1;DATABASE=test;UID=root;PW D=XXXXXX;OPTION=3;, country_list)
    2 If "BDB form" And ([$resault] = "1") then
    3 Show Message (Connected!, Connected!, Ok)
    4 Else
    5 Show Message (Failed!, Failed!, Ok)
    6 End If

    To disconnect:
    1 Close External DB ()

    To Write to SQL:
    1 External DB SQL Query (UPDATE country_list<BR>SET id='[id]', country='[country name]'<BR><BR>, Line Break, Comma (,))

    To read from SQL:
    1 [$resault] = External DB SQL Query (select country<BR>from country_list<BR>where id = '[id]', Line Break, Comma (,))
    2 Set field value: [field name] = "[$resault]"

    Hope this helps.
    rimpet
    Than you so much your honor, i followed it and its really sounding. One last question on this one is "i see you have used some variables there mainly [$resault] and "BDB form". what those two? are they some buttons in your form or some items i didn't add in my form?

    Hope you will help on that one

    Thank you so much

  9. #9
    Join Date
    Jan 2014
    Posts
    18
    Quote Originally Posted by David Tjinjeka View Post
    Than you so much your honor, i followed it and its really sounding. One last question on this one is "i see you have used some variables there mainly [$resault] and "BDB form". what those two? are they some buttons in your form or some items i didn't add in my form?

    Hope you will help on that one

    Thank you so much
    [$resault] - is a variable that returns 1 if connection to SQL DB was successful and returns 0 if its failed. You can define it when you use BDB Action "Connect to external DB". Write "resault" in "save resault of action to variable" field.

    "BDB Form" - is just a name of the form. I used button which was placed on form named "BDB form" In this case it is not necessary as we are checking the value of variable, so this is enough: If [$resault] = "1" then

    Try to test the connection with a button placed on the form or toolbar. I noticed that connection sometaimes fails if you try to use timer and establish it on BDB startup. I have no idea why it happens...

    rimpet

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •