Database

Re: Sybase query returns blank/empty for char fields

15-Dec-17 6:56

For many years, I have been successfully using MS Access VBA to import data from a Sybase ASE server through direct queries. Several years ago, it was necessary to locate and install new drivers when I upgraded from XP to Win7 64-bit. All was working well until I got a new Win10 PC. I fought with installing the same drivers I used with the Win7 machine but was finally able to make a successful connection. The recordsets that are returned contain empty character fields. Numeric and date fields display fine.

For example,

SQL

SELECT 123 FROM someTable

Returns: 123

SQL

SELECT '123' FROM someTable

Returns:

I tried different values for the CharacterSet parameter in the connection string but it made no difference. I also googled... There was some information regarding empty strings for formatting purposes but none that I found that would explain the above results.I can't figure out why only char fields are affected. I would expect all or nothing if it were a driver issue but the same drivers work in Win7.

Any thoughts??

jschell16-Dec-17 9:06

jschell

16-Dec-17 9:06

Presuming you are not hiding/eating errors then it has nothing to do with connections nor general calling semantics.

However it very likely has to do with HOW you are calling it which you did not specify.

But I will note that in the first case I would expect a caller to be defined to expect a numeric value.

While the second would require a caller to expect a textual value and probably a 'CHAR' type with a specific size (3 or greater) specified.

Re: Sybase query returns blank/empty for char fields

Member 1354244117-Dec-17 1:33

Re: Sybase query returns blank/empty for char fields

17-Dec-17 1:33

The call for the Win 10 machine is below. This is the same as it is in the Win 7 machine. Sensitive information masked

Public Sub Doit()
    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    Dim sSql As String
    Dim sConnStr As String
            
    sSql = "SELECT '123' FROM someTable"
    sConnStr = "Provider=ASEOLEDB.1;Password=[password];Persist Security Info=True;User ID=[user];Data Source=[ip_address]:[port];Initial Catalog=xyz"
    sConnStr = Replace(sConnStr, "[password]", "******")
    sConnStr = Replace(sConnStr, "[user]", "******")
    sConnStr = Replace(sConnStr, "[ip_address]", "*******")
    sConnStr = Replace(sConnStr, "[port]", "******")
	
    cn.Open sConnStr
    rs.Open sSql, cn
    
    Debug.Print rs.GetString
    
End Sub

Member 1354244119-Dec-17 13:36

Re: Sybase query returns blank/empty for char fields

19-Dec-17 13:36

Well, It appears as if it is a driver compatibility issue. I downloaded the Devart driver and string fields are returned as expected. Further research shows that there are newer drivers in the SDK but it is no longer available to common folks like myself. I suppose I will have to weigh the cost of a third party driver versus redesigning and distributing the app. Unless anyone knows an alternate location for ASE drivers compatible with Windows 10??

Member 1354244121-Dec-17 14:09

Need a demo or tutorial of a simple SQL Database with a 3 Layer modell

21-Dec-17 14:09

This issue has been resolved. After days of searching, I found an evaluation version of SAP Adaptive Server Enterprise 16. This package contained updated drivers that work with Windows 10.

Schudi12-Dec-17 3:27

Schudi

12-Dec-17 3:27

First please excuse my bad english. As you can asume it's not my native language....

I'm using SQL Server Compact Edition and Visual Basic 2015 and i did a few programs with SQL-Databas-Access so far.

This programs where quite simple. They read the whole table at the beginning into a Dataset, work with this and write it back when the application is closed.

Now i need some more secure access and found some information that it's better to cut the database Acces from the gui and the business logic - all three called layer.

What i need or what i'm asking for is a small sample application with just one Database-Table - let's name it "Persons" with the fiels "Person-Id" (the key), "Person-Firstname", "Person-Lastname", "Person-DateOfBirth".

GUI should have separate fields for each of the given fields.

Acces should be Record by Record. (Input the Id, if exist show the related data in the gui, if not show empty fields, with a save button to change values / write the changes to tha database.)

Business logic may just check if the DateOfBirth is in a range from 1.1.1900 to 31.12.2099.

How should my class look like to build all three layers?

Maybe this is to much described in detail...

I just want to see a class representing all 3 layers to handle some persons data ...

Is this possible? Whould someone be so kind? This would help me so much in understanding!!!

Thank you very, very much!

Re: Need a demo or tutorial of a simple SQL Database with a 3 Layer modell

Richard MacCutchan12-Dec-17 5:35

Richard MacCutchan

12-Dec-17 5:35

Here is a useful article on the subject: The Model-View-Controller(MVC) Pattern with C#/WinForms[^]

Re: Need a demo or tutorial of a simple SQL Database with a 3 Layer modell

Nathan Minier12-Dec-17 5:50

Re: Need a demo or tutorial of a simple SQL Database with a 3 Layer modell

