SQL Server Database examples

[semiplay]

Are there any examples online for getting records from SQL Server via ODBC? I can connect to the database using the QueryBuilder using a DSN, but it won't show me all the fields when I attach it to a text grid.

All the fieldnames are shown in the connection dialog, and I select to use them all, but not all show up in the text field.

Any help is appreciated. Ultimately I'd like to code this without the query builder because I have to manipulate the data.

Thanks,

Mark

[Janschenkel]

Hi Mark,

Is it possible that the extra columns are there, but out of view?
Turn on the horizontal scrollbar to check if that's the case.

Other than that, here's a few code snippets.
1. To open the connection and fetch a return and tab-delimited grid:

Code: Select all

on mouseUp
  put revOpenDatabase("odbc","sqlserverdsn",,"user,"password") into tConnectionId
  -- make sure we have a valid connection
  if tConnectionId is not an integer then
    answer error tConnectionId
    exit mouseUp
  end if
  -- prepare the query and get the complete resultset as a tab-and-return delimited string
  put "SELECT * FROM Customers" into tQuery
  put revDataFromQuery(tab, return, tConnectionId, tQuery) into field "CustomersGrid"
  -- don't forget to close the database connection
  revCloseDatabase tConnectionId
end mouseUp

2. To open the connection and put columns into fields:

Code: Select all

on mouseUp
  put revOpenDatabase("odbc","sqlserverdsn",,"user,"password") into tConnectionId
  -- make sure we have a valid connection
  if tConnectionId is not an integer then
    answer error tConnectionId
    exit mouseUp
  end if
  -- prepare the query and get the complete resultset as a tab-and-return delimited string
  put "SELECT * FROM Customers WHERE CustomerId = :1" into tQuery
  put 123456 into tCustomerId
  put revQueryDatabase(tConnectionId, tQuery, "tCustomerId") into tResultSetId
  -- make sure we have a valid result set
  if tResultSetId is not an integer then
    answer error tResultSetId
  else
    put revDatabaseColumnNamed(tResultSetId, "FirstName") into field "First name"
    put revDatabaseColumnNamed(tResultSetId, "LastName") into field "Last name"
  end if
  -- don't forget to close the database connection
  revCloseDatabase tConnectionId
end mouseUp

3. To create a new customer record

Code: Select all

on mouseUp
  put revOpenDatabase("odbc","sqlserverdsn",,"user,"password") into tConnectionId
  -- make sure we have a valid connection
  if tConnectionId is not an integer then
    answer error tConnectionId
    exit mouseUp
  end if
  -- prepare the query and get the complete resultset as a tab-and-return delimited string
  put "INSERT INTO Customers (CustomerId,FirstName,LastName) VALUES (:1,:2,:3)" into tQuery
  put field "Customer Id" into tCustomerId
  put field "First name" into tFirstName
  put field "Last name" into tLastName
  revExecuteSQL tConnectionId, tQuery, "tCustomerId", "tFirstName", "tLastName"
  -- don't forget to close the database connection
  revCloseDatabase tConnectionId
end mouseUp

4. To update an existing customer record

Code: Select all

on mouseUp
  put revOpenDatabase("odbc","sqlserverdsn",,"user,"password") into tConnectionId
  -- make sure we have a valid connection
  if tConnectionId is not an integer then
    answer error tConnectionId
    exit mouseUp
  end if
  -- prepare the query and get the complete resultset as a tab-and-return delimited string
  put "UPDATE Customers SET FirstName = :1 WHERE CustomerId = :2" into tQuery
  put field "Customer Id" into tCustomerId
  put field "First name" into tFirstName
  revExecuteSQL tConnectionId, tQuery, "tFirstName", "tCustomerId"
  -- don't forget to close the database connection
  revCloseDatabase tConnectionId
end mouseUp

Hope this gets you started on using database without the query builder. Make sure to read the User Guide PDF document, as this has a lot of information on how to combine the Query Builder and database library commands and functions.

Jan Schenkel.

[semiplay]

Jan,

Thanks for the examples! Ok, I tried the first, and I am getting the same thing. I am putting some screenshots in here. The first is from the sql database --- that's the data I am trying to pull. The next is what is in Rev (I also tried the H Scrollbars), and then the last one is what happens when I click into a field --- the number changes!

Any ideas?

Mark

[Janschenkel]

That looks like an impedance between the database query and the table field cache. Try clearing the cache using the following command:

Code: Select all

revEmptyTable (the long ID of field "<MyTable>"

(where MyTable is the name of the table field) and then refresh the database query.

If that doesn't help, we'll have to think of something else.

Jan Schenkel.

[semiplay]

Jan,

I tried that code. The number in the field doesn't change when I click on it now, but the data still stops short a column like it was before. Any other ideas? Thanks!

Mark

[Janschenkel]

In that case, I would try and fetch the data myself in two ways, to try and figure out the issue.
Assuming that the automated query is named "MyQuery" and the sql itself is "SELECT * FROM Customers"

Add a new scrolling field "Test1" and a new button "Test1" with script:

Code: Select all

on mouseUp
  put revConnectionOfQuery("MyQuery") into tConnectionId
  put "SELECT * FROM Customers" into tSQL
  put revDataFromQuery(tab, return, tConnectionId, tSQL) into field "Test1"
end mouseUp

What happens when you click the button? Do you see all the columns of the table?

Add another scrolling field "Test2" and a new button "Test2" with script:

Code: Select all

on mouseUp
  put revConnectionOfQuery("MyQuery") into tConnectionId
  put "SELECT * FROM Customers" into tSQL
  put revQueryDatabase(tConnectionId, tSQL) into tResultSet
  if tResultSet is not a number then
    answer error tResultSet
    exit mouseUp
  end if
  put revDatabaseColumnNames(tResultSet) & return into field "Test2"
  put revDatabaseColumnCount(tResultSet) into tColumnCount
  repeat until revCurrentRecordIsLast(tResultSet)
    repeat with tColumn = 1 to tColumnCount
      if revDatabaseColumnIsNull(tResultSet, tColumn) then
        put "null" after field "Test2"
      else
        put empty into tHolder
        get revDatabaseColumnNumbered(tResultSet, tColumn, "tHolder")
        put tHolder after field "Test2"
      end if
      put tab after field 2
    end repeat
    put return after field "Test2"
    -- uncomment next line for visual progress
    --  set the scroll of field "Test2" to the formattedHeight of field "Test2"
    revMoveToNextRecord tResultSet
  end repeat
  revCloseCursor tResultSet
end mouseUp

Now click on button "Test2" and see what happens next.

Which of the two test buttons showed you all the data?

Jan Schenkel.

[semiplay]

Ok, Test1, same results. Test 2 results below:

There were some strange characters mixed in there as well, so maybe it was coming through, but was not legible?

[Janschenkel]

The weird characters would efinitely indicate that there is data in there that Revolution is not sure how to handle. Could it be that the data in the column is in Unicode, not in ASCII?

Jan Schenkel.

[semiplay]

Jan,

If it is in Unicode, is there anything I can do to get Rev to understand it?

I'm new to MS SQL, so that doesn't help. But I'd much rather do this app in Rev than .NET

Thanks,

Mark

[Janschenkel]

If the field data is indeed in Unicode, you mae have to use the 'uniDecode' function to transform it from the database character encoding to the UTF-16 encoding used internally in Revolution.

Do you have access to the SQLServer administrator application to check the datatype of the FieldValue column?

Jan Schenkel.

[semiplay]

Jan,

The field types are bigint, char, and ntext

Also found this:
Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-2003 synonym for ntext is national text.

Any ideas?

Thanks,

Mark

[Janschenkel]

As far as I know, Rev has no built-in support for UCS-2 encoding. Maybe an email to support@runrev.com can help you figure out a solution.

Jan Schenkel.

[semiplay]

Jan,

I have emailed support about this.

I just read that UCS-2 is synonymous with UTF-16. Do you have any code that would decode UTF-16? I tried uniencode and unidecode in various fashions, got funky characters, but I am thinking this might work.

Thanks,

Mark

[Janschenkel]

Well, Revolution uses Unicode UTF-16 internally, so this should be achievable. Maybe we shouldn't try to read the data as text data, but actually get it as binary data.
Going back to my earlier script:

Code: Select all

on mouseUp 
  put revConnectionOfQuery("MyQuery") into tConnectionId 
  put "SELECT * FROM Customers" into tSQL 
  put revQueryDatabaseBLOB(tConnectionId, tSQL) into tResultSet 
  if tResultSet is not a number then 
    answer error tResultSet 
    exit mouseUp 
  end if 
  put revDatabaseColumnNames(tResultSet) & return into field "Test2" 
  put revDatabaseColumnTypes(tResultSet) & return after field "Test2"
  put revDatabaseColumnCount(tResultSet) into tColumnCount 
  repeat until revCurrentRecordIsLast(tResultSet) 
    repeat with tColumn = 1 to tColumnCount 
      if revDatabaseColumnIsNull(tResultSet, tColumn) then 
        put "null" after field "Test2" 
      else 
        put empty into tHolder 
        get revDatabaseColumnNumbered(tResultSet, tColumn, "tHolder") 
        put tHolder after field "Test2" 
      end if 
      put tab after field 2 
    end repeat 
    put return after field "Test2" 
    -- uncomment next line for visual progress 
    --  set the scroll of field "Test2" to the formattedHeight of field "Test2" 
    revMoveToNextRecord tResultSet 
  end repeat 
  revCloseCursor tResultSet 
end mouseUp

This version switches to the 'revQueryDatabaseBLOB' function, and adds a call to 'revDatabaseColumnTypes' to see what the Rev database library thinks each column is.

So the question is two-fold:
1) does switching to BLOB help?
2) what column type is the 'FieldValue' column?

Jan Schenkel.

[Janschenkel]

UPDATE: thanks to a post by Ben Rubinstein on the use-revolution mailing list, the following quality center entry surfaced: http://quality.runrev.com/qacenter/show_bug.cgi?id=5440
It seems this sort of corruption is fixed in Revolution 2.9 - could you download version 2.9 (release candidate) and verify that this solves your problem?

Jan Schenkel.