SQL SERVER !!! Alpha?? does it work???

[spydre100]

Hmmm...was quite excited when i saw alpha 5, looked like it could be a half decent bit of dev software...but after playing with a remote connection string and then hooking up some tables it seems it dosn't work. altho it picks up tables and views, shows the fields, lets you map the primary key etc when you view the linked data its all wrong???? we have a view constructed of two tables tbl_main and tbl_main_info and the data shown isn't what we see in SQL??

So we then tried the Grid componet, that picked up the view, adds an alias shows all the columns which all looks great, but then when you preview the grid it pulls an error saying the view dosnt exist???

Not bad hu?

So before we waste any time this sort of suggests the drivers are not all that and it's riddled with issues, which im sure can all be fixed and worked around but prob utilises stacks of time and adds to a slow operating end solution....am i missing something, bailing board before a flood on bang on the mark?

basically Alpha 5 SQL server - --- Any good????

spider
UK

[Selwyn Rabins]

Re: SQL SERVER !!! Alpha?? does it work???

Hmmm...was quite excited when i saw alpha 5, looked like it could be a half decent bit of dev software...but after playing with a remote connection string and then hooking up some tables it seems it dosn't work. altho it picks up tables and views, shows the fields, lets you map the primary key etc when you view the linked data its all wrong???? we have a view constructed of two tables tbl_main and tbl_main_info and the data shown isn't what we see in SQL??

So we then tried the Grid componet, that picked up the view, adds an alias shows all the columns which all looks great, but then when you preview the grid it pulls an error saying the view dosnt exist???

Not bad hu?

So before we waste any time this sort of suggests the drivers are not all that and it's riddled with issues, which im sure can all be fixed and worked around but prob utilises stacks of time and adds to a slow operating end solution....am i missing something, bailing board before a flood on bang on the mark?

basically Alpha 5 SQL server - --- Any good????

spider
UK

are you building active-link tables, or web grid components.

either way, it works.

there may be something about your particular database, so i can't comment on that.

however, since we know ****definitively*** that when used with either Northwinds, Adventureworks, or Pubs (the sample databases that everyone who uses SQL server will have), why don't you confirm that it is working correctly with those database on your machine as a first step.

Once you can confirmed that the sample SQL server databases work as expected, we can move on to understanding what (if anything) is peculiar about your database.

[neil_albala]

Re: SQL SERVER !!! Alpha?? does it work???

So we then tried the Grid componet, that picked up the view, adds an alias shows all the columns which all looks great, but then when you preview the grid it pulls an error saying the view dosnt exist??? spider UK

Hey Spider-- I see you new here at Alpha Software. There are a few things you should know. And, anyone else that is concerned about this problem.

First of all, the fellow that responded to you message was Selwyn. He's one of the two CEO's of Alpha Software. Actually it's a private company, so you could say he's one of the two owners. Where else does the CEO-- who is the lead development engineer, respond to, and get involved in the day to day forum messages? You should take him up on his offer-- work with him-- he'll get that problem resolved-- FOR SURE-- he always does.

Last I head, Alpha Five had over a million users, so they're not a small company, BUT they're beta-testing is not as stringent as it could be. This is pretty typical when a new version is released.

The problem you describe sounds critical. If you give him the information he needs, I wouldn't be surprised if they had if fixed in less than a week. It's only been two weeks since Version 9 has been officially released. And don't be surprised if you discover another bug or two... Of course, Selwyn and his team will fix those as well. It usually takes a month or two for them to get them all.

The point I'm trying to make is this. I've used a LOT of different development platforms over the years, and there are two things-- paramount about Alpha Five:

1- No where have I ever seen such a responsive community of developers, as you will find in this forum. Both senior management and gangs of long term users have ALWAYS quickly, come to my aid. Every time I've had a question or run into a problem. All without cost. They do this for everyone, beginner and old pro alike. You'll see-- some of these threads get quite long. Nobody is left hanging. They'll work with you until the issue is resolved.

2- Unlike other development platforms which have a seemingly endless dependency requirements and complex, almost countless configuration files, A5's real beauty is it's all-in-one simplicity and elegance. You'll see the real genius there, once use attach a few Action Scripts to a button or event, in less than a minute, accomplishing an otherwise complex task-- THEN-- just as easily, look and learn from the Xbasic code that was created for that whole operation. It's VERY elegant in it's intuitiveness-- the way almost everything can be accomplished with these no-code-necessary Action scripts-- and then for the serious developers, generate and learn from the Xbasic code behind the scenes. There's no other platform that makes development and learning the language so easy.

Why am I taking the time to put in a good word for them? I don't work for the company. I haven't even upgraded yet. I'll tell you why. Because I can't count the number of times the staff here-- and the other members of this community, have come to my rescue-- often in a matter of hours-- getting me the answers I need, to get back on track quickly. Unless you're willing to pay per-incident, you just can't find that kind of attention anywhere else.

There are LOTS of other reasons why Alpha Five stands out, but those are the two that are most important to me.

[spydre100]

Re: SQL SERVER !!! Alpha?? does it work???

Well im sold on the support!! And if there is one thing i know support is worth fortunes.

Okay im not going to work with the northwind database etc etc as im sure the whole plannet have done this, also altho our SQL schema is very complex i did try to resolve the issue by simply asking alpha to look at just a simple table.

Alpha builds windows apps and web based apps, from what i can digest the web apps are constructed from components (such as grids) that you build using the alpha wizards and then you can ship the code and place it into a HTML page etc etc, all pretty simple.

When we first wound up Alpha 5 we spent ages trying to get the connection string to work, that was an error that thrown in becuase the drop down for the server (says (local)) when edited/deleted still appended (local) to our server name and because of its size (in width) it hid the word local? so in the drop down it stated our server name perfectly but in the connection string it read (local)#our server name# which threw us and it also threw the "Test" opperation as it failed.

Once we got the connection as expected Alpha found all our tables and views (at this point we were using alpha in windows dev mode) We took 2 objects

1. a simple table
2. a complex view

These were added as active link tables. We then viewed object 1 which was the table, the data was all correct. The tbl in question was out app login table and all that is required is to punch in a four digit password into the password column and the row when updated will return some login locales, such as a session var which is a "UNIQUE IDENTIFIER". We edited the password column entered "1234", right mouse clicked and selected "save record" and this threw and error:

"SERVER SIDE ERROR UPDATING ROW. DATABASE API SPECIFIC ERROR [MICROSOFT]ODBC SQL SERVER DRIVE] INVALID CHARACTER VALUE FOR CAST SPECIFICATION SQL STATE IS 22018."

The table has instead of update/insert triggers. But works fine in SQL and also we have build classes, web services, grids etc that all use this table and function fine.

Maybe its the "UNQIUE ID" fields, some form of cast error translation problem?

Okay so nect we looked at object 2, the view this on first site again looked okay, BUT on second glace it had repeated each row (42 times, which is how many rows are in the view) but simply displayed the first row repeated, instead of actualy showing us all the rows in the recordset.

So i have just mapped another active link table, this time i explored the options for carefully, this view did not comprise of two tables, Alpha asked for a primary key, i added the intended field (col 1) and was suspecting the view would display correctly, this one has only two rows its our currency object "pounds gbp" and "dollars" however again two rows show both are a row one repeated???

What we then did was to jump over to creating a grid, this was less successful as altho the wizard found the tables/views showed all the columns etc ect, when we tried to preview the grid in threw an error:

Database API specific error Your database has returned the following error code and description to Alpha Five. Consult your database documentation for further information. 208 - '[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.Tbl_Login'. SQL State is: 42S02'

Which is strange as Tbl_Login exists????

We tried creating grids from a number of tables and views all failed with the same error whcih appears in the row data, altho the column headings are all correct

Any thoughts??/

Spider

[spydre100]

Re: SQL SERVER !!! Alpha?? does it work???

Okay we are using a remote version of SQL 2005 and alpha 9 platinum.

Iwould seem that we prob are having problems becuase our tables have

SQL datetime fields
SQL UNIQUE IDENTIFIERS

We made anutha simple table without these and in the form project it mapped a linked table and we could see the data correctly and we could edit it, it worked fine at first glance.