12-Dec-17 5:50

I think you have misunderstood a little how the three-tier model works.

You don't need to re-invent your model for each tier. There are different approaches on how to handle this, but most modern approaches are based on the Model-View-Something(MV*) architecture (MVC, MVVM, MVP...there seem to be new ones daily).

Each of these map to the three-tier architecture in slightly different ways, but there are consistent points. A Model is a an object that is solely a data container. It should implement no business logic, and in your case will represent a single record from the database. It's basically just a data container.

The other consistent element is the View, or UI. Any UI interaction logic should live here, to include wiring for UI event handlers and any UI-specific logic. This is where it can become easy to get lost and break the pattern, but play with it a little and you'll start to get it.

The last part of the MV* architecture is how the business layer is implemented. Ideally, the business layer should server as an intermediary between the DAL and the UI, and depending on implementation there can be a varying level of success with that.

I suggest you have a look around at some resources regarding these patterns; there is a lot available. Which one you start with depends a lot on what sort of applications you are writing: web apps almost invariably fall to the MVC side while Windows desktop apps generally end up in an MVVM world. Just don't let yourself get confused by MVC.NET, that's a specific implementation from MicroSoft.

"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli

Schudi12-Dec-17 6:22

Schudi

12-Dec-17 6:22

I heared about MVC so far. This is what a mean. Sorry if i asked my question the wrong way.

My goal is a windows desktop app using win forms.

Of course a want to learn it from the basics but i wasn't able to find some tutorials about that and on the other hand i often lerned by having a closer look on an ready example...

Do you know a basic tutorial or example about mvc or mvvm?

Many, many thanks in advance.

Re: Need a demo or tutorial of a simple SQL Database with a 3 Layer modell

Nathan Minier12-Dec-17 7:10

Re: Need a demo or tutorial of a simple SQL Database with a 3 Layer modell

12-Dec-17 7:10

Generally you're looking at MVVM then, which is more commonly associated with WPF, which may be the path you want to take. WinForms is a supposedly deprecated technology and MS may remove support for it "someday", but WPF is still in wide use.

There are many, many examples and tutorials available on CP. Just searching the articles will give you a abundance of choice:
CP MVVM Search

You can likely safely ignore the articles that directly address specific controls and technologies until you get the gist.

"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli

Richard MacCutchan12-Dec-17 8:01

Richard MacCutchan

12-Dec-17 8:01

I gave you a link to a simple example/tutorial in my message above.

Re: Need a demo or tutorial of a simple SQL Database with a 3 Layer modell

Schudi12-Dec-17 8:13

Schudi

12-Dec-17 8:13

Yes, you did. Thank you very much. I will have a closer look tomorrow.

CTE Sub-Sort

Stan Lake11-Dec-17 11:52

Stan Lake

11-Dec-17 11:52

I am trying to improve my CTE knowledge and am stuck on a point that I am hoping that someone can help me with. I have not found this in the forum, so if you know of a post that covers this, please point me towards it.

Using the sample off the MS CTE page:

<pre lang="SQL">CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