However if we used that table for the source of a Grid component we get the same error:

Database API specific error Your database has returned the following error code and description to Alpha Five. Consult your database documentation for further information. 208 - '[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.Tbl_SysDay'. SQL State is: 42S02'


So it would seem,

1. The error we reported in our first post regarding when we tried to edit out login row is because this table uses UNIQUE IDENTIFIERS and also SQL DATETIME FIELDS which are casuing the cast error.
2. The Error regarding the view which showed repeated rows??? We will try building a view without UNIQ ID and DATES and see if that works or still has errors.

We will keep u informed.

[spydre100]

Re: SQL SERVER !!! Alpha?? does it work???

We have now tried using an sql select statement (SELECT * from OurView; Instead of selecting the view from the drop down, this strangley works!!
So it would seem that the Tables and Views that are preloaded in the drop downs do not create the correct SQL to execute them when you use the Grid Wizrd.

One thing we note the grids are not Ajax? Is this correct or are we missing a setting?

[spydre100]

Re: SQL SERVER !!! Alpha?? does it work???

We have also tried using the SQL Genie and our views work fine and display data correctly, so the issue must be with the datasource selection tab of the 'add link table' and web grid genie where if we select a view or table the SQL created is not parsed properley to the drivers.

We also have a concern that it is not possible to pase SQL statements that obtain data from SQL 2005 table functions

for example;

SELECT * FROM dbo.fnSel_Currency(APPID);

Our SQL database uses table functions so that we can return localised data rows formated and configured for the caller (Dates, TimeZone, Currencies, Language etc)

The beauty of this is that we can then join localized tablesets at the server to return pre-formated date, which cannot be done using Stored Procedures.



Will Alpha allow us to access our functions or will we need to rap them as constraints (sorry views) or stored procedures??

many thanks

martin

[Selwyn Rabins]

Re: SQL SERVER !!! Alpha?? does it work???

We have also tried using the SQL Genie and our views work fine and display data correctly, so the issue must be with the datasource selection tab of the 'add link table' and web grid genie where if we select a view or table the SQL created is not parsed properley to the drivers.

We also have a concern that it is not possible to pase SQL statements that obtain data from SQL 2005 table functions

for example;

SELECT * FROM dbo.fnSel_Currency(APPID);

Our SQL database uses table functions so that we can return localised data rows formated and configured for the caller (Dates, TimeZone, Currencies, Language etc)

The beauty of this is that we can then join localized tablesets at the server to return pre-formated date, which cannot be done using Stored Procedures.



Will Alpha allow us to access our functions or will we need to rap them as constraints (sorry views) or stored procedures??

many thanks

martin

would it be possible to get a copy of your sql server .mdf and .ldf files so that we can attach them to a local SQL Server and then duplicate the issues you are having.

any issue that we can duplicate we will fix promptly.

a5 should not be putting any limits on what sql you send to the server.

[spydre100]

Re: SQL SERVER !!! Alpha?? does it work???

Ok just dealing with the active tables which we canot make work.
We have tried native sql and using the table/view selector both return duplicated rows, we have also tried using our view and then using our table that the view is based on, neither work.

So as a start assuming the Database enviroment isnt an issue here is the code for the table:


CREATE TABLE [dbo].[Tbl_InventoryBin] (
[InventoryBin_Key] int IDENTITY(1, 1) NOT NULL,
[InventoryBin_WareHouseKey] int NOT NULL,
[InventoryBin_BinTypeKey] int NOT NULL,
[InventoryBin_ItemKey] int NOT NULL,
[InventoryBin_Handle] nvarchar(20) COLLATE Latin1_General_CI_AS CONSTRAINT [DF__Tbl_Inven__BinHa__2CC890AD] DEFAULT '' NOT NULL,
[InventoryBin_Units] int CONSTRAINT [DF__Tbl_Inven__BinUn__29EC2402] DEFAULT (0) NOT NULL,
[InventoryBin_Value] money CONSTRAINT [DF__Tbl_Inven__BinVa__2AE0483B] DEFAULT (0) NOT NULL,
[InventoryBin_Avg] money CONSTRAINT [DF__Tbl_Inven__BinAv__2BD46C74] DEFAULT (0) NOT NULL,
[InventoryBin_Added] datetime CONSTRAINT [DF__Tbl_Inven__First__04BA9F53] DEFAULT getutcdate() NULL,
[InventoryBin_AddedSessionKey] int NULL,
[InventoryBin_Updated] datetime NULL,
[InventoryBin_UpdatedSessionKey] int NULL,
[InventoryBin_TimeStamp] timestamp NULL,
[InventoryBin_CurrencyKey] int NOT NULL,
[InventoryBin_ApplicationKey] int NULL,
[InventoryBin_Enabled] bit CONSTRAINT [DF__Tbl_Inven__Inven__202F464C] DEFAULT (1) NULL,
[InventoryBin_Remove] bit CONSTRAINT [DF__Tbl_Inven__Inven__21236A85] DEFAULT (0) NULL,
[InventoryBin_Certificate] uniqueidentifier NULL,
PRIMARY KEY CLUSTERED ([InventoryBin_Key])
)
GO



Create this table on your SQL server and see if you can create a linked table in ALpha 9 Plat. If this works then the issue must be with the server enviroment or the database itself.

See how that goes.

P.S. can you confirm should it be possible to access 2005 table functions through the use of native SQL??
As we cant?!

[Selwyn Rabins]

Re: SQL SERVER !!! Alpha?? does it work???

We also have a concern that it is not possible to pase SQL statements that obtain data from SQL 2005 table functions

for example;

SELECT * FROM dbo.fnSel_Currency(APPID);

we tested this. we wrote a simple table function called oldfolks() that returned a list of people born before a certain date.
here is the Interactive window session:

dim c as sql::connection
?c.open("{A5API='SQLServer',Server='.\SQLEXPRESS',Trusted_connection=yes,Database='Test1'}")
= .T.
?c.execute("select * from dbo.oldfolks()")
= .T.
sql_resultset_preview(c.resultset)

works fine.

[Selwyn Rabins]

Re: SQL SERVER !!! Alpha?? does it work???

Ok just dealing with the active tables which we canot make work.
We have tried native sql and using the table/view selector both return duplicated rows, we have also tried using our view and then using our table that the view is based on, neither work.

So as a start assuming the Database enviroment isnt an issue here is the code for the table:


CREATE TABLE [dbo].[Tbl_InventoryBin] (
[InventoryBin_Key] int IDENTITY(1, 1) NOT NULL,
[InventoryBin_WareHouseKey] int NOT NULL,
[InventoryBin_BinTypeKey] int NOT NULL,
[InventoryBin_ItemKey] int NOT NULL,
[InventoryBin_Handle] nvarchar(20) COLLATE Latin1_General_CI_AS CONSTRAINT [DF__Tbl_Inven__BinHa__2CC890AD] DEFAULT '' NOT NULL,
[InventoryBin_Units] int CONSTRAINT [DF__Tbl_Inven__BinUn__29EC2402] DEFAULT (0) NOT NULL,
[InventoryBin_Value] money CONSTRAINT [DF__Tbl_Inven__BinVa__2AE0483B] DEFAULT (0) NOT NULL,
[InventoryBin_Avg] money CONSTRAINT [DF__Tbl_Inven__BinAv__2BD46C74] DEFAULT (0) NOT NULL,
[InventoryBin_Added] datetime CONSTRAINT [DF__Tbl_Inven__First__04BA9F53] DEFAULT getutcdate() NULL,
[InventoryBin_AddedSessionKey] int NULL,
[InventoryBin_Updated] datetime NULL,
[InventoryBin_UpdatedSessionKey] int NULL,
[InventoryBin_TimeStamp] timestamp NULL,
[InventoryBin_CurrencyKey] int NOT NULL,
[InventoryBin_ApplicationKey] int NULL,
[InventoryBin_Enabled] bit CONSTRAINT [DF__Tbl_Inven__Inven__202F464C] DEFAULT (1) NULL,
[InventoryBin_Remove] bit CONSTRAINT [DF__Tbl_Inven__Inven__21236A85] DEFAULT (0) NULL,
[InventoryBin_Certificate] uniqueidentifier NULL,
PRIMARY KEY CLUSTERED ([InventoryBin_Key])
)
GO



Create this table on your SQL server and see if you can create a linked table in ALpha 9 Plat. If this works then the issue must be with the server enviroment or the database itself.

See how that goes.

P.S. can you confirm should it be possible to access 2005 table functions through the use of native SQL??
As we cant?!

thanks.

the problem is the timestamp field. we will have a fix for this early next week.
there may be a problem in the build you are using related to the GUID fields, but these have already been fixed internally, but not yet released in a patch.

in our tests, if we omitted the timestamp field from the active link definition, then it worked as expected.

thanks for bringing this to our attention.

[spydre100]

Re: SQL SERVER !!! Alpha?? does it work???

Thanx for that, okay so the new version will fix these bugs, great! how do we check when the update is available?

"select * from dbo.oldfolks()"

Yeah this works great from code and also can be used on an active link table, but when we try and create say a grid component (for web forms) because these use portable sql/sql genie they dont parse the functions and they throw an error. is it not possible to simply pass a function call to a grid and see the dataset?? Obviosly this would not be updateable directly, but one could pass SQL/SP's for the update, insert, delete to the underlying view or tables?

Many thank for helping with this

Martin

[spydre100]

Re: SQL SERVER !!! Alpha?? does it work???

Now that we seem to have solved the Question of whether it works, i think i should re-title my post, or then again perhaps its best left to prove the point, That YES it works and very well, reminds me slightly of the good old days of building apps with MS ACCESS, which was a synch!!

Anyway moving on. Our database has its own login/session system. Any call to the database through functions (for viewing data) or Views for Inserting or Updating require the caller to pass a Certificate value for authentication. Which is a param in the table functions and a column in the views.
This certificate identifies "who" the calling client is. If it has expired or is invalid the triggers/functions fail the execution and return an error (your session has expired, or caller not known etc)



We therfore need to find out how to pass data to hidden columns in a grid and in a web grid, basically append the certificate to the insert/update before the sql parsed.


Can you point us to any documentation available on how to pass column insert/update data behind the scenes for both grid web components and windows forms???

Many thanks

Martin

[Selwyn Rabins]

Re: SQL SERVER !!! Alpha?? does it work???

Thanx for that, okay so the new version will fix these bugs, great! how do we check when the update is available?

"select * from dbo.oldfolks()"

Yeah this works great from code and also can be used on an active link table, but when we try and create say a grid component (for web forms) because these use portable sql/sql genie they dont parse the functions and they throw an error. is it not possible to simply pass a function call to a grid and see the dataset?? Obviosly this would not be updateable directly, but one could pass SQL/SP's for the update, insert, delete to the underlying view or tables?

Many thank for helping with this

Martin

I'll need to do some testing on the Grid component to see what's going on. The Grid don't currently expose a 'portable/native' SQL switch, but I know that internally, if executing a the SQL as a portable statement fails, they try to then execute as a native statement.

Once I can duplicate the problem, it should be easy to fix.

Of course, the Grid component is by no means the only way to build a web app. You sound like you are a pretty sophisticated user and in the next 14 days or so we will be posting an example of how a user like you might code a Grid using Xbasic/Ajax. If you code the grid using Xbasic, then of course there are no constraints on what SQL you execute for the Select, Update, Delete, etc.


When an update is available, the News tab on the Welcome screen that is shown on startup will let you know.

[spydre100]

Re: SQL SERVER !!! Alpha?? does it work???

That sounds perfect, i can see from the grid wizard/genie that for the functionality we will need (re passing params behind the scenes in hidden columns) that building our own xbasic grid code will be the way forward, further more we will required ajax and so on, once we have a grid coded that works to our diesired functionality we can build a code template and run our SQL code builder to roll out all the grids we need for our views and functions.

This is very exciting, could you keep us informed as and when you get fixes to our issues and get any documentation/webcasts on how to build the xbasic grid code/ajax

Many thanks

Martin