WITH DirectReports (ManagerID, EmployeeID, Title, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title,
0 AS Level
FROM dbo.MyEmployees AS e
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
order by level, managerid
GO</pre>

Gives this, which I am pretty much understanding.

ManagerID EmployeeID Title Level
NULL 1 Chief Executive Officer 0
1 273 Vice President of Sales 1
273 16 Marketing Manager 2
273 274 North American Sales Manager 2
273 285 Pacific Sales Manager 2
16 23 Marketing Specialist 3
274 275 Sales Representative 3
274 276 Sales Representative 3
285 286 Sales Representative 3

What I would like to figure out is how to modify the query to group the data differently, in this example show a manager, then anyone who reports to them, then the next manager. It seems like a simple concept, but I will be damned if I have been able to figure it out.

ManagerID EmployeeID Title Level
NULL 1 Chief Executive Officer 0
1 273 Vice President of Sales 1
273 16 Marketing Manager 2
16 23 Marketing Specialist 3
273 274 North American Sales Manager 2
274 275 Sales Representative 3
274 276 Sales Representative 3
273 285 Pacific Sales Manager 2
285 286 Sales Representative 3

</pre>

Thanks for reading.

Re: CTE Sub-Sort

Richard Deeming12-Dec-17 1:56

Richard Deeming

12-Dec-17 1:56

Something like this should work:

SQL

WITH DirectReports As
(
    SELECT 
        e.ManagerID, 
        e.EmployeeID, 
        e.Title,
        0 AS Level,
        CAST(e.EmployeeID As varchar(max)) As Path
    FROM 
        @T As e
    WHERE 
        ManagerID IS NULL

    UNION ALL

    SELECT 
        e.ManagerID, 
        e.EmployeeID, 
        e.Title,
        Level + 1,
        Path + '/' + CAST(e.EmployeeID As varchar(max))
    FROM 
        @T As e
        INNER JOIN DirectReports As d
        ON e.ManagerID = d.EmployeeID
)
SELECT 
    ManagerID, 
    EmployeeID, 
    Title, 
    Level,
    Path
FROM 
    DirectReports
ORDER BY 
    Path
;

Output:

ManagerID    EmployeeID    Title                           Level    Path
NULL         1             Chief Executive Officer         0        1
1            273           Vice President of Sales         1        1/273
273          16            Marketing Manager               2        1/273/16
16           23            Marketing Specialist            3        1/273/16/23
273          274           North American Sales Manager    2        1/273/274
274          275           Sales Representative            3        1/273/274/275
274          276           Sales Representative            3        1/273/274/276
273          285           Pacific Sales Manager           2        1/273/285
285          286           Sales Representative            3        1/273/285/286

Alternatively, you could use the hierarchyid[^] type:
Tutorial: Using the hierarchyid Data Type | Microsoft Docs[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: CTE Sub-Sort

Stan Lake12-Dec-17 2:35

Stan Lake

12-Dec-17 2:35

Thank you. That was driving me nuts. I was very close, but kept just missing. Plus, thanks for the tip on hierarchyid. I will be digging into that, as soon as I get this working inside my design.

Re: CTE Sub-Sort

Mycroft Holmes12-Dec-17 12:45

Mycroft Holmes

12-Dec-17 12:45

Psst the best way to thank Richard (the CTE guru) is to up vote his response. Voting arrows appear on the left of the message.

Never underestimate the power of human stupidity
RAH

Table Design Suggestion

Manish K. Agarwal7-Dec-17 19:42

Manish K. Agarwal

7-Dec-17 19:42

I have to maintain some metadata lists in DB, I have 2 options to design underlying simple table,
1st:

NAME   VALUE
-------------------
dept   HR
dept   fin
role   engineer
role   designer

UNIQUE CONSTRAINT (NAME, VALUE) and some other columns like auto generated ID, etc.

2nd:

NAME   VALUE_JSON_CLOB
-------------------
dept   {["HR", "fin"]}
role   {["engineer", "designer"}]

UNIQUE CONSTRAINT (NAME) and some other columns like auto generated ID, etc.

There is no DELETE operation, only SELECT and INSERT/UPDATE. In first advantage is only INSERT is required but SELECT (fetch all values for a given NAME) will be slow. In second SELECT will be fast but UPDATE will be slow. By considering there could be 1000s of such lists with 1000s for possible values in the system with frequent SELECTs and less INSERTs, which TABLE design will be good in terms of performance.

Thanks in advance.

Nathan Minier8-Dec-17 1:33

8-Dec-17 1:33

Performance is a meaningless metric unless we know what your use case is.

Also, the second is more like an object store, and would do better in a non-relational (NoSQL) system.

"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli

Manish K. Agarwal10-Dec-17 17:46

Manish K. Agarwal

10-Dec-17 17:46

As described above as well:

There is no DELETE operation, only SELECT and INSERT/UPDATE. Also every time I want to select all possible values for given NAME.

This is a kind of reference list to fill the autocomplete suggestions on UI. Hence fetch will be always all values and INSERT new only one value at a time.

Nathan Minier11-Dec-17 1:38

11-Dec-17 1:38

Manish K. Agarwal wrote:
Also every time I want to select all possible values for given NAME.

Not exactly what you said above, to be fair. It also doesn't say whether this is native or web-based, which has a relevant impact on performance of the system as a whole.

If writes are rare in either case, you'll see better SELECT performance out of option 2 for one simple reason: assuming that you index the key, when it's found the appropriate key, it's done and will stop searching the table. If you're in a web environment, you have the added benefit of being able to send it straight down the pipe with no serialization/deserialization required. If all your model requires is selection speed, option 2 is the way to go.

Option 2 is definitely not write friendly, though, nor can you use it to relate data down the road on the database level. If these are likely or even possible considerations, you might want to go with option 1.

"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli

Richard Deeming8-Dec-17 1:35

Richard Deeming

8-Dec-17 1:35

I'd immediately rule out the second option. You should always try to avoid storing multiple values in a single column.

Assuming the data is meant to be a set of look-ups for other records, I'd also try avoid the first option. Put each list in its own table - Departments, Roles, etc. That way, you'll be able to define foreign key relationships from the tables that reference them.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Manish K. Agarwal10-Dec-17 17:53

Manish K. Agarwal

10-Dec-17 17:53

It is a complex system where I don't know upfront what all possible reference list may exist in the runtime environment. Also, these value will never be referenced from any other table or column. A kind of auto-suggest list and user is always free to choose new value if not like any of the suggestions